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 Autocomplete.
- 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 .
Pivot table. Click the pivot table sheet, if it’s not already open.
.Change or remove data
- On your computer, open a spreadsheet in Google Sheets.
- Clickthe 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.
You can look at the source data rows for a cell in a pivot table.
- On your computer, open a spreadsheet in Google Sheets.
- Clickthe 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 click Calculated field.
- 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 aboutSpreadsheets, Databases, and Query Languages, click the button below:
Tutorial: Intro to Spreadsheets, Databases, and Query Languages
Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.
