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
- In Sheets, open your spreadsheet that contains the source data.
- At the bottom right, click Explore assistant.
- 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.
- Hover over the pivot table you want and click Insert pivot table
.
The new pivot table is added in a new sheet.
Create a pivot table manually
- In Sheets, open your spreadsheet that contains the source data.
- Select the columns with the data you want to analyze.
- Click Data > Pivot table.
- At the bottom, click the pivot table tab if it’s not already open.
- 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.
- If you want to add data manually by row and column, under Rows and Columns, click Add and choose the data to use.
- 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.
- (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:
- Under Filters, click Add and select a column to filter.
- Under Show, click the Down arrow arrow_drop_down and deselect the items you want to hide.
- 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.