You can use pivot tables to:
- Narrow down a large data set.
- See relationships between data points .
For example, you could use a pivot table to analyze which salesperson produced the most revenue for a specific month.
Add or edit pivot tables
- On your computer, open a spreadsheet in Google Sheets.
- Select the cells with source data you want to use. Important: Each column needs a header.
- In the menu at the top, click Insert
Pivot table. Click the pivot table sheet, if it’s not already open.
- In the side panel, next to "Rows" or "Columns," click Add, then choose a value.
- Sometimes, you'll see recommended pivot tables based on the data you choose. To add a pivot table, under "Suggested," choose a pivot table.
- High confidence pivot table suggestions will be automatically inserted upon pivot table creation.
- To turn off pivot table suggestions:
- At the top, click Tools
- Turn off Enable Pivot table suggestions.
- At the top, click Tools
- In the side panel, next to "Values," click Add, then choose the value you want to see over your rows or columns.
- You can change how your data is listed, sorted, summarized, or filtered. Next to what you want to change, click the Down Arrow
.
Change or remove data
- On your computer, open a spreadsheet in Google Sheets.
- Click the pop-up Edit button underneath the pivot table.
- In the side panel, change or remove fields:
- To move a field , drag it to another category.
- To remove a field, click Remove
.
- To change the range of data used for your pivot table, click Select data range
.
- To clear all fields, click Clear all.
Note: The pivot table refreshes any time you change the source data cells it’s drawn from.
See cell details
You can look at the source data rows for a cell in a pivot table.
- On your computer, open a spreadsheet in Google Sheets.
- Click the pop-up Edit button underneath the pivot table.
- Double-click the cell you want to look at in more detail.
- You'll see a new sheet with the cell’s source data.
Calculated fields with SUM or a custom formula
- On your computer, open a spreadsheet in Google Sheets.
- Click the pop-up Edit button underneath the pivot table.
- In the side panel, next to "Values," click Add
- Calculate a value with SUM: Next to “Summarize by,” click SUM.
- Calculate a value with a custom formula: In the field that appears, enter a formula. Then, next to "Summarize by," click Custom.
- On the bottom right, click Add and the new column will appear.
Tip: To write custom formulas, you can use:
- Other columns, for example,
=sum(Price)/counta(Product)where "Price" and "Product" are fields in the pivot table or an underlying table (available with Connected Sheets.)- Google Sheets functions.
Important: If you use field values with spaces, make sure to use quotations around them in your custom formula. For example: ="h sdf".
Example
Related articles
To learn about Spreadsheets, Databases, and Query Languages, click the button below:
Tutorial: Intro to Spreadsheets, Databases, and Query Languages
