How to create a Pivot table in Google Sheets

What are pivot tables?

Pivot tables are a powerful tool to help you extract and analyze the information you need from a large data set. They’re an easy way to “slice and dice” data in spreadsheets: in other words, to filter and then group data. For example, you might want to look at sales for one particular year (slice) and then break it down by product category (dice).

The big advantage of pivot tables is that they are dynamic, quicker and more reliable than manually typing formulas.

Pivot tables allow different users to customize the structure and design of tables, and view the specific data they need. Within the same organization, each stakeholder are interested in different aspects of operations. Even within departments, co-workers use slightly different KPIs or filters for the same goal.

If you’ve used Excel, you’re probably already familiar with Pivot tables and Google Sheets has the same tool.

Spreadsheets are just the start

How to make a pivot table in Google Sheets

Step 1: Select the data source

  • Open a new spreadsheet in Google Sheets and click File > Open
  • Select a file from Google Drive or upload a new file in any spreadsheet format.

In this example, I’m using publically available data from Airbnb listings in Paris. This dataset has listed rooms and apartments in Paris, including indicators such as neighborhood, room type and price.

Step 2: Select the data range of the pivot table

  • Select all of the cells containing data.
  • In this case, I will select all columns to be included in the pivot table (a shortcut is to use Ctrl+A). Now I will customize and edit the variables/KPIs I want to include.

Note: your columns must have a named header in the top row to use the data in the pivot table.

Step 3: Insert the pivot table

  • Go to Data in the menu bar and select Pivot table.
  • For a better overview, we will add the table to a new sheet. Click Create.
  • A new spreadsheet called “Pivot Table” opens. On the right-hand side you see the pivot table editor.
how to create a pivot table in google sheets - 2

Step 4: Pivot table editor

  • Google is smart so it automatically provides suggestions for how to analyze your data set.
  • In this example, the second suggestion is what we want to see: Airbnb listings in Paris by room type.

Step 5: Customize the pivot table

I want my pivot table to show the average prices for each neighborhood in Paris. This is where Pivot tables are really useful as they allow you to analyze information quickly without having to.

 

  • In the pivot table editor, under Rows, click Add and choose “neighborhood” from the listings spreadsheet.
  • Under Values, click Add, then select price.
  • Click Summarize by dropdown and select Average.
  • Select Column B (price) and change the format to currency.
how to create a pivot table in google sheets - 1

Lets extract more data. Suppose I want to know how many days of the year these rooms are available.

  • Go to Values, click Add and select availability_365.
  • Summarize by AVERAGE.
how to create a pivot table in google sheets - 3

That’s it: these are the basic steps for creating a pivot table in Google Sheets.

In this example, I’ve created a pivot table that shows the average price for a room in each neighborhood of Paris and the number of days per year these rooms are available.

From a huge data set containing more than 60,000 Airbnb listings, I can easily reach various insights:

  • Average price of accommodation in Paris is €118.
  • Average availability per accommodation is 80 days per year.
  • There is a price deviation in the Élysée neighborhood where a room costs €247 on average.

Transform your data into actionable insights

How to edit a pivot table in Google Sheets

Add a filter to the pivot table

You have already filtered your data by creating a pivot table, but you might want to break that information down into further categories. You can do that by adding an extra filter in the pivot table editor.

Go to Filter. Again, Google Sheets provides automatic suggestions based on your data.

If you want to add a different filter, click Add. Next to Showing all items, click the ⏷dropdown menu.

You can:

  • Filter by condition (e.g. text contains). You can then enter a value, a cell reference or a field.
  • Filter by value.

Note: if you apply a filter and then update your source data, you need to update your pivot table filter.

Looking at the Airbnb data, let’s say I want to analyze my data by room type. First I add a column for room type. But I only want to see results for: hotel room, private room and shared room. Under Filter I select those rooms.

Pivot table Airbnb room types

How to customize rows and columns

If Google’s automatic suggestions don’t match what data you need, you can adjust the Rows, Columns, Values, and Filter manually.

Deselect Google’s default suggestions by clicking X, then Add, and selecting which row or column you want to pull data from.

You can change the header names by clicking directly on a row or column.

Add a custom formula

In the pivot table editor on the right-hand side, go to Values > Add > Calculated field.

Enter your formula, then go to Summarize by, and click Custom. A new column appears called “Calculated field 1.”

For help with writing formulas in Google Sheets, see Google’s full function list here.

That’s how to do a pivot table in Google Sheets

You’ve now got an overview of how to create a pivot table in Google Sheets. Pivot tables have many uses and they can get quite complicated, hopefully this post helped you get started.

Looking for other quick ways to filter data in your spreadsheets? Check out our post on sorting and filtering data in Google Sheets.

Ready to streamline your spreadsheet data?

You may also like…