Free tools Windows power users keep installed
One-click scans. No signup required.
To create a pivot table in Google Sheets on a computer, select a data range with a header in every column, choose Insert > Pivot table, then add fields under Rows, Columns, and Values in the editor. Use those fields to group records, summarize numbers, and focus the result with sorting or filters.
Prepare your data and create a pivot table
Each row should represent a record, each column a field, and every selected column must have a header. For example, a sales sheet might have the columns Date, Region, Product, and Sales. Select the data cells, then choose Insert > Pivot table and open the pivot-table sheet if Google Sheets does not open it automatically. Google’s pivot table instructions cover this workflow.
As an Amazon Associate I earn from qualifying purchases.
Sheets may offer suggested pivot tables. If you want to disable suggestions, choose Tools > Suggestion controls.
How do you add rows, columns, and values?
Start with the question you want the summary to answer. For “What are total sales by region and product?” use Region as the row grouping, Product as the column grouping, and Sales as the value to aggregate.
#1 Best Overall
- Used Book in Good Condition
- In the pivot-table editor, click Add beside Rows and select a category such as Region.
- Optionally click Add beside Columns and select a second category, such as Product, to create a cross-tab.
- Click Add beside Values and select the number to summarize, such as Sales.
Use each field’s menu to adjust how it is listed, sorted, summarized, or filtered. Categories belong in Rows or Columns; the measure you want to calculate belongs in Values.
Sort, show totals, group dates, and filter
The editor can order rows or columns by their labels or by aggregated values. Use Show totals when you want totals for a row or column grouping. For dates and times, Google Sheets supports grouping from a date-formatted cell; it also supports grouping numeric values into intervals. See Google’s pivot table customization guidance for the available controls.
To narrow the result, add a filter and either choose a condition, such as values greater than a threshold, or select values to hide. A value filter can retain its saved visible-value selection even after a filtered value disappears from the source. If a newly added category should appear but does not, open the filter and update its visible-value list.
Edit a pivot table and inspect its source rows
Use the pivot table’s Edit control to add, move, or remove fields, change the data range, or clear the fields. To see the records behind an aggregate, double-click that aggregate cell; Sheets opens a new sheet with the corresponding source rows.
Rank #3
When should you use a calculated field?
Use a calculated field when the built-in value summaries do not express the metric you need. Add it under Values and choose SUM or enter a custom formula. Google’s calculated-field instructions include =sum(Price)/counta(Product) as an example. If a field name contains spaces, put quotation marks around it in the formula, as Google specifies.
What changes with BigQuery-connected data?
A regular pivot table built from cells in a spreadsheet refreshes when its source cells change. A BigQuery-connected pivot uses Connected Sheets and a separate workflow: open the connected spreadsheet, choose the pivot-table action and destination, configure the settings, and apply. To get the latest BigQuery data, use the pivot table’s refresh action.
Google’s Connected Sheets documentation states a limit of up to 100,000 results for Connected Sheets pivot tables. This limit applies to that connected-data workflow; it is not a general limit for every ordinary Sheets pivot table.
Fix common pivot-table problems
- The pivot cannot be built or fields are missing: check that the selected range is correct and every column has a header.
- A new category is missing: check the filter’s saved visible-value selection and update it.
- Rows or columns are in the wrong order: change the ordering and choose whether to sort by label or aggregated value.
- A total is missing: turn on Show totals for the relevant grouping.
- A calculated field formula fails: check its formula syntax and quote field names containing spaces.
- BigQuery-connected results appear stale: use the Connected Sheets refresh action.
What pivot tables are useful for
Google describes pivot tables as a way to “Narrow down a large data set” and “See relationships between data points” in its Google Docs Editors Help. In practice, a pivot table lets you change the grouping and summary of a data set without manually calculating each group.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
