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

I’ve written a blog post about building Grid Maps in Excel using the worksheet grid combined with Conditional Formatting. In this blog post I’m going to create a Grid Map—again inside the worksheet grid—where relevant cells of the map include sparklines.

The map below show the year-over-year growth rates of international students studying at U.S. colleges and universities for the years 2009 – 2018. Blue color indicates positive growth and red color decline.

1. On the worksheet, select the 2-digit ANSI codes of all states (range A27:A77).
2. Next, on the Home tab click the Fill color icon in the Font group and choose a color of your choice (light grey).
3. Next, while the A27:A77 range is still selected, click on the Borders icon in the Font group under the Home tab. On the dropdown menu click More Borders.
4. 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.
5. Click the Ok button to close the Format Cells dialog box and the worksheet should look like this:

HOW TO CREATE A GRID MAP WITH SPARKLINES IN EXCEL

6. On the worksheet, select all the growth rates in the range C27:L77. Next, on the Insert tab, click on the Column Sparkline icon in the Sparklines group.
7. In the Create Sparklines dialog box, set the Location Range to refer to A27:A77 as per the screenshot below:

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

9. On the worksheet, select the 2-digit ANSI codes of all states (range A27:A77).
10. Next, on the Home tab click the Decrease Font Size icon in the Font group to reduce the font size to 9.
11. Next, while the A27:A77 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:

12. On the worksheet, select columns N, P, R, T, V, X, Z, AB, AD, AF and AH (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.7.
13. 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.
14. 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 A27. Next, while the Sparkline Group is highlighted in blue (Range A27:A77), click the Sparkline Axis icon in the Group group under the Sparkline tab. On the dropdown menu click the Same for All Sparklines under Vertical Axis Minimum Value Options.
16. Repeat the same procedure while this time selecting the Same for All Sparklines under Vertical Axis Maximum Value Options.
17. Next, click on the Marker Color icon in the Style group. In the dropdown menu change the Negative Points color to red. The worksheet should look like this:

18. Next, select cell A27 and press Ctrl + X on the keyboard to cut its content. Then, look for the place of Alabama on the Grid, cell AA18, and press Ctrl + V to paste the cell.
19. Do the same for cell A28 (Alaska) and paste it into cell M6. Here are few more examples:
     A29 (Arizona) --> Q16
     A30 (Arkansas) --> W16
     A31 (California) --> O14
The spreadsheet should look like this:

20. Continue the same process for all the remaining states.
21. Add a title, data source and scales for one of the states. The chart 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 9 years of growth rates for the years 2009 – 2018.