Google Sheets Pivot Tables: Create, Customize, and Refresh

Yes, you can create a pivot table in Google Sheets using Insert > Pivot table. Select a range of well-organized data, choose whether to place the pivot table on a new or existing sheet, then use the pivot table editor to summarize, sort, and filter the information without changing your source data.

What a pivot table does

A pivot table rearranges and summarizes a larger list of records into a compact report. Instead of manually adding up every sale by region or counting every request by status, you choose the fields to organize and the values to calculate. Google Sheets builds the summary and recalculates it when the data in its source range changes.

For example, imagine an order list with columns for Region, Product, Sales representative, and Sales. A pivot table can show total sales by region, break each regional total down by product, or count orders by representative. The original rows stay in place; the pivot table is a separate view of them.

Pivot tables are useful when you need to explore the same dataset in different ways. A sales manager might compare revenue by product and region, while a project coordinator might count tasks by owner and status. They are especially helpful when the source contains many rows and a summary would be tedious to maintain by hand.

Prepare the source data first

Good source data makes a pivot table easier to create and less likely to produce misleading totals. Treat the source as a simple table: one header row followed by one record per row. For instance, each order should have its own row, rather than a row containing several products or a manually calculated subtotal mixed into the list.

Give every column a clear, unique header, such as Date, Region, Product, and Sales.
Keep each kind of information in its own column. Avoid combining a person’s name and department in one cell if you may want to summarize them separately.
Use consistent values. If a region appears as both West and W. Region, Sheets treats those as different categories.
Enter numbers as numbers and dates as dates, not as text that only looks numeric or date-like.
Avoid merged cells, blank header cells, and title rows above the actual headers in the selected range.

These are data-cleaning principles, not special pivot-table rules, but they affect the result. For instance, a text-formatted sales amount may not be included correctly in a sum. If the report has unexpected categories or totals, inspect the source values before assuming the pivot table is at fault.

Create a pivot table in Google Sheets

In Google Sheets in a web browser, follow these steps:

Open the spreadsheet and select a cell in the data, or select the full data range including its header row.
Choose Insert > Pivot table.
Check the data range shown in the dialog. Choose New sheet to put the pivot table on a separate tab, or Existing sheet to place it at a location in the current spreadsheet.
Select Create. Sheets opens the pivot table and displays the pivot table editor, usually at the right of the screen.
In the editor, choose Add next to Rows, Columns, Values, or Filters, then select a source column to use in that part of the report.

Putting the pivot table on a new sheet is often the clearest option: the summary will not cover the source list, and both remain in the same spreadsheet. If you choose an existing sheet, select a location with enough empty cells for the report to expand. The menu path and steps above are for Google Sheets; Excel has its own PivotTable commands and editor.

Choose rows, columns, and values

The four areas in the editor determine how the report is arranged. Rows create categories down the left side, while Columns create categories across the top. Values contain the figures or counts to summarize. Filters let you limit which source records are included in the displayed result.

Suppose an order sheet has the headers Region, Product, Representative, and Sales. To compare sales by region, add Region under Rows and Sales under Values. To split each regional total by product, add Product under Columns. The resulting table can show a total for each region-product combination, along with row or column totals when those options are available in the editor.

For a small illustrative dataset, imagine East has sales of 120, 80, and 60, while West has sales of 150 and 50. With Region as the row field and Sales summarized by SUM, the pivot table should report East as 260, West as 200, and a grand total of 460. If the figure is different, check whether the selected source range includes all five records and whether the Sales entries are numeric.

For Values, Sheets selects a summary method based on the field, but you can change it in the editor. SUM adds numeric values; AVERAGE finds their mean; COUNT counts numeric entries; and COUNTA counts non-empty entries. The right choice depends on the question. For example, use COUNTA on an order ID field to count orders, rather than summing the IDs.

Sort, filter, and refine the summary

A pivot table is more useful when it answers a specific question rather than displaying every available category. In the editor, a field added under Filters can restrict the report to selected values or conditions. For example, filter Region to East to review only eastern orders, or filter a date field to focus on a relevant period if the available filter options suit the data.

Row and column fields can also be sorted, and value fields can be used to order categories by their summarized results. Sorting regions by total sales, for example, makes it easier to see which region contributed the most. You can add or remove fields in the editor as your question changes; you do not need to rebuild the source list for each new view.

When a field is used more than once, check what each instance is doing. A field can be useful as a row category and also as a value to count records, but the two placements serve different purposes. If the report becomes crowded, remove fields that do not help answer the question, or move a category from Columns to Rows to make the layout easier to read.

Calculated fields and totals

Google Sheets pivot tables can include calculated fields, which derive a value from fields in the source data. In the pivot table editor, use Values > Add > Calculated field when that option is appropriate for your report, then enter a calculation based on source fields. A calculated field can be useful for a metric that is not already stored as a column, but it is important to confirm that the calculation matches the business meaning you intend.

Do not confuse a calculated field with a normal spreadsheet formula placed beside the pivot table. A separate formula may not expand or stay aligned when the pivot table changes size. If you use formulas adjacent to the report, leave room for the pivot table to grow and verify the references after changing its layout.

Totals are also worth checking. Grand totals can help confirm that groups add up to the overall figure, but they are not meaningful for every measure. Adding category averages together, for instance, does not produce an overall average. If you need an overall average, use the source records and the appropriate summary method rather than treating each group’s average as a component to sum.

Updates, range limits, and common problems

Edits to cells inside a pivot table’s source range are reflected in its summary. However, new rows pasted below the range may not be included if the original source range ended above them. If recently added records are missing, inspect the source range shown in the pivot table editor and extend it to cover the new rows. Keeping the source list contiguous and checking the range after adding data can prevent incomplete reports.

Other frequent problems have simple causes:

The pivot table shows an unexpected count: Confirm whether the value field is using COUNT or COUNTA. COUNT counts numeric cells; COUNTA includes non-empty text cells too.
A sum is missing or too small: Check for amounts stored as text, blank cells, or records outside the selected range.
Similar categories appear separately: Look for spelling differences, extra spaces, or inconsistent labels in the source column.
A field is absent from the editor: Check that its column is inside the selected data range and has a header.
The report overlaps other content: Move it to a new sheet or choose an empty area with enough room for its rows and columns.

For trustworthy results, compare the pivot table’s grand total with a known total or a quick check of the source. A pivot table summarizes the data it receives; it cannot determine whether the underlying entries are accurate, complete, or categorized correctly.

Pivot tables compared with formulas and charts

A pivot table is not the only way to summarize spreadsheet data. Formulas are better when you need a fixed calculation in a specific cell, a custom rule, or a result that must feed another calculation. Pivot tables are often more convenient for exploring categories because you can change the arrangement through the editor instead of rewriting a series of formulas.

A chart, meanwhile, presents values visually. It does not replace the summary logic: a chart based on detailed rows may be difficult to interpret, while a chart based on a pivot table can display already-grouped totals. A practical workflow is to build and check the pivot table first, then create a chart when a visual comparison will help readers understand the result.

Pivot tables are not ideal for every dataset. If the source has inconsistent labels, complicated record structures, or a calculation that depends on custom row-by-row logic, clean or reshape the data first. For a short list with only a few totals, a direct formula or manual summary may be quicker. Choose the simplest method that remains easy to audit and update.

Frequently Asked Questions

Are pivot tables available in Google Sheets?
Yes. In Google Sheets in a web browser, select your source data and choose Insert > Pivot table. You can place the result on a new sheet or an existing one.

Do Google Sheets pivot tables update automatically?
Changes to cells within the source range are reflected in the pivot table. New rows outside that range may be excluded, so expand the source range when you add records beyond its current boundary.

Can I make a pivot table from multiple columns?
Yes. Add different source fields as rows, columns, values, and filters. For example, use Region for rows, Product for columns, and Sales for values to compare totals across both categories.

Why is my pivot table showing COUNT instead of SUM?
Check the value field’s summary setting in the pivot table editor and confirm that its source cells contain numeric values. Text-formatted numbers may not be treated as numbers for a sum.

Can I create a pivot table in Google Sheets on a phone?
The instructions here are for the web browser. Google Sheets mobile apps have different interfaces and may not expose the same pivot-table creation and editing controls, so use Sheets in a desktop browser for the clearest access to the editor.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.