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.

How To Make a Table in Google Sheets, and Make It Look Great

A collection of formatting tips for how to make a table in Google Sheets.

Let’s start with a simple table, completely devoid of any formatting:

Table no format

How to Format a Table in Google Sheets

The goal with this article is to show you how to make a table in Google Sheets look great, like this:

How to make a table in Google Sheets and make it look great

Header rows

Go for bold, center-aligned and wrap the text, so it all shows.

Google Sheets formatting column headings

Aligning columns

Let’s align those columns, they’re messy!

Table alignment GIF

Center column headings, ID numbers, or other standardized entries.

Left align text.

Right align numbers (which they are by default). Really the only exception to this rule is for numbers that are not really numbers, i.e. they’re not being compared against each other or being used in any sort of calculations. For example, the ID numbers above can be center aligned.

Right align dates (which they are by default). However, if you’re working with just a year, as in the example above, you can get away with center aligning, just be consistent.

Formatting data

Choose appropriate formatting options for the data in your tables.

Add thousand separators to big numbers above a thousand. Add currency signs to financial numbers to add context.

Choose an appropriate number of decimal places. For example 2 decimal places if you need that level of detail to show cents on the dollar, but remove decimal places if they’re not needed for large numbers:

Google Sheets formatting format as currency

I could stop the article here, as the table is now sufficiently formatted to maximize legibility.

However, for tables that are part of dashboard reports or are being presented to clients, you can add colors to match your branding, or bring additional context to your numbers.

How to make a table in Google Sheets with Alternating colors

Sure you can do this manually, but it’s way easier and quicker to do with the Alternating Colors tool under the formatting menu.

Simply highlight your whole table and then open up the Alternating Colors option sidebar. Select the color scheme you want, whether you have a header or footer row, and even choose custom colors if you wish:

Google Sheets formatting alternating rows

How to make a table in Google Sheets with colors and indicator arrows

Hidden in the Custom Number Format menu is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0 or less than zero.

It’s a great tool to apply to tables in your Google Sheets dashboards for example, where the data is changing. By changing the color of a table cell’s text as the data changes, you can bring it to the attention of your user.

Consider the following sales table which has a % change column:

Google Sheets percent table

Now take a look at the same table with colors and arrows added to call out the % change column:

Google Sheets table

It’s significantly easier/quicker to read and absorb that information.

How to add this custom formatting

1. Somewhere in your Sheet, or a new blank Sheet, copy these three CHAR formulas (you can delete them later):

Google Sheets Char formula

=char(A1)

=char(A2)

=char(A3)

Now, copy and paste them as values in your Sheet so they look like column C and are not formulas any longer.

(You copy as values by copying, then right clicking into a cell and select Paste special > Paste values only…)

You’ll need to copy these to your clipboard so you can paste them into the custom number format tool.

2. Highlight the % column and go to the custom number formatting menu:

Google Sheets custom number format menu

3. Change the 0.00% in the Custom number formats input box to this:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬

as shown in this image:

Google Sheets custom number format

What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.

Copy in the symbols from step 1 (you’ll have to do this separately for each one).

Use the square brackets to specify the color you want e.g. [color50] for green.

Read more about custom number formatting here: Excel custom number formats

(It’s an Excel article, but the rules are the same.)

Updates to the Google Sheets Training Courses

Sep 18 announcement: Whew! This was a big task, but today I’m proud to announce that my video training courses are bang up-to-date! I sent out emails to students earlier today, announcing updates to 40 videos in my Google Sheets training courses.

Specifically:

Read on for more details.
Continue reading Updates to the Google Sheets Training Courses

How to apply conditional formatting across an entire row in Google Sheets

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.

For example, you could use it to apply background colors to cells based on the value in the cell.

You can go further than this though, and apply the formatting across an entire row, based on the value in a single cell.

For example, if the continent is “Africa” in column C, you can apply the background formatting to the entire row (as shown by 1 and 2):

Conditional formatting across an entire row

Five steps to apply conditional formatting across an entire row

It’s actually relatively straightforward once you know the technique using the $ sign (Step 5).

Step 1. Highlight the data range you want to format

The first step is to highlight the range of data that you want to apply your conditional formatting to. In this case, I’ve selected A2:C13:
Continue reading How to apply conditional formatting across an entire row in Google Sheets

My new course, Data Analysis with Google Sheets, is open for enrollment!

Data Analysis with Google Sheets course

Are you drowning in data, but not using it to make better decisions?

Do you run a small business and want to use your data to inform your strategy?

Has your business migrated from Excel to Google Sheets, and you’re looking to further your skills?

My new course, Data Analysis with Google Sheets, is now open for enrollment!

In this course, you’ll learn how to:

  • Understand and describe what your data tells you
  • Predict what things might look like in the future
  • Understand your customers to better serve them
  • Discover what factors affect your business
  • Best practices for doing all of this…
  • …and much, much more!

Continue reading My new course, Data Analysis with Google Sheets, is open for enrollment!