Skip to main content

How to quickly create waffle chart in Excel?

A waffle chart is also called square pie chart which works on a percentage basis where one square represents one percent of the whole as the following screenshot shown. In this article, I will talk about how to create a waffle chart in Excel worksheet.

Quickly create a waffle chart in Excel


Quickly create a waffle chart in Excel

Unfortunately, there is no waffle chart in the list of Excel’s default charts, so, please follow the below step by step to create a waffle chart.

1. First, please select 10 rows and 10 columns and resize it to make it look like the grid as shown in the waffle charts, and then enter a percentage value you want to use for the waffle chart, see screenshot:

2. Then, you should enter values from 1% to 100% in cells starting from the first cell of the last row in the grid. You can use the below formula to insert the percentage from 1% to 100% in the grid at once.

=(COLUMNS($A11:A$11)+10*(ROWS($A11:A$11)-1))/100

Note: Enter this formula into the first cell of the last row, and then drag the fill handle to right and then to above to apply this formula to all cells of this grid. A11 is the first cell of the last row, you can change it to your need.

3. After getting the result, keep the formula cells selected, please click Percent Style under the Home tab to change the values to the percentage values, see screenshot:

4. Then, select the percentage values, and then click Home > Conditional Formatting > New Rule, see screenshot:

5. In the New Formatting Rule dialog box, please do the following operations:

  • (1.) Select Format only cells that contain option in the Select a Rule Type list box;
  • (2.) Then, specify the cell value between 0 and L2 (L2 is the linked cell with the waffle chart) under the Format only cells with section;
  • (3.) And then, click Format button, in the Format Cells dialog box, select the same fill color and font color to make the cell value invisible.

6. Then, click OK button, and you will get the below screenshot:

7. And then, select the 100 grids, and format the cell with the same fill color and font color (the color is lighter than the color in the conditional formatting), see screenshot:

8. After creating the main waffle chart, then, you should insert a label for the chart. Please click Insert > Text Box > Draw Horizontal Text Box and draw a text box.

9. After inserting the text box, select the text box, and then enter the formula: =$L$2 into the formula bar, and press Enter key, see screenshot:

Note: The L2 is the linked cell with the waffle chart.

10. Then, drag the text box to put it on the waffle chart, then, you should format the text box with no fill and no outline, and change the font size and color for the value in the text box. The waffle chart will be created successfully as below screenshot shown:

Note: You can set all borders for the cells and specify a color for them as you need.


More relative articles:

  • Create A Step Chart In Excel
  • A step chart is used to show the changes happened at irregular intervals, it is an extended version of a line chart. But, there is no direct way to create it in Excel. This article, I will talk about how to create a step chart step by step in Excel worksheet.
  • Create A Win Loss Sparkline Chart In Excel
  • In Excel, a win loss chart displays positive and negative values with different colors which can help you to view the trends of multiple data. This article, I will talk about how to create a simple win loss sparkline chart in cells.
  • Create Progress Bar Chart In Excel
  • In Excel, progress bar chart can help you to monitor progress towards a target as following screenshot shown. But, how could you create a progress bar chart in Excel worksheet?

  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations