Making Excel maps without VBA

If you want to make a choropleth/thematic map in Excel without programming perhaps conditional formatting is all you need. Here is how to do it:

1. Select a few hundred columns and rows;
2. Set width and height to 3 (more or less, depending on the resolutions you want);
3. Set font size to 1;
4. Place a map file over those cells and make it transparent (if possible, use a WMF file) so that you can see the cells and the map borders;
5. In a new sheet, add a table with all the regions and some random data;
6. Get back to the first sheet and for each cell within a region enter a lookup function and change cell background (the lookup must include the region ID);

1. Save your work and make a copy of this sheet;

1. Select a rectangle around your map and add a border;
2. Choose a conditional formatting rule for the entire rectangle;
3. Clear the existing cell background color;
4. You should see a colored map using the conditional formatting rule.
5. Inside the rectangle, merge a few cells and create a legend (in this case, I used percentiles to automatically update it);
6. You can delete the overlapping file, but the maps will look nicer with the borders;
7. Use the camera tool to copy the map to a new sheet.

Here is the sheet for the example above:

Enjoy!