The ONE Campaign published a Tile Grid Map of Africa a couple of weeks ago in their new feature on how the internet can help end extreme poverty. The map is really interesting because it combines a Tile Grid Map with Waffle charts in which the latter is embedded in the former. For complete lack of a better term, I’m going to call it a Tile Grid Waffle Chart Map, which I know is terrible, but at least it’s clear.

It’s no secret that I’m a fan of Tile Grid Maps (TGM). They overcome some of the basic issues with using standard maps for data visualization, namely geographic distortions. It’s also a bit easier to create a TGM because you just need boxes, so for those who can’t access a mapping tool (for monetary or technical reasons), a TGM is a decent alternative.

I’m not sure what tool the ONE Campaign used to create this map; I’m guessing Adobe Illustrator or InDesign. But I wanted to take a shot at creating it in Excel. And take a shot I did. A winning shot.

UPDATE #1: A couple of people on Twitter noted that this map doesn’t include all 54 countries in Africa. I copied the ONE Campaign map and didn’t try to create a new one with all of the countries, though that is something I should (and will try to) do.

UPDATE #1b: I’ve now added a new version of the African map to the Shop. You will get both files with your purchase.

UPDATE #2: Also on Twitter, FFunction–Montreal-based data visualization firm–chimed in that they created this project using a combination of SVG and Adobe Illustrator. They also noted that they included the 42 countries because those were the ones for which they had quality data. Kudos to them on a job well done!

UPDATE #3: The original US visualization failed to properly label Delaware and Pennsylvania. Those have been fixed now and updated in the Shop, if you want to buy the template file.

This Tile Grid Waffle Chart Map combination uses the VLOOKUP formula, conditional formatting, and a little number formatting trick, all of which I’ve used in previous TGM tutorials (here, here, here, and here). Essentially, I create a NxN grid of small columns and rows to create the square shapes for each country. I then place a VLOOKUP formula within an IF statement to compare the data value for each country to a “root” grid with values from 1 to 100. That gives me a series of 1s and 0s in the country grids to which I then apply conditional formatting. Instead of manually drawing lines around each country, I insert extra rows and columns and color them white. Finally, I use text boxes to add the country names. This wasn’t my ideal solution, but I struggled to make each country a square with the country name inside the square and color the rest.

There are a lot more details here, and instead of writing down the steps one-by-one, I put it all in this short video tutorial (or watch below).

As a bonus, I also created a US map version of this chart—all 50 states plus Washington, DC. I use the same approach and talk about some of the details in the video. I’ve also created a version using Excel’s Data Validation tool, which allows the user to select the variable she wants to plot on the map.

 

I’m trying to raise a bit of money to help support the production of the PolicyViz Podcast, so if you’re interested in these maps, please consider purchasing the Excel files from the PolicyViz Shop. You don’t need to do much to get these to work—just drop in your data—so it’s a great investment!