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

In a previous blog post I showed you how to build a Grid Map using the worksheet grid with Conditional Formatting. In this blog post I’m going to use the same data to build a grid map using circles in charts—as per the below graph showing the US Death Penalty Status in 2020 shown below.

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.

1. First, lets create a chart that defines the foundations for our grid map. To do that, I need to assign each state a row and column in the grid as per the screenshot below:

2. Next, select the range D6:E55 on the worksheet, and then on the Insert tab, in the Charts group, click Insert Scatter (X,Y) or Bubble Chart > Scatter.
3. Set the Maximum Bound of the Horizontal Axis to 13 and its Major Unit to 1. Then, set the Maximum Bound of the Vertical Axis to 9 and its Major Unit to 1. The chart should look like this:

HOW TO CREATE A GRID MAP WITH CIRCLES IN EXCEL

4. Next, I need to create the setup to split the original data into three different series depending on the death penalty status. To do this add the following formulas in the respective cells:
     F6 --> = IF(C6 = $A$2,D6,NA())
     G6 --> = IF(C6 = $A$3,D6,NA())
     H6 --> = IF(C6 = $A$4,D6,NA())
5. Next, select the range F6:H6 then drag the handle to copy the formulas into cells F7:H55. Add column headers and the data source should look like this:

6. Right-click the chart and then, on the shortcut menu, click Select Data.
7. In the Select Data Source dialog box, select the Series1 data series and then click the Edit button under Legend Entries (Series).
8. In the Edit Series dialog box, edit the Series name to refer to cell A5. Click on the Ok button to close the Edit Series dialog box.
9. Next, In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
10. In the Edit Series dialog box, refer to cell F5 in the Series name edit box. Next, edit the Series X values to include cells F6:F55 and the Series Y values to include cells E6:E55. Click on the Ok button to close the Edit Series dialog box.
11. Repeat the same process to add Band2 and Band3 data series to the chart. Press the Ok button to close the Select Data Source dialog box. The chart should look like this:

12. Delete the horizontal and vertical gridlines.
13. Delete the vertical and horizontal axes.
14. Turn off the border of the Chart Area by setting the Border to No line.
15. Set the chart font to Trebuchet MS, its color to black and font size to 9. The chart should look like this:

16. Right-click the Series “Band1” data series and then, on the shortcut menu, click Format Data Series.
17. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Marker, set Size to 22.
18. Next, set Fill to Solid fill and change its Color to dark brown and set Border to No line. The chart should look like this:

19. Repeat the same process for the Band2 and Band3 data series. The chart should look like this:

20. Select the chart and then, on the Format tab, in the Current Selection group, select the Series “State” data series form the dropdown list.
21. Next, on the Chart Design tab, in the Chart Layouts group, click on the Add Chart Element icon and select the Data Labels > Center option.
22. Next, right-click the Series “State” Data Labels and then, on the shortcut menu, click Format Data Labels.
23. In the Format Data Labels pane, with Label Options selected, click on the Values From Cells Select Range button. In the Select Data Label Range refer to cells B6:B55. Press the Ok button to close the Data Label Range dialog box.
24. Uncheck the Y Value and Show Leader Lines options. The chart should look like this:

25. Add title and sources.
26. Change labels color to white.
27. Enter the legend, by adding the following text in cell K2:
    n Death Penalty    n No Death Penalty    n Governor imposed Moratorium
28. Next, edit cell K2 and in the Formula Bar change the font of the letters “n” to Webdings along with their color.
29. Resize the chart so that the vertical and horizontal distance between the dots are equal (Width = 4.7” , Height = 3.4” ). The chart should look like this: