Description



Pivot tables help you summarize data, find patterns, and reorganize information. You can add pivot tables based on suggestions in Google Sheets or create them manually. After you create a pivot table, you can add and move data, add a filter, drill down to see details about a calculation, and more.

Examples

  • Summarize thousands of rows of data, such as sales each year segmented by region.
  • Drill down to analyze a region. Use a pivot table to see the total of each region’s sales. Then, get a breakdown of individuals and their sales for that region.
  • Try it now: Pivot tables template

Note: Pivot tables refresh any time you change the source data.


Instructions


Add a pivot table from a suggestion

  1. In Sheets, open your spreadsheet that contains the source data. 
  2. At the bottom right, click Explore assistant.
  3. Scroll down to the Pivot Table section to see suggested pivot tables. Click More to see additional suggestions.

    Note: If a pivot table isn’t relevant for your data, none appear. The data you want to use should be in columns, and each column should have a header.

  4. Hover over the pivot table you want and click Insert pivot table pivot table.
    The new pivot table is added in a new sheet.


Create a pivot table manually

  1. In Sheets, open your spreadsheet that contains the source data.
  2. Select the columns with the data you want to analyze.
  3. Click Data > Pivot table.
  4. At the bottom, click the pivot table tab if it’s not already open.
  5. If you want to add data automatically, on the right under Suggestions, click the pivot table you want to add.

    Note: If a pivot table isn’t relevant for your data, none appear. The data you want to use should be in columns, and each column should have a header.

  6. If you want to add data manually by row and column, under Rows and Columns, click Add and choose the data to use.
  7. If you want to add data manually by value, under Values, click Add and choose the data to use. This data appears as values under your rows and columns.
  8. (Optional) To use a custom formula in the Values category:
    • Click Add > Calculated Field and enter the formula. 
    • Next to Summarize, click the Down arrow arrow_drop_down and select Custom.


Work with a pivot table

  • Create custom formulas—Create custom formulas if the one you want isn’t available. See step 8 above, in Create a pivot table manually.
  • Create custom groups—Select the items you want to see in their own groups. See Create customized groups of data in a pivot table.
  • Show details behind data—Double-click a value to see its source data.

Edit a pivot table

Click anywhere in a pivot table to open the editor. 

  • Add data—Depending on where you want to add data, under Rows, Columns, or Values, click Add.
  • Change column names—Double-click a Row or Column name and enter a new name.
  • Change sort order or column—Under Rows or Columns, click the Down arrow arrow_drop_down under Order or Sort by and select the option or item.
  • Change the data range—Click Select data range grid_on and enter a new range.
  • Delete data—Click Remove clear.
  • Hide data with filters:
    1. Under Filters, click Add and select a column to filter.
    2. Under Show, click the Down arrow arrow_drop_down and deselect the items you want to hide.
    3. Click OK.
  • Move data—Drag data from one category to another. For example, change row data to column data by dragging data in Rows under Columns.
  • Repeat row labels—Add at least 2 row items. Under the first row item, click Repeat row labels.
  • Show totals—Under Rows or Columns, check the Show Totals box.
  • Show values as percentages—Under Values, click the Down arrow arrow_drop_down under Show as and select a percentage option.