SAP - Advanced Excel Chart

Back to Course

Lesson Description

Lession - #490 Advanced Excel Charts – Waffle Chart

Waffle chart adds beauty to your data visualization, if you want to display work progress as percentage of completion, thing achieved vs Target, etc. It gives a quick visual cue of what you want to portray. Waffle chart is also known as Square Pie chart or Matrix chart.

What's a Waffle Chart?
Waffle chart is a 10 X 10 cell grid with the cells colored as per conditional formatting. The grid represents values in the range 1- 100 and the cells will be highlighted with the conditional formatting applied to the values they contain. For illustration, if the percentage of completion of work is 85, it's portrayed by formatting all the cells that contain values

< = 85 with a specific color, say green. Waffle chart looks as shown below.

Advantages of Waffle Charts
Waffle chart has the following advantages-

• It's visually interesting.

• It's very readable.

• It's discoverable.

• It doesn't distort the data.

• It provides visual communication beyond simple data visualization.

Uses of Waffle Chart
The Waffle chart is used for fully flat data that adds up to 100. The percentage of a variable is highlighted to give the depiction by the number of cells that are highlighted. It can be used for colorful purposes, including the following –

• To display the percentage of work that's complete.

• To display the percentage of progress that's made.

• To depict the charges incurred as against the budget.

• To display Profit.

• To portray the actual value achieved as against the set target, say in sales.

• To visualize the company progress as against the goals that are set.

• To display the pass percentage in an test in a council/ megacity/ state.

Creating a Waffle Chart Grid
For the Waffle Chart, you need to first produce the 10 X 10 Grid of square cells similar that the Grid itself will be a forecourt.
Step 1 produce a 10 X 10 square grid on an Excel distance by adjusting the cell extents.
Step 2 Fill the cells with values, starting with 1 in the left-bottom cell and ending with 100 in the right-top cell.
Step 3 Decrease the font size similar that all the values are visible but don't change the shape of the grid.
This is the grid that you'll use for the Waffle chart.

Creating a Waffle Chart
Suppose you have the following data –
Step 1 produce a Waffle chart that displays the Profit for the Region East by applying Conditional Formatting to the Grid you have created as follows-

• Select the Grid.

• Click Conditional Formatting on the Ribbon.

• Select New Rule from the drop down list.

• Define the Rule to format values< = 85( give the cell reference of the Profit>
with fill color and font color as dark green.
Step 2 Define another rule to format values> 85( give the cell reference of the Profit
with fill color and font color as light green.
Step 3 Give the Chart Title by giving reference to the cell B3.

As you can see, choosing the same color for both Fill and Font enable you not to display the values.
Step 4 Give a Label to the chart as follows –

o Insert a Text box in the chart.

o Give the reference to the cell C3 in the Text box.
Step 5 Color the cell borders white.

Your Waffle chart for the Region East is ready.
produce Waffle charts for the Regions, i.e. North, South and West as follows –

• produce the Grids for North, South and West as given in the previous section.

• For each Grid, apply conditional formatting as given above based on the corresponding Profit value.

You can also make Waffle charts for different regions distinctly, by choosing a variation in the colors for tentative Formatting.

As you can see, the colors chosen for the Waffle charts on the right are varying from the colors chosen for the Waffle charts on the left.