A Beginner’s Guide to Pivot Tables in Google Sheets

If you use Google Sheets, or any spreadsheet application for that matter, but don’t use Pivot Tables, then you’re missing out on one of the most powerful and useful features available.

This tutorial will (attempt to) demystify Pivot Tables and give you the confidence to start using them in your own work.

Contents

1. An Introduction to Pivot Tables
   • What are Pivot Tables?
   • Why use Pivot Tables?
   • How to create your first Pivot Table
   • Let Google build them for you

2. Pivot Tables: Fundamentals
   • Rows, columns and values
   • Totals
   • Sorting

3. Pivot Tables: Tips and Tricks
   • Multiple value fields
   • Changing aggregation types
   • Adding filters
   • Multiple row fields
   • Copying Pivot Tables

4. Pivot Tables: Next steps

1. An Introduction to Pivot Tables

What are Pivot Tables?

Let’s see a super simple example, to demonstrate how Pivot Tables work. Consider this dataset:

You want to summarize the data and answer questions like: how many apartments are there in the dataset? What’s the total cost of all the apartments?

Now, this would be easy to do with formulas, using a COUNTIF and a SUMIF, but if you change our mind and now want to summarize “Condo” you have to modify all your formulas, which is a pain.

Enter the Pivot Table:

This took me eight mouse clicks and I didn’t have to write a single formula (in a few paragraphs I’ll show you those exact 8 clicks so you can build your own version).
Continue reading Pivot Tables 101: A Beginner’s Guide

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

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?
Continue reading Mapping data with Pivot Tables in Google Sheets…yes, really! 🌎

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):

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