Advanced Excel - Waffle Chart

Advanced Excel – Waffle Chart

Waffle chart provides magnificence to your information visualization, if you wish to show work progress as share of completion, purpose achieved vs Goal, and so forth. It offers a fast visible cue of what you need to painting.

Waffle chart is also referred to as Sq. Pie chart or Matrix chart.

What’s a Waffle Chart?

Waffle chart is a 10 × 10 cell grid with the cells coloured as per conditional formatting. The grid represents values within the vary 1% – 100% and the cells will probably be highlighted with the conditional formatting utilized to the % values they include. For instance, if the share of completion of labor is 85%, it’s portrayed by formatting all of the cells that include values <= 85% with a particular colour, say inexperienced.

Waffle chart appears to be like as proven under.

Waffle Chart

Benefits of Waffle Chart

Waffle chart has the next benefits −

  • It’s visually fascinating.
  • It is vitally readable.
  • It’s discoverable.
  • It doesn’t distort the info.
  • It offers visible communication past easy information visualization.

Makes use of of Waffle Chart

The Waffle chart is used for utterly flat information that provides as much as 100%. The share of a variable is highlighted to provide the depiction by the variety of cells which can be highlighted. It may be used for varied functions, together with the next −

  • To show the share of labor that’s full.
  • To show the share of progress that’s made.
  • To depict the bills incurred as towards the funds.
  • To show Revenue %.
  • To painting the precise worth achieved as towards the set goal, say in gross sales.
  • To visualise the corporate progress as towards the objectives which can be set.
  • To show the move share in an examination in a school / metropolis/ state.

Making a Waffle Chart Grid

For the Waffle Chart, you have to first create the 10 × 10 Grid of sq. cells such that the Grid itself will probably be a sq..

Step 1 − Create a 10 × 10 sq. grid on an Excel sheet by adjusting the cell widths.

Create 10*10 Square Grid

Step 2 − Fill the cells with % values, beginning with 1% within the left-bottom cell and ending with 100% within the right-top cell.

Step 3 − Lower the font dimension such that each one the values are seen however don’t change the form of the grid.

Decrease Font Size

That is the grid that you’ll use for the Waffle chart.

Making a Waffle Chart

Suppose you could have the next information −

Data For Waffle Chart

Step 1 − Create a Waffle chart that shows the Revenue% for the Area East by making use of Conditional Formatting to the Grid you could have created as follows −

  • Choose the Grid.
  • Click on Conditional Formatting on the Ribbon.
  • Choose New Rule from the drop down record.
  • Outline the Rule to format values <= 85 % (give the cell reference of the Revenue %) with fill colour and font colour as darkish inexperienced.

Green Font Color

Step 2 − Outline one other rule to format values > 85 % (give the cell reference of the Revenue %) with fill colour and font colour as gentle inexperienced.

Light Green Font Color

Step 3 − Give the Chart Title by giving reference to the cell B3.

Cell B3

As you possibly can see, selecting the identical colour for each Fill and Font allow you to not show the %values.

Step 4 − Give a Label to the chart as follows.

  • Insert a Textual content field within the chart.
  • Give the reference to the cell C3 within the Textual content field.

Give Chart Label

Step 5 − Coloration the cell borders white.

White Cell Borders

Your Waffle chart for the Area East is prepared.

Create Waffle charts for the Areas, i.e. North, South and West as follows −

  • Create the Grids for North, South and West as given within the earlier part.
  • For every Grid, apply conditional formatting as given above based mostly on the corresponding Revenue % worth.

You may also make Waffle charts for various areas distinctly, by selecting a variation within the colours for Conditional Formatting.

Different Regions

As you possibly can see, the colours chosen for the Waffle charts on the suitable are various from the colours chosen for the Waffle charts on the left.