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.
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.
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 Bar Chart From Yes No Cells In Excel
- If you have a report which contains the answers Yes and NO, and now, you need to create a chart based on these Yes and No answers. How could you finish this job in Excel?
- 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?