I saw this really nice tile grid map with slope charts from Philip Bump in the Washington Post a few weeks ago. It shows changes in COVID-19 cases (per 1,000 residents) and deaths (per 100,000 residents) at three points during the pandemic (March 12, 2020; January 20, 2021; November 30, 2021). To make my version of the map, I pulled the COVID-19 data from the CDC and population data from the Census Bureau.
Did you know that you can make this in Excel? I’ve written several posts in the past about creating tile grid maps in Excel, including waffle charts and vertical bar charts. To show changes over time in a tile grid map, I’ve used Excel’s sparkline tool. That works pretty well when inserting sparklines as vertical bar charts, but it doesn’t work quite as well when using lines because the lines don’t look great when pulled out of Excel–they get very pixelated and blurry.
But Philip’s tile grid map with pairs of slope charts got me thinking that there might be a better way. Instead of using the sparklines tool, I created a series of line charts. When I add gaps (empty cells) between the pairs of data points, Excel renders those as gaps in the lines so that it looks like a series of slope charts. I then added a series of scatterplot points with vertical error bars to add the gaps between the states. A final set of scatterplots are then added to place the state abbreviations within each state cell. You can get a sense of what I mean in this image.
To make this more reproducible and easier to control, I use a lot of formulas to get everything where I want it. It’s essentially a series of VLOOKUP formulas, sometimes embedded within IF statements. It does require a bit of formatting to change the line colors and styles and whatnot, but that’s not too bad. I admit that that part is a bit repetitive and tedious, but once it’s done, the map is really done for any kind of data you’d like to use. Because I use a series of formulas to pull out the three dates for the exact series I want to use, it doesn’t require starting all over again. Basically, I’m trying to make Excel graphs closer to what you could do with computer code–make it as reproducible as possible.
I’ve created a full step-by-step tutorial to help you build this map, which you can watch below or on my YouTube channel. It’s an admittedly long video, but I’ve divided it into three parts so you can just skip ahead if you want: In Part 1 (the beginning), I prepare the raw data; in Part 2 (starting at the 7-minute mark), I do the calculations for the map; and in Part 3 (starting at the 13-minute mark), I create the maps. If you don’t want to bother building the whole thing yourself from scratch, you can purchase my Excel file for a measly $3 (which helps me offset the costs of video editing).
If you use this template in your own work, please let me know!
