I saw this interesting post from Len Kiefer yesterday about creating a Pixel map of the U.S. using R (which he based off this blog post by Colin Fay). While tile grid maps and hexagon maps address some of the issues with geography scaling, the Pixel map just seems like a fun and different way to map data (and it has all the issues with Mercator projections, so let’s not even argue that point).
Because the Pixel Map is made up mostly of squares (or rectangles, actually), it seemed like a relatively easy thing to make in Excel. Thus, while it doesn’t address many of the fundamental issues with maps, it is something you can easily use in Excel to quickly visualize your data. And I’m giving you my Excel file, so you can just simply drop in your data and not even have to make it yourself.
By my count, this Pixel Map has 11 triangles, which I don’t know if you can actually do in Excel, so my Excel version will not be an exact replicate of the original, though pretty close. (Technically, I could approximate the triangle by using an infinite number of squares, but I went for a simpler approach of just ignoring them.)
How I did it
The overall approach here takes four steps: create map shell in a spreadsheet and manually type in the state abbreviations; use a VLOOKUP formula to pull over the data; apply conditional formatting; and hide the numbers (similar to this heatmap tutorial).
First, I took Len’s map and drew a grid over it in PowerPoint. This allowed me to more easily count the number of squares for each state and check the boundaries. Then, in Excel, I created my grid—I started with squares but ultimately went with rectangles (25 pixels wide by 34 pixels tall) to better match Len’s projection. (By the way, I’m sure that last sentence just angered a whole bunch of cartographers—if anyone has a better, more mathematical way to set the aspect ratios of the cells, please do let me know.)
In each cell of my Excel file, I manually typed in the abbreviation for each state—I’ll call this the Abbreviation Map. As you can see, this is a hot mess, but I will use the state abbreviations to pull over the data values and color the map.
In the second step, I set up my state-level data table with the state abbreviation, full name, and data value in a separate worksheet. I’m going to simply plot state populations here, but I’ve set this up in such a way that I can replace my Data column with any state-level data.
I duplicate the Abbreviation Map and replace each cell with a simple VLOOKUP formula referencing the original worksheet. For example, in the very top-left part of the country—more or less where Seattle is—I use the following formula: =VLOOKUP(AbbreviationsMap!D2,DATA!$A$1:$C$52,3,0). The first part (AbbreviationsMap!D2) reads the state abbreviation I typed in manually in the Abbreviations Map; the second part (DATA!$A$1:$C$52) refers to my data table; the third argument (3) looks in the third column of the data table for the actual data value; and the final argument (0) makes this an “exact match” so that the VLOOKUP formula looks for the abbreviation exactly.
Now, I copy and paste this formula in all of the cells of the map. Because I used the dollar signs for absolute references in the second argument, the same dimensions of the data table are always used. With this, the map fills in, but because the cells are so small, I get a whole bunch of “###” signs.
Now it’s just a matter of adding some color and hiding those “###” symbols. So, in the third step, I use Excel’s conditional formatting tool to add color. The Conditional Formatting menu can be found in the Home tab. Selecting the Color Scales option allows you to pick a color ramp. Then, selecting OK, the map fills in (this image from Excel 2011 on the Mac).
Finally, I hide the numbers using a handy little trick: Select the entire map and use the keyboard shortcut CTRL+1 (on PCs; CMD+1 on Macs) or right-click and select Format Cells. Either way, go to the bottom of the menu to the Custom option and in the “Type:” box, insert three semicolons (;;;). The numbers will disappear from the map, but will still be in the cells so that the colors will remain. (Alternatively, I could increase the size of the cells or decrease the font size to make the numbers visible.)
As a (preferred) alternative, I added outlines to each state. I did this manually by using the Borders options available in the Font menu of the Home tab. I think this looks a little better, especially when the data values are close for neighboring states.
So that’s it. The Excel file is available for your use. All you need to do is drop in your data in the DATA tab and, if you want, change the colors in the Conditional Formatting menu.
Hi,
Have you tried this with a human body map to show a heat map of body injuries in the workplace?