How to Create a Pivot Table in Google Sheets

Extract the data you want from all those results

Pivot tables are powerful spreadsheet features that can summarize only 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 extract only what you need. Here's how to create a pivot table in Google Sheets.

When Should You Use a Pivot Table?

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, as well.

Planning Your Pivot Table

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

  1. Examine your 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 all the necessary information for the pivot table.

  2. Format the data as a table by selecting the cells containing the data and clicking the Filter button on the toolbar. The cells in the first row will be 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 will help 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 will further streamline 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.

  1. 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.

  2. The column area contains headings within the pivot table. The column area can help you discover trends over time.

  3. Google Sheets will calculate and count data in the Values area. Typically, you will use this area for data you want to measure, such as sums, counts, or averages.

  4. 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 will be filtered based on this information.

Creating the Pivot Table

Google Sheets can automatically 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. Click Data > Pivot Table.

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

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

  6. Click 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. Click Add in the Filters area and choose the condition or value by which to filter the data, if you choose.

    Filter a pivot table in Google Sheets
  8. Order or sort columns or rows by clicking the arrow under Order or Sort By in the Pivot Table Editor pane and selecting 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 your pivot table at any time.

  1. Open the spreadsheet containing the pivot table.

  2. Click the pivot table.

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

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

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

If you modify or add to the source data your pivot table draws from, the pivot table will refresh automatically.

Was this page helpful?