Pivot Table Maps in Google Sheets…yes, really!

Let me show you a unique use case for pivot tables – Pivot Table 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 Pivot Table 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 Maps of Washington DC

The dark areas in the pivot table 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 Pivot Table Maps

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…).

When could we use Pivot Table Maps?

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 Pivot Table Maps 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.

6 thoughts on “Pivot Table Maps 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.

  2. Hi Ben, this is awesome! So in that case would it be possible to have a double layer on a google sheet map?

    Like having the red to green dots, and on top of some of them have an extra information like an action is happening on that location?


  3. This is amazing!

    Do you know if there is an easy way to view the locations over an actual map in google maps?

    1. Thanks! I don’t think so unfortunately… Perhaps you can overlay the pivot table over an image of Google maps pasted into your Sheet??

Leave a Reply

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