twitter-mono
facebook-mono
linkedin-mono
youtube-mono

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

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.

How to create a grid map with square pies charts in excel 3

1. In cell Z27 enter the following formula:
  = AA27 & CHAR(10) & TEXT(AC27,"0.0%")
2. Select cell Z27 and drag the handle of the cell to copy the formula into the range Z28:Z77.
The worksheet should look like this:

How to create a grid map with square pies charts in excel 4

22. Next, select cell Z27 and press Ctrl + X on the keyboard to cut its content. Then, look for the place of Alabama on the Grid, cell P18, and press Ctrl + V to paste the cell.
23. Do the same for cell Z28 (Alaska) and paste it into cell B6. Here are few more examples:
     Z29 (Arizona) --> F16
     Z30 (Arkansas) --> L16
     Z31 (California) --> D14
The spreadsheet should look like this:

3. On the worksheet, select the range AC27:AC77 then press Ctrl + C to copy the content to the clipboard.
4. Select the range AD27:AV77 and press Ctrl + V to paste the content of the clipboard onto the sheet as per the screenshot below:

How to create a grid map with square pies charts in excel 5
How to create a grid map with square pies charts in excel 6
How to create a grid map with square pies charts in excel 7
How to create a grid map with square pies charts in excel 8
How to create a grid map with square pies charts in excel 10
How to create a grid map with square pies charts in excel 9
How to create a grid map with square pies charts in excel 11

24. Continue the same process for all the remaining states.
25. Add a title, data source and scales for one of the states. The chart should look like this:

How to create a grid map with square pies charts in excel 1

Or alternatively buy product on Gumroad

Or alternatively buy product on Gumroad

Or alternatively buy product on Gumroad

How to create a grid map with square pies charts in excel 2

Download Excel Model

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.

How to create a grid map with square pies charts in excel 12

It should be noted that comparing areas across geographies is more difficult than say using position as an encoding mechanism such as in the dot plot below.