How many times have you heard someone rail against the use of maps in data visualization? The areas are not scaled to the data; geography distorts the patterns; you can’t accurately discern the quantities….
And yet, people love maps. They are familiar. We know where we live, where we’ve been, where we might want to go. Ah, but I digress….
Lately, the Tile Grid Map has become more and more popular. In the Tile Grid Map, the map areas are given a uniform size and shape—usually a square—and are arranged to approximate their real-world position. You’ve seen them from FiveThirtyEight, the Economist, the Washington Post, the Urban Institute, and many others. Danny DeBelius (formerly at NPR) wrote a great post about maps last year in which he wrote that, “Tile grid maps avoid the visual imbalances inherent to traditional choropleths, while keeping the map a quick read by forgoing the complexity of cartograms with map areas sized by a variable data point.” NPR went even further, implementing Hexagon Tile Grid Maps.
I’m going to ignore the pros and cons of Tile Grid Maps here—for example, where certain states should or should not go—and instead show you how to create a Tile Grid Map in Excel. Tomorrow, I’ll extend it to create an interactive version and pair it with an interactive bar chart.
Tile grid maps can be fairly easily constructed in Excel, but require a bit of upfront investment. Of course, I’m providing you with the Excel file, so a lot of the tediousness is already taken care of; you’ll just need to insert your own data. There are basically four steps:
- Size the cells into squares;
- Use a formula in each cell (state) to point to your data;
- Apply Number Formats for the labels; and
- Apply Conditional Formatting to the map for color.
I’ll walk you through steps 2-4 below (step 1 is easy), but first, here’s what the worksheet looks like. The map takes up columns B-L and then off to the right, I’ve created a data table for each state (it actually uses the VLOOKUP formula to pull data from an entirely different worksheet, but that’s not particularly necessary or relevant). To the right of that, I’ve calculated the median of this series; I’ll use the median to divide the states into two groups.
Steps 2 and 3 require a bit of tedious up-front work, but once that’s done, the map can be easily replicated for different colors or different numbers of groups:
- Obviously, you need to first plot out the states in the (square) cells. For each state, I grab the data from the data columns; for example, the state cell for Alaska in the map simply points to cell V2.
- Apply a Custom Number Format for each state. The “\AK” custom label for Alaska hides the number and replaces it with the “AK” abbreviation.
The final step is to color the map; I’ll use Excel’s Conditional Formatting to do so. Conditional Formatting is used to set the colors in the map based on the number of groups you want to show (above the median, fill the cell with blue; below the median, fill it with yellow). To apply it here, select the entire map and use the “formula” option in the Conditional Formatting menu. You’ll need three formulas: One for each color (above and below the median), and another for the white color that will apply to cells with a blank value (i.e., no state). (I wrote an earlier post about Conditional Formatting here if you want to learn more.)
I’ve created several different maps for you in this file that correspond to maps that divide the data into 2 groups (above and below the median), 3 groups (split into thirds), 4 groups (quartiles), and 5 groups (quintiles). You can easily modify the data, the map colors, and how the data are split into groups.
What do you think? Useful? Let me know if you have questions or issues and I’ll try my best to help you out.
