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

Data maps are a great tool to combine spatial and statistical data together. Nonetheless, data maps do not come without shortcomings. One such flaw is to mistakenly associate the size of a geographic area with the statistical data related to that shape or area. Take for instance the 2016 electoral map of the US presidential elections shown below. It shows in red the counties where Trump won and in grey the counties where Clinton won. Based on the map you may conclude that Trump won about 80% of the electoral votes. The reality is different though. Trump got 304 out of 538 electoral votes—or about 57%. For a detailed analysis of this case I recommend Alberto Cairo’s book How Charts Lie.

Source: How Charts Lie by Alberto Cairo.

The data source for the Grid Map is shown below. Each state is defined by its name, its 2-digit ANSI code and a status value that could be 1 for states with death penalty, 2 for states without death penalty and 3 for death penalty states with gubernatorial moratoria.

HOW TO CREATE A GRID MAP IN EXCEL

1. On the worksheet, select columns F to Q. Right-click over the selected range and then, on the shortcut menu, click Column Width and set it to 3.8.
2. On the worksheet, select rows 5 to 12. Right-click over the selected range and then, on the shortcut menu, click Row Height and set it to 25. The worksheet should look like this:

3. On the worksheet, select cells F5:Q12. Next, on the Home tab click the Fill color icon in the Font group and choose a color of your choice.
4. Next, while the F5:Q12 range is still selected, click on the Borders icon in the Font group under the Home tab. On the dropdown menu click More Borders.
5. In the Format Cells dialog box, on the Border tab, under Line, set the Style to the thick line (second before last) and the Color to white. Then, under Presets, click the Outline and Inside buttons to indicate the border placement. Hence, I’ve defined a matrix 8 rows and 12 columns. Click the Ok button to close the Format Cells dialog box and the worksheet should look like this:

6. Next, type into the matrix created above each state 2-digit ANSI code as per the screenshot below (note that I changed the font color to white and the text alignment to Middle and Center):

7. Now I have to define a new matrix that mirrors the one above and I’m gonna fill it with 0, 1, 2 or 3 depending on the state status. For that enter the following formula in cell F17:
     = IFERROR(INDEX($C$6:$C$55,MATCH(F5,$B$6:$B$55,0)),0)
8. Select cell F17, press Ctrl + C to copy the formula into the clipboard, then select cells F17:Q24 and press Ctrl + V to paste the formulas from the clipboard.
9. Highlight the cell borders of the range F17:Q24. The worksheet should look like this:

10. Highlight the range F5:Q12 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
11. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
12. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
    = F17 = 0
13. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click on the No Color button under Background Color.
14. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

15. Highlight the range F5:Q12 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
16. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
17. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
    = F17 = $A$3
18. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click a color of your choice under Background Color.
19. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

20. Highlight the range F5:Q12 and then on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then select New Rule.
21. In the New Formatting Rule dialog box, click on the Use a formula to determine which cells to format option under Select a Rule Type.
22. In the input line Format values where this formula is true under Edit the Rule Description write the following formula:
     = F17 = $A$4
23. Click on the Format button in the Preview area. Next, in the Format Cells dialog box, under the Fill tab, click a color of your choice under Background Color.
24. Press the Ok button twice to close the Format Cells and New Formatting Rule dialog boxes. The worksheet should look like this:

25. Turn off the gridlines. Add title and sources.
26. To enter the legend, add the following text in cell F3:
          n Death Penalty    n No Death Penalty    n Governor imposed Moratorium
27. Next, edit cell F3 and, in the Formula Bar, change the font of the letters “n” to Webdings along with their color. And the chart should look like this:

One way to solve the problem above is to use a Grid Map such as the one for the US Death Penalty Status in 2020 shown below. Here, equal weight is given to all geographic boundaries—states in this case—using a grid of equal-area squares. Hence the focus is given to the statistical data rather than geographic boundaries and shapes.