Google Sheets Pivot Tables: Create, Customize, Refresh

To create a pivot table in Google Sheets, select a range of data, choose Insert > Pivot table, pick a destination, and click Create. In the pivot table editor, add fields to Rows, Columns, Values, and Filters to summarize the records without changing the source data.

What a Google Sheets pivot table does

A pivot table reorganizes a detailed list into a compact summary. Instead of reading hundreds of sales records one by one, you can group them by region, product, or date and calculate totals or counts for each group. The word pivot refers to the ability to rearrange the summary: a field can be used as a row label in one view and a column label in another.

A pivot table does not replace or rewrite the original rows. It creates a separate report based on a source range, so the original data remains available for checking or reuse. This makes pivot tables useful for exploring questions such as which product sold most, how many orders came from each region, or how monthly totals compare.

For example, a sales list with columns for Date, Region, Product, and Revenue can become a report showing revenue by region and product. Adding Date as a row field can reveal a month-by-month pattern; adding Order ID as a value summarized by count can show the number of orders rather than their dollar value.

Prepare the source data first

Good source data makes a pivot table easier to build and less likely to produce misleading results. Use one header row with a distinct name for each column, then put one record on each row. Avoid merged cells, blank header cells, subtotal rows mixed into the records, and decorative headings inside the data range.

Keep each column consistent. A Revenue column should contain numbers rather than a mixture of numbers and phrases such as pending. Dates should be actual date values, not text that merely looks like a date. If a field has inconsistent spelling, such as North, north, and N. region, Sheets may treat those as separate categories.

Before inserting the pivot table, check that the range includes all relevant records and headers. If the source is on another sheet, you can select it there first or enter its range in the pivot table setup. A source range that ends at row 200 will not automatically include new records later added in row 201 or below; expand the range when needed.

Use a single, clearly labeled header row.
Remove blank rows and existing totals from the middle of the records.
Check that numbers and dates are stored as values of the right type.
Make category labels consistent before summarizing them.

Create a pivot table step by step

Select a cell in the data, or select the complete data range including its headers.
Choose Insert > Pivot table.
Confirm the source data range in the setup dialog.
Choose New sheet to place the report on its own sheet, or Existing sheet and specify a destination cell.
Click Create. The pivot table editor appears, usually at the right of the sheet.
In the editor, click Add beside Rows, Columns, Values, or Filters and choose the fields to use.

For a basic revenue summary, add Region to Rows and Revenue to Values. Sheets will summarize the numeric values, commonly by SUM. The result shows one total per region. To compare products within each region, add Product to Columns. The resulting grid has regions down the side, products across the top, and revenue totals in the cells.

To count orders, add a field such as Order ID to Values and change its summary method to COUNT or COUNTA, depending on whether the IDs are numeric or text. The field’s dropdown in the Values section lets you change how it is summarized. The editor also lets you reorder fields by dragging them, which changes the layout without changing the source list.

Choose rows, columns, values, and filters

Each pivot-table area has a distinct role. Rows create groups listed vertically; Columns create groups across the top; Values perform calculations; and Filters restrict which source records contribute to the report. A field can be useful in more than one area, but adding too many categories can make the result wide or difficult to read.

Rows: Use a category such as Region, Customer, or Month when you want one line per group.
Columns: Use a second category, such as Product Type, to compare groups side by side.
Values: Use numeric fields for sums or averages, or identifiers and labels for counts.
Filters: Limit the included records, for example to one sales channel or a selected year.

When you add a field to Values, check the summary method rather than assuming the default is right. SUM answers a total question; AVERAGE answers a typical-value question; COUNT or COUNTA answers a volume question. Summing customer IDs, for example, produces a number but not a meaningful business measure. If you need a percentage view, the Values options include display choices such as a percentage of a row, column, or grand total where applicable.

Sorting can make a report more useful. In the Rows or Columns settings, choose an order and, where available, the field used to sort. Sorting categories by a value can bring the largest totals to the top. For date fields, use actual dates so Sheets can order them chronologically rather than alphabetically.

Practical examples and date summaries

Suppose a tutoring service tracks Session Date, Tutor, Subject, and Fee. To compare earnings by tutor, add Tutor to Rows and Fee to Values, summarized by SUM. To see how many sessions each tutor delivered, add a session identifier to Values and use a count. To compare subjects within each tutor, add Subject to Columns.

For a monthly report, add a valid date field to Rows. Depending on the pivot table controls available for that field, you can group dates into periods such as months, quarters, or years. Check that the source dates are genuine date values and that the selected period matches the question. A month-only view can combine January from different years, so include Year as well when comparing several years of data.

A pivot table can also help reveal missing or unexpected categories. If an apparent total is lower than expected, filter by a category and inspect the underlying records. If a label appears twice with slightly different spelling, the pivot table is reflecting the source values rather than deciding that they mean the same thing. Clean the source labels if those categories should be combined.

Editing, updating, and sharing the report

Click inside the pivot table to bring up its editor again. You can add or remove fields, change a summary method, adjust filters, and alter the row or column arrangement. These edits change the report, not the underlying records. To make a separate view, duplicate the report sheet or create another pivot table from the same source.

Changes to cells within the pivot table’s source range are generally reflected in the summary. However, new records added beyond the selected range may be excluded because they are outside its boundaries. If that happens, click the pivot table, inspect the source data range in the editor, and expand it to include the new rows. This range issue is a common reason a report appears not to update.

For sharing, the pivot table is part of the Google Sheets file and follows its sharing permissions. A reader who can view the file can see the report, while editing access allows changes to the sheet and its pivot table. If collaborators need a fixed snapshot, copy the displayed results and paste values elsewhere, understanding that the copied figures will not update with the source.

Common problems and how to fix them

There is no field in the editor: Check that the source range includes the header row and that the header cell is not blank.
A total is unexpectedly small: Confirm the source range includes every record and that filters are not excluding data.
Numbers are counted instead of added: Check that the source column contains numeric values consistently, then select the appropriate summary method.
Dates appear as separate odd categories: Convert text dates to actual date values and check for mixed formats or invalid entries.
New rows are missing: Expand the pivot table’s source range to cover those rows.
Duplicate-looking labels appear: Check for extra spaces, punctuation differences, or inconsistent spelling in the source.

One useful troubleshooting habit is to compare a pivot result with a small, manually checked subset of the source rows. This can distinguish a setup problem from a data-quality problem. Also check whether a filter is active: a filter can produce a correct total for only part of the data while making the report look incomplete.

Pivot tables compared with formulas and other tools

Pivot tables are convenient when you want to rearrange a summary interactively without writing a separate formula for each category. They are especially effective for exploration and recurring reports where the grouping may change as questions develop.

Formulas such as SUMIFS are often better when you need a fixed, custom-designed report or want results placed in a particular layout. For example, with Region in B2:B100, Revenue in D2:D100, and a region label in G2, enter =SUMIFS($D$2:$D$100,$B$2:$B$100,G2) in H2. If G2 contains West and the West records total 1,250, H2 returns 1,250. The formula uses a fixed source range, so extend that range if additional records are added outside row 100.

Google Sheets also offers functions such as QUERY for more formula-driven summaries, but those require writing and maintaining a query expression. A pivot table is usually easier for a one-off analysis or for a collaborator who prefers menus. A chart can communicate a trend more clearly, but it does not replace the pivot table’s ability to summarize and regroup records. You can create a chart from summarized data when a visual comparison would help.

Excel also has pivot tables, but its interface and some options differ. This guide uses Google Sheets in a web browser: use Insert > Pivot table and the Sheets pivot table editor. Do not assume that an Excel ribbon path, refresh control, or feature name applies in Sheets.

Frequently Asked Questions

Are Google Sheets pivot tables free?

There is no separate charge for the pivot table feature itself in Google Sheets. Access to Sheets and its collaboration features depends on the Google account or organization using it, so check the applicable account or Workspace terms if cost or administrative access matters.

Do Google Sheets pivot tables update automatically?

Edits to data within the pivot table’s source range are generally reflected in the pivot table. New rows outside that range are not included automatically, so expand the source range when you add records beyond its current boundary.

Can a pivot table show a count instead of a sum?

Yes. In the pivot table editor, open the field’s options under Values and choose a count summary, such as COUNT or COUNTA, as appropriate for the data. Use a field that is populated for every record you intend to count.

Why does my pivot table show the wrong total?

Check the source range, filters, data types, and summary method. Text stored in a numeric column, a filter that excludes records, or rows outside the selected range can all change the displayed total.

Can I use a pivot table to summarize dates by month?

Yes, when the source column contains valid date values and the pivot table offers date-grouping options for that field. Include the year as well if the data covers multiple years, so that matching months from different years are not mistaken for a single period.

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.