Create & use pivot tables

You can use pivot tables to narrow down a large data set or see relationships between data points. For example, you could use a pivot table to analyze which salesperson brought the most revenue for a specific month.

Add or edit pivot tables

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the cells with source data you want to use. Important: Each column needs a header.
  3. In the menu at the top, click Data and then Pivot table. Click the pivot table sheet, if it’s not already open.
  4. In the side panel, next to "Rows" or "Columns," click Add, then choose a value.
    • Note: Sometimes, you'll see recommended pivot tables based on the data you choose. To add a pivot table, under "Suggested," choose a pivot table.
  5. In the side panel, next to "Values," click Add, then choose the value you want to see over your rows or columns.
  6. You can change how your data is listed, sorted, summarized, or filtered. Next to what you want to change, click the Down Arrow Down Arrow.

Change or remove data

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click the pivot table.
  3. In the side panel, change or remove fields:
    • To move a field , drag it to another category.
    • To remove a field, click Remove Remove.
    • To change the range of data used for your pivot table, click Select data range Select data range.

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.

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click the pivot table.
  3. Double-click the cell you want to look at in more detail.
  4. You'll see a new sheet with the cell’s source data.

Calculate a value with a custom formula

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click the pivot table.
  3. In the side panel, next to "Values," click Add and then click Calculated field.
  4. In the field that appears, enter a formula. Then, next to "Summarize by," click Custom.
    • You can use other columns to write a formula. For example, you can use =sum(Price)/counta(Product) where "Price" and "Product" are fields in the pivot table.
    • You can use Google Sheets functions in your formula.
  5. You'll see a new column called "Calculated field 1."

Example

Make a copy

 

Related articles

 

Was this helpful?
How can we improve it?