How to Create a Pivot Table in Google Sheets

Extract the data you want from all those results

What to Know

  • Format the data as a table by highlighting the cells containing the data and selecting the Filter button on the toolbar.
  • Select the table containing the source data you want to use, then select Data Pivot Table.
  • Select one of the suggested pivot tables at the top of the Pivot Table Editor to apply it to your data.

This article explains how to create a pivot table in Google Sheets.

Planning Your Pivot Table

Taking time to plan before creating a Google Sheets pivot table ensures the most effective outcome.

  1. Examine the source data to make sure it is well organized. It should not have any blank rows or columns. It should also have headings that provide the necessary information for the pivot table.

  2. Format the data as a table by selecting the cells containing the data and selecting the Filter button on the toolbar. The cells in the first row are formatted as column headings.

    Format data as a table in Google Sheets
  3. Clarify what you want from the pivot table. Setting a goal for what you want to accomplish with it helps you set it up properly.

  4. Consider how you would like the results displayed. Knowing which data you want to appear in specific columns and rows further streamlines the process of making a pivot table.

Pivot Table Areas

All pivot tables have four distinct areas. Each area serves a specific purpose. Learning about these areas will help you plan and create a pivot table in Google Sheets.

  • The Row area displays data down the rows on the left side of the pivot table. This area is used for data that you want to categorize and group, such as products, names, or regions. It is possible for this area to contain no fields.
  • The column area contains headings within the pivot table. The column area can help you discover trends over time.
  • Google Sheets calculates and counts data in the Values area. Typically, you use this area for data you want to measure, such as sums, counts, or averages.
  • You can opt to use the Filter area to create filters. When you select a data field in the Filters area, the entire pivot table is filtered based on this information.

Create the Pivot Table

Google Sheets can create a pivot table using your data. It may suggest one or more tables based on the data you provide. You can accept a suggestion to make an instant pivot table or create one manually.

  1. Open the spreadsheet in Google Sheets that has the data you want to use.

  2. Select the table containing the source data you want to use.

  3. Select Data > Pivot Table.

    The Pivot Table command in Google Sheets
  4. A new sheet opens, and the Pivot Table Editor opens on the right side of the screen.

    Pivot table sheet and editor in Google Sheets
  5. Select one of the suggested pivot tables at the top of the Pivot Table Editor to apply it to your data.

  6. Select the Add button next to each area and choose the data field you want in that area if you want to create the pivot table manually.

    The Add button in the Pivot Table window
  7. Select Add in the Filters area and choose the condition or value by which to filter the data.

    Filter a pivot table in Google Sheets
  8. Order or sort columns or rows by selecting the arrow under Order or Sort By in the Pivot Table Editor pane and choosing the option you want to apply.

    The "Order" and "Sort By" headings in Google Sheets
  9. Select the Show totals check box to display the totals of a column or row.

    The "Show Totals" option in Google Sheets

Edit or Remove Data

You can change or remove the data that appears in a pivot table at any time.

  1. Open the spreadsheet containing the pivot table.

  2. Select the pivot table.

  3. Drag a field to another category in the Pivot Table Editor to move it.

  4. Select Remove X in the Pivot Table Editor to remove a field.

  5. Choose Select Data Range, which looks like a small grid in the upper-right corner of the Pivot Table Editor, to change the data range used for the pivot table.

If you modify or add to the source data that the pivot table draws from, the pivot table refreshes automatically.

When Should You Use a Pivot Table?

Pivot tables are powerful spreadsheet features that can summarize the data that is significant to your needs from a sizable datasheet. You can use the interactive pivot tables to explore a large amount of data and only extract what you need.

If you have a massive amount of data and you only want to view a few data fields, a pivot table simplifies the process. You can summarize this data just as easily. You can create customized tables to find recurrent data patterns, which aids in accurate data forecasting. In addition, you can create custom reports efficiently.

Was this page helpful?