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 that 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 each time you need to extract data for reports.
Pivot tables allow users to customize the structure and design of tables, and view the specific data they need. Within one business or organization, different stakeholders will be 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. Here’s how to get started:
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 includes Airbnb 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 name/header (in the top row of the spreadsheet) 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.
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. I slice and dice the data: this is where Pivot tables are really useful.
- In the pivot table editor, under Rows, click on Add and choose “neighborhood” from our listings spreadsheet.
- Under Values, click on Add, then select price.
- Click on the Summarize by dropdown and select Average.
- Select Column B (price) and change the format to currency.
- Delete the decimal places.
I still want to extract more data. I want to know how many days of the year these rooms are available.
- Go to Values, click on Add and select availability_365.
- Summarize by AVERAGE.
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 now see that the average price of accommodation in Paris is €118 and available for 80 days per year. I can also identify clear deviations from the average: for example, in the Élysée neighborhood, I can see that the average room costs €247 and is available for more days per year.
Customizing your 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. My pivot table now looks like this:
How to edit the 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 on the X, then click on Add, and select 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.
Change the data range
In the pivot table editor on the right-hand side, click Select data range.
You’ve now got an overview of how to create a pivot table in Google Sheets.
Looking for other quick ways to filter data in your spreadsheets? Check out our post on sorting and filtering data in Google Sheets.
