Excel Map Hack

While pivoting data in Excel, it occurred to me that I could drag the latitude data into the row and the longitude data into the column. The result would be a 1980's-Nintendo-looking chunky cell map (which, if I make the cells square, happens to be a pseudo-equirectangular projection). Low and behold...

This map of historic tornado hot spots is really just an Excel pivot table. Read on to see how to spatial-hack Excel in this devious way.

How-To

Any spreadsheet with latitude and longitude columns can be mapped in this way (here's mine, if you want to cheat). No plug-ins. No add-ons. No power-pivot. Let's do this.

1. Create a new column in your spreadsheet, and round your lat and long fields to the whole degree. The zero value there in the formula determines how many places to the right of the decimal place you want to keep. The higher the number the more spatially resolved your map will be. The chunkier map above is rounded to the whole lat and long degree. Rounding to the tenth of a degree blows up the resolution pretty fast (x100 with each decimal place) so start with a whole number. In this example, I have a spreadsheet of fires over the past week, available from NASA.
2. 
3. Now make a pivot table that includes these rounded lat/long columns and whatever other data you might want to count up in your fake map (drag-select columns, then Insert > Pivot Table). Here,I've included "energy", which is the heat in Megawatts released by the fire. In your new pivot table dialog, drag your rounded latitude field as rows (up/downedness) and rounded longitude field as columns (left/rightiness), then you've got a pseudo-geographic grid. Drag the field you want counted up into the Values area (you can just drag the latitude or longitude field into this and pick "count" if you don't have a data field).
4. 
5. Your fake map will be upside-down at this point -because the pivot table will order the rows from small to large (naturally) by default. But latitude numbers are the reverse (-90 at the South Pole and +90 at the North Pole) for some reason. No sweat, just reverse your sort order ("z to a").
6. Select the whole sheet and adjust the width and height of the cells so they are all roughly even. Now take a moment and zoom out of your new pivot table and let the magic of having just made a bastard-map wash over your eyeballs... Yes, this is happening!
7. 
8. You are almost there, just monkey around with some conditional formatting of the cell colors to assign a color range to the cell values. Not only do you have an equirectangular map of spatially aggregated values, but it's also a choropleth! In Excel!
9. 

The result: A sweet global map of cumulative fire energy over one week. It also happens to look like it could rest comfortably in the Contra video game. (Do you remember the 30 free lives cheat sequence?)

A hacked map of global fires, created squarely in Excel.

Here's a detail view of Russia-India-China. I love that the cells still retain their quantitative value. Binning, labeling, and coloring like this would not be a trivial task in a standard GIS. But Excel is a non-standard GIS.

And there it is. Once you get the hang of it, you can crank these suckers out in about two minutes. I have grown to depend on it as the fastest first-glimpse method for just about any coordinate data that comes around. For example, in the fire map I just made for this example, Mexico and Burma looked like interesting hot spots. So I re-ran the process with lat long fields that had one decimal place of precision for a mega-huge 100x resolution map:

A tenth-degree precision Excel map of fires in Northern Latin America.

And here is what it looks like over in the Burma, Cambodia, Vietnam neck of the woods. Lots of fires.

If you like, you can also conditionally format the totals cells that pivot tables give you. Sort of an interesting option that you get for free. You could also conditionally format them as bar charts instead of just color, like I did here. The map above is conceptually similar to this.

Here is a massive pile of historic hurricane data mapped in Excel. Here is a non-Excel view of this data.

An also-massive pile of historic earthquake data mapped in Excel. The law of emergence whispers to us that this is really a map of tectonic boundaries. Here is a non-Excel view of this data.

Here's a longer term look at that fire data, for just the contiguous US. Here is a non-Excel (animated) view of this data.

Design Tips

You'll probably notice that the pivot table does not necessarily generate a complete lat long grid, but a grid that only includes cells were you have data (because Excel doesn't know or care that you are making a map!). If you want a complete grid (to overlay country boundaries, for instance), then just manually add those lat and long values in your source table. Generally, I find it convenient that the pivot table leaves out cells that have no data, but you aren't stuck either way.

Did you know you can re-size row heights or column widths wholesale by drag-selecting a bunch and then re-sizing one of them? All the cells will inherit that new size. Remember, make your cells roughly square if you want the real old school equirectangular-ish "plate carrée" map. Otherwise, just make the cells whatever aspect ratio looks best to you.

If you want a map with a dark background, just select all the cells of your map and fill them as black. The conditional formatting won't get wrecked. You'll probably also want to reduce the font size and center it.

You can select a range of cells (your map) and copy them to your clipboard, then just paste them into a graphic design program. Like Illustrator, if you are into that sort of thing. Remember, you are only doing this in Excel because it is convenient -don't feel like you are trapped there. Same goes for standard GIS programs, too. If you are fine-tuning your layout and fonts in a GIS then you are an arbitrary slave to your tool.

Adventure

Data is best when it is allowed to communicate its awesome encoded messages. This has been a fun and time-saving trick. If you have spreadsheets of latitude and longitude kicking around, I think you should give this a go. If you do, of course I'd love to see the results!

@John_M_Nelson

I originally posted this on the UX/data viz blog of IDV Solutions.