In a recent article about the political geography in Scotland, The Guardian created a tile grid map, but instead of inserting text in the grid, they inserted Sankey diagrams. It was an interesting approach, and Alberto Cairo remarked that, “I feel like the charts on the main map could have been bigger, but I like the results anyways, as the graphic reveals the downward trend of the Labour Party.”
Intrigued, Matt Chambers tried to recreate the map in Tableau. He didn’t quite recreate the Sankey diagram, but he did build a tile grid map with stacked area charts (and then showed how to do it). Instead of vote shares in Scotland, Matt used percentage of votes for Democrats and Republicans in U.S. presidential elections since 1964.
I, in turn, was intrigued by Matt’s graph and wanted to see if I could remake it in Excel. Having just published two posts about building tile grid maps in Excel (a static and an interactive version), I thought this might be a nice extension. As before, I’m providing the Excel file for your use (see below).
I could, of course, create 51 different area charts, resize them, and try to fit them into the tile grid map layout, but that seems excessive and too difficult. Instead, I’m going to use the sparklines feature available in versions of Excel 2010 and later.
Excel’s sparklines won’t allow me to create area charts—though that seems like an obvious extension—but does allow me to create column charts. I’m going to use Matt’s data, and because there are only two series, which sum to 100%, a column chart will work fine. It may not be ideal, but I think this is the best I can do with the sparklines feature.
Step 1. Set up the tiles and decide how you want to lay out the states. I’ve resized each cell to be 0.9”x0.9”.
Step 2. The data (Democrat vote shares) are in a wide format with states along the rows and years along the columns. I create column sparklines for each state in the column next to the data table. The Sparklines menu is available in the Insert tab of the ribbon (in Excel 2010 and 2013; it’s in the Charts menu in Excel 2011). Simply select the data and point to the cells in which you’d like to place the sparklines.
Step 3. Cut and paste the sparkline charts to the map. You do need to cut the charts and move them; copying them will change the data reference. (This is obviously a fairly tedious task, but I’ve already done it for you, so now all you need to do is update the data and your sparkline charts will all update automatically.)
Step 4. I’ve added the state abbreviations to each cell by using a text box. The text boxes are sized to the same size as the map cells (0.9”x0.9”) and the fill and outlines are all turned off. I’m able to place the text boxes exactly where I want in each cell by using the “Snap to grid” option, which works by holding down the Option key (or the Command key on the Mac) and then moving each text box to each cell.
That’s about it. I also tried a few alternatives to the basic map.
-In this one, I changed the metric to be the Democrat voting shares above 50%. You end up with a lot more white space here, which I like, and you see the trends a bit more clearly, maybe.
-In this one, I highlighted the highest values by using the options in the Design tab. I don’t think this really adds all that much, but the built-in sparklines features can be used in some nice ways.
-Finally, using the Democrat share above 50% as in the first alternative, I converted the Column charts to Win/Loss charts so that you get a binary result for Democrat or Republican. I found these a little harder to view, so I added borders around the text boxes. But the whole thing looks a bit more cluttered to me.
In the end, I think I prefer the original map. I don’t have Sankeys, and I don’t have area charts, but the column sparkline charts can be added fairly easily and are sufficient for this particular visualization.
What do you think? Could you see yourself using this approach? Let me know in the comment boxes below or on Twitter.
I neglected to include another alternative version of the map in this post. Fortunately, Jeff Shaffer gave me a little nudge to add the red (Republican) color to each cell by coloring the cell backgrounds. The text box borders are then changed to white and the thickness increased (here to 7pt) so they fill in the space and cover the bottom of the cell (the sparklines don’t actually sit at the bottom of the cell). So, just another version should you wish to have both parties (sides) included in each state cell.