.

John mentioned that Excel can remove the background from an imported image. I didn't know that! I'm guessing most of Excel's non-artistic nerd community missed this too.

Apparently, this feature works in Excel 2010 and Excel 2013 (not sure about 2007).

Here's the deal:

.

Let's say I import a picture like this one into Excel, and I want to crop out everything except the picture of John playing the banjo on the right.

.

I can click on the picture while it's in Excel, and choose the Remove Background command on the far left of the Format tab.

This immediately puts a purple box around my picture.

I start by adjusting the handles to the area that I want to keep. Excel does a pretty good job getting close to removing everything but John. But you can see that it missed his feet, part of his head, and part of the banjo (they're still in purple).

.

So this is where I can use a few commands on the Background Removal tab.

Mark Areas to Keep: Allows me to tag the areas to keep in my picture

Mark Areas to Remove: Allows me to tag the areas to crop out

.

The idea is to select Mark Areas to Keep and draw a line through the areas that I want to keep. Then I select Mark Areas to Remove and draw a line through all the areas that I want removed.

.

Once I'm ready, I can click the Keep Changes command. My reward is this fine picture of Zen Walkenbach playing banjo on one foot.

.

He looked uncomfortable, so I gave him something to sit on.

.

Thanks for the tip John!

.

As you can see, a Waffle chart is basically a square is divided into a 10×10 grid. Each grid box represents 1% toward a goal of 100% percent. The number of grid boxes that are colored or shaded is determined by the associated metric. This kind of chart is a relatively effective option when you want to add an interesting visualization to your dashboard without distorting the data or taking up too much dashboard real estate.

There are several ways to achieve this visualization in Excel. My personal preferred method is to use an actual chart object. Although there are easier ways to implement this type of visualization (with conditional formatting in cells), using an actual chart object allows me to easily resize and move the visualization to fit my dashboard.

.

In this post, I'll walk you through the steps to set up a waffle chart template and how to duplicate it for as many metrics as you need.

.

Preparing the Data for your Waffle Chart

The first step is to create three ranges of data. Each range will play a part in building out the structure of your waffle chart.

• A range for Horizontal Lines: This range will help you draw the horizontal grid lines for your chart. Here, we simply enter the numbers 1 through 10.
  
• A range for Vertical lines: This range will help you draw the vertical grid lines for your chart. Here, we fill the entire range with 0.
  
• A Range for the Box Values: This Range will hold the values that determine which grid boxes get shaded. We'll fill this range with a formula.
  

.

To fill the Box Values, we enter this formula: MAX(MIN(E$3*100-($B5-1)*10,10),0)

Note the absolute reference designations (the $ signs) in the formula. These will enable us to easily copy and paste our formulas down and across when it comes time to duplicate our waffle char – more on that later.

.

As you can see in the screenshot below, this formula will take our actual metric value and parse it into groups of 10. Notice that the metric is 45%, and the Box Value range shows four 10s and one 5.

.

Changing the metric to 67% will cause the Box Value range to show six 10s and one 7.

.

At this point, we have all you need to start building your chart.

.

Building the your Waffle Chart

Creating the actual waffle chart can be a little tricky, but the good news is that once you have the chart built, you can easily duplicate it and point it to as many metrics you need.

Here are the detailed steps:

.

Step 1: Plot the Box Values into a Clustered Bar chart.

.

Step 2: Copy the ranges for the Horizontal and Vertical lines, click on the chart, and paste them in.

.

Step 3: Select the Plot Area and apply grey shading.

Note: In Excel 2013, you can right-click on the chart to see a dropdown of all the chart elements. In Excel 2007 and 2010, you can find this dropdown on the far-left of the Layout tab.

.

Step 4: Change the Chart Type for the Horizontal and Vertical Lines series to "Scatter with Markers".

.

Step 5: Click the series for the Horizontal Lines and add a "Y Values" range by pointing to the zeros in the Vertical Lines range. Tip: You can do this right in the formula bar.

.

At this point, your chart will look similar to this

.

Step 6: Set both the primary and secondary axes to a fixed max of 10.

.

Step 7: Delete all axis labels.

.

Step 8: Add Error Bars to both the Horizontal and Vertical Lines series.

.

Step 9: Select the "Horizontal Lines Y Error Bars" element and delete it.

Note: In Excel 2013, you can right-click on the chart to see a dropdown of all the chart elements.

In Excel 2007 and 2010, you can find this dropdown on the far-left of the Layout tab.

.

Step 10: Select the "Horizontal Lines X Error Bars" and format it to Plus – No Cap – Fixed value of 10.

.

Step 11: Select the "Vertical Lines X Error Bars" element and delete it.

.

Step 12: Select the "Vertical Lines Y Error Bars" and format it to Plus – No Cap – Fixed value of 10.

.

Step 13: Select the Vertical Lines series and set the Marker Options to NONE.

.

Your chart will now look similar to this.

.

Step 14: Format the Error Bars so that they are white.

.

Step 15: Highlight the Box Values series and set the Gap Width to 0.

.

Step 16: Expand the plot area so that it fills the entire chart, and then apply an appropriate color to the Box Values series.

.

Step 17: Optionally, you can make your chart title read back the actual metric value by clicking the chart title, then clicking inside the formula bar, then pointing to the cell holding the metric value.

.

At this point, you have completed your waffle chart.

.

Duplicating your Waffle Chart for more Metrics

As mentioned before, since the waffle chart takes some time and effort to create, you wouldn't want to build each one from scratch. Instead, you can simply duplicate your waffle chart and point it to a new set of data.

.

First, you will want to copy the range that holds your metric value and Box Values.

.

Now you can enter the appropriate metric value into you newly pasted range.

Next, copy your waffle chart and paste it as a new chart on the spreadsheet.

Finally, click the Box Values series for you newly copied chart and point it to the Box Values range for your new metric.

.

You can repeat this process for as many metrics you need to cover.

And remember, because these are charts, you can resize them to as large or small as you need them. You can also move them around as needed.

Feel free to Download the sample file to get a starter model with these charts already built out.

..

So how do these fit into dashboarding best practices?

Look, let's call a spade a spade here. These charts give you a way to jazz up performance against a goal. You could technically get the point across by merely showing the percentages.

I would say these are marginally better than standard gauges, because they can more be easily compared and they can be more effectively shrunk down to take up less real estate than gauges (because of their square-ness). Nevertheless, they still have the same drawbacks as gauges (they effectively show only one metric, they lack trending, etc.)

.

That being said I have found some success using these in real life dashboards where jazzing "performance vs. goal" was important to my customer. I can definitely use these Waffle charts with a relatively clear BI conscience.

.

Anyway – I'm back from the brink of death and I want to share a couple of things before heading into the weekend.

.

Another Live Excel Training Event in October
The Dallas Power Analyst Boot Camp sold out in record time. All seats have been filled ; thanks to everyone who signed up.

Because this event was filled up so fast, I'm considering adding another event in October. Maybe I can get one of the other MVPs to do it with me.

What city should I go to? (Atlanta, Miami, Charlotte, Phoenix, Los Angeles, Washington DC, Baltimore, any others)?

.

The Access 2013 Bible Hits Stores and Amazon on Monday!

Dick Kusleika (of Daily Dose of Excel fame) wrote the Access 2013 Bible with me. I'd like to say we knocked this out over a couple of beers like men. Sadly, the truth is uglier. We were both alone in our own houses (probably at 2am in black socks and white boxers) feverishly trying to get a few pages at a time done before stumbling to our day jobs. DK and I tackled this book over the course of about 5 months.

.

I know I'm biased, but I truly think that this version of the Access Bible is the best in the series. We reorganized the chapters, we added a bunch of new content, and Wiley added a new (very efficient) layout.

.

Consider picking it up if you want to advance your skill-set to encompass database design, application development, or integrating Excel and Access.

This book is fun for the whole family.

.

I'll be back on Monday.

In that post, I explained that Ctrl+J is the hot-key representation of carriage returns. So you can find and replace carriage returns by entering Ctrl+J in the Find What field.

.

This got me to thinking; could we use Ctrl+J in the Text to Columns dialog box to split data into several columns based on carriage returns? It turns out we can.

.

Here, I have several address blocks containing carriage returns. I'd like to parse these addresses so that they go across several columns.

.

So I highlight the column and fire the Text to Columns command on the Data tab of the Ribbon.

After selecting Delimited and the Next button, I get this dialog box.

As you can see in the Data Preview, Text to Columns initially just recognizes the first line of each address block.

.

So I Click the Other Delimiter and enter Ctrl+J. The field looks blank, but you can immediately see that Excel brings in the rest of the address.

.

Now to parse the city state and zip, I simply click the Comma delimiter.

.

The reward for my efforts is a set of columns that contain my parsed addresses.

.

I don't know why I never tried this in the past.

Maybe I just never ran into this situation.

Nevertheless, here it is.

"DataPig, is there any way to find and replace Alt+Enter without resorting to VBA?"

Good question Allen. The answer is yes; here's how.

.

Apparently Allen has a spreadsheet where the creator entered data, pressed Alt+Enter to force a carriage return, and then entered more data. Like this for example:

.

Pressing Alt+Enter allows you to create a nice effect where your text is on separate lines.

But what happens when you want to find and replace a bunch of these carriage returns?

.

Simple.

Just call up the Find and Replace dialog box.

In the Find What input, enter Ctrl+J. It will look empty, but don't worry about that.

In the Replace With input, enter something like a Space.

.

Click on the Replace All button and Bingo!

.

There you go Allen.

And for those VBA nerds out there, here's a way to accomplish this with code.

Sub Clear_Alt_Enter()
Dim MyRange As Range
For Each MyRange In ActiveSheet.UsedRange
MyRange = Replace(MyRange, Chr(10), " ")
Next
End Sub