Views > Excel charts > How to create a grid map with square pies charts in excel

This is the fourth in a series about how to create Grid Maps in Excel. This time it’s about grid maps with square pie charts.

If you’ve missed the first three blog posts, here’s a Tile Grid Map using the grid and Excel’s Conditional Formatting capabilities. This one is about creating Grid Maps using circles with Excel’s charting capabilities. And this one is about Tile Grid Maps with Sparklines within the grid.

The Grid map below shows, for 51 U.S. states, the percent of the population fully vaccinated by COVID 19 vaccine as of July 1, 2021. The source of the data is the CDC.

Each state is represented by a grey square. The % is represented by the orange filling working as a square pie chart.

5. On the worksheet, select the 2-digit ANSI codes with percentages of all states (range Z27:Z77).
6. Next, on the Home tab click the Fill color icon in the Font group and choose a color of your choice (light grey).
7. Next, while the Z27:Z77 range is still selected, click on the More Borders icon in the Font group under the Home tab. On the dropdown menu click More Borders.
8. In the Format Cells dialog box, on the Border tab, under Line, set the Color to a darker shade of grey. Then, under Presets, click the Outline and Inside buttons to indicate the border placement. Click the Ok button to close the Format Cells dialog box.
9. Next, click on the Wrap Text icon in the Alignment group under the Home tab.
10. Next, on the Home tab click the Decrease Font Size icon in the Font group to reduce the font size to 9 and change the font to Trebuchet MS.
11. Next, while the Z27:Z77 range is still selected, click on the Top Align icon followed by a click on the Center icon in the Font group under the Home tab. The worksheet should look like this:

HOW TO CREATE A GRID MAP WITH SQUARE PIE CHARTS IN EXCEL

12. On the worksheet, select all the percentages in the range AC27:AV77. Next, on the Insert tab, click on the Column Sparkline icon in the Sparklines group.
13. In the Create Sparklines dialog box, set the Location Range to refer to A27:A77 as per the screenshot below:

14. Click the Ok button to close the Create Sparklines dialog box. The worksheet should look like this:

19. On the worksheet, select columns C, E, G, I, K, M, O, Q, S, U and W (by clicking the header of each column while pressing the Ctrl key on the keyboard). Right-click over the selected columns and then, on the shortcut menu, click Column Width and set it to 0.8.
20. Next, on the worksheet, select rows 6, 8, 10, 12, 14, 16, 18 and 20 (by clicking the header of each row while pressing the Ctrl key on the keyboard). Right-click over the selected rows and then, on the shortcut menu, click Row Height and set it to 52.5.
21. Do the same for rows 7, 9, 11, 13, 15, 17 and 19 by setting their Row Height to 7.5. The worksheet should look like this:

15. On the worksheet, select cell Z27. Next, while the Sparkline Group is highlighted in blue (Range Z27:Z77), click the Sparkline Axis icon in the Group group under the Sparkline tab. On the dropdown menu click the Custom Value under Vertical Axis Maximum Value Options.
16. In the Sparkline Vertical Axis Setting dialog box, set the Enter the maximum value for the vertical axis to 1. Click the Ok button to close the dialog box.
17. Repeat the same procedure for the Vertical Axis Minimum Value Options, while setting the Custom Value to 0.
18. Change the Sparkline Color to light orange and The worksheet should look like this:

The data source for the Grid Map is shown below. Each state is defined by its name, its 2-digit ANSI code and the percent of the population fully vaccinated by COVID-19 vaccine by the state of residence.