|
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, group data, 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.
Sheets, open your spreadsheet that contains the source data.
At the bottom right, click Explore .
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 manualIn Sheets, open your spreadsheet and select the columns with the data that you want to analyze.
Make sure your data is in columns with headers.
Click Insert Pivot table.
Under Insert to, choose where to add your pivot table.
Click Create.
(Optional) To use a pivot table suggestion instead, on the right, click Suggested and select a table.
If you want to add data manually by row or column or if you want to filter data, click Add and choose the data to use.
If you want to add data manually by value, next to Values, click Add and choose an option:
Select a column to use.
To use a custom formula, click Calculated Field, enter a formula, and under Summarize by, select Custom.
Work with a pivot tablCreate custom formulas—Create custom formulas if the one you want isn’t available. See step 7 above, in Create a pivot table manually.
Create custom groups—Select the items you want to see in their own groups. See Group data in a pivot table.
Show details behind data—Double-click a value to see its source data.
Edit a pivot tableNext to the pivot table, click Edit to open the pivot table editor.
Add data—Depending on where you want to add data, under Rows, Columns, or Values, click Add.
Change row or 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 under Order or Sort by and select the option or item.
Change the data range—Click Select data range and enter a new range.
Delete data—Click Remove .
Hide data with filters:
Under Filters, click Add and select a column to filter.
Under Show, click the Down arrow 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 under Show as and select a percentage option.
Group data in a pivot tableYou can group data to create and act on a subset of the pivot table data. Group data manually or automatically using a rule.
Note: You can’t apply a pivot group rule if you’ve already created a manual group on the same row or column item (and vice versa).
Manually group data:
In Sheets, open an existing pivot table or create a new one.
Select the row or column items you want to group together.
Right-click a selected item and then click Create pivot group.
(Optional) To ungroup, right-click a grouped item and click Ungroup pivot items.
Automatically group data using a rule:
In Sheets, open an existing pivot table or create a new one.
If your items are numbers, right-click the row or column items you want to group with a rule and click Create pivot group rule.
Choose an interval size.
(Optional) Choose when your group starts and ends.
Click OK.
(Optional) To ungroup, right-click a grouped item and click Ungroup pivot items.
|
