Mapping data with Pivot Tables in Google Sheets…yes, really! 🌎

Let me show you a unique use case for pivot tables – building maps!

Pivot Table Map of Washington DC

Can you guess which city this is?

It’s Washington D.C. and it’s also a pivot table in Google Sheets. The image on the left is the map built with a pivot table. The image on the right is a screenshot of Washington D.C. from Google maps.

Look closely and you might just be able to see the Google Sheet row and column headings around the map.

Wait, what?

I downloaded a dataset of DC crime data, which had latitude and longitude data, and plotted it as a pivot table with conditional formatting.

Pivot Table Map of Washington DC

The dark areas in the pivot map represent Rock Creek Park (huge wooded area with few people, hence few crimes) and two rivers, the Anacostia, which runs through the SE of the city, and the Potomac river, which delineates the W edge of the city.

Annotated Pivot Table Map of Washington DC

And here’s a close up of the Sheet, to convince you it’s really a pivot table (click to enlarge) 😉

Pivot Table map in Google Sheets

I first saw this technique implemented in Excel (see this example) but I haven’t seen anything in Google Sheets before.

How to make a Pivot Table Map

It’s actually pretty simple and can be performed on any dataset with latitude and longitude data, to varying degrees of success (some datasets are too large, some not granular enough).

  1. Download your chosen dataset.
  2. Round your latitude and longitude data columns to 1, 2 or 3 decimal places (depending on how granular your data is, trial and error).
  3. Highlight the whole data table and create a pivot table.
  4. Insert latitude as rows.
  5. Sort the latitude rows as descending, since larger positive numbers are further North.
  6. Insert longitude as columns.
  7. Highlight all the longitude columns and reduce the column width of all of them in one go, so all the cells are square.
  8. Insert your measure into the values section of your pivot report builder, in the DC map example above I inserted “Offense” and used COUNTA to count them.
  9. Highlight whole pivot table and choose Conditional Formatting –> Color Scale (I chose variations of yellow).
  10. Change the background of the pivot table to black.
  11. Zoom out on your Sheet as far as you need to see the full “map”.

Want to see this template?

Click here >>

Feel free to make your own copy (File > Make a copy…).

Want to learn more about Pivot Tables?

Data Analysis courseMy Pivot Tables in Google Sheets course will teach you how to use Pivot Tables from the ground-up to an advanced level.

Find out more here >>

When could we use this pivot table mapping technique?

It’s a quick and dirty way of visualizing geographic data sets.

It’s not really suitable for deep analysis or final charts, where a tool like Tableau would really prove it’s worth, but for a quick look with only a few minutes work, it’s not too bad!

Here’s a few more experiments of my own:

A pivot table map of 7,000 breweries and brew pubs in the USA:

A pivot table map of breweries and brew pubs in the USA

Houston, Texas as a pivot table map, created from 311 data:

Houston pivot table map

Subway stations of New York City pivot table map:

NYC Subway pivot table map

Let me know if you try this yourself! I’d love to see any creations you come up with.

Want to learn more about Pivot Tables?

Data Analysis courseMy Pivot Tables in Google Sheets course will teach you how to use Pivot Tables from the ground-up to an advanced level.

Find out more here >>

3 thoughts on “Mapping data with Pivot Tables in Google Sheets…yes, really! 🌎”

  1. Very cool. Would one issue though be potential blank rows/columns? I guess to ensure those get represented you’d need to create some dummy entries to cover all the latitudes & longitudes that you want on the map.

Leave a Reply

Your email address will not be published. Required fields are marked *