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:
- Select a few hundred columns and rows;
- Set width and height to 3 (more or less, depending on the resolutions you want);
- Set font size to 1;
- 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;
- In a new sheet, add a table with all the regions and some random data;
- 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);
- Save your work and make a copy of this sheet;
- Select a rectangle around your map and add a border;
- Choose a conditional formatting rule for the entire rectangle;
- Clear the existing cell background color;
- You should see a colored map using the conditional formatting rule.
- Inside the rectangle, merge a few cells and create a legend (in this case, I used percentiles to automatically update it);
- You can delete the overlapping file, but the maps will look nicer with the borders;
- Use the camera tool to copy the map to a new sheet.
Here is the sheet for the example above:
Make Excel choropleth/thematic map without programming.
Enjoy!
That’s… a long way 🙂 the alternative to this is to use and Excel hack: when you copy some shape, then select a series in a chart and paste, all series shapes (e.g. columns, bars, or markers) are being replaced with this shape. You can create series for each shape multiplied by number of colors you want to display; set XY-chart and set correct coordinates for each shape to keep the map consistent; then hide / display correct series to display a shape with color needed using IF formulas and N/A value to hide series on a chart.
I have to try that. For this example, I didn’t want to use VBA and/or shapes.
Interesting approach, Jorge…
However I have to say it’s really impractical. It’s more like an experiment than a real solution. The problem from my point of view is the huge amount of work you have to do here. I understand that you wanted to demonstrate what is possible, however, I would tend to use Dmitry idea here because that’s what VBA is made for: Simplifying and automating tasks, that otherwise would get daunting 😉
just my 0.02
Phil
Thanks for the comment Phil. Sure, but VBA is way out of many users’ comfort zone, even if this means spending hours defining the regions. Should the average Excel user learn basic VBA? Obviously they should.
Unfortunately, VBA is virtually unusable in the latest versions of MSOffice, as it is no longer an option – unless running in “degraded” mode. Certainly no good on a MAC. I miss it somewhat, I must agree!
I was looking for a way to use maps on Excel and I can barely believe that it’s possible to do such high-level documents without using VBA. I now have to find out how to do it with a map of France but I should find one.