Google Sheets Pivot Tables: Create, Edit, and Analyze

Yes. In Google Sheets, select a range of data and choose Insert > Pivot table, then use the Pivot table editor to arrange fields and summarize the results. A pivot table reorganizes a dataset into a compact report without changing the original records.

What a Google Sheets pivot table does

A pivot table groups and summarizes rows of source data. Instead of scanning hundreds of transactions one by one, you can answer questions such as how much each product sold, which region had the most orders, or how sales changed by month. You choose fields for rows and columns, then select a calculation for the values.

For example, a sales list might contain one row per order, with columns for Date, Region, Product, and Amount. A pivot table can put regions down the left side, products across the top, and the sum of Amount in the cells. The result is a cross-tabulated report: the same underlying information, presented in a form that is easier to compare.

Pivot tables are useful for exploration because you can rearrange the report without writing formulas or moving the original data. They do not replace the source table, and they are not a new copy of every transaction. They present a summary of the source range.

Prepare the source data first

Good source data makes a reliable pivot table. Put one record on each row and one type of information in each column. The first row should contain a distinct heading for every column, such as Date, Region, Product, and Amount. Avoid blank header cells, merged cells, and extra title rows within the selected range.

Check that each column contains consistent values. For example, if amounts include a mixture of numbers and text such as “$120” typed as text, Sheets may not sum every entry as expected. Dates should be actual date values rather than labels that only look like dates. Keep subtotals and grand totals out of the source range, since the pivot table will calculate its own totals.

Before starting, consider these checks:

Remove completely blank rows or columns inside the dataset.
Use a single header row and give every field a clear name.
Make sure each row represents one transaction or other consistent record.
Check that numbers, dates, and categories use consistent formats.
Identify the full source range, including all headers and records you want summarized.

Create a pivot table in Google Sheets

In Google Sheets in a web browser, click a cell in the source data or select the range, then choose Insert > Pivot table. Sheets usually detects the surrounding data when you start from a cell inside it, but verify the range shown in the setup dialog. Choose whether to place the pivot table in a new sheet or an existing sheet, then select Create. A new sheet is often the clearest option because it keeps the report separate from the source.

The Pivot table editor appears at the right of the sheet. Under Rows, Columns, Values, and Filters, choose Add and select a field from the source headers. You can add more than one field to a section. The layout updates as you make choices, so you can adjust it until it answers the question you have in mind.

Add a category such as Region under Rows to list each region separately.
Add another category, such as Product, under Columns if you want a grid of regions and products.
Add a numeric field such as Amount under Values to calculate a summary.
Check the selected summary method and the resulting totals before relying on the report.

To change the report later, click a cell in the pivot table. The editor will reopen, allowing you to add, remove, or rearrange fields. You can also move a field between Rows and Columns by removing it from one section and adding it to another. The original data remains in its own range.

Choose fields and summary calculations

Rows and columns determine how the records are grouped. Rows are usually the main categories you want to compare; columns create a second dimension across the report. A table with Region in Rows and Product in Columns, for instance, shows each region-product combination. If the grid becomes too wide, put one category in Rows instead of Columns, or remove a field that does not help answer the question.

Values determine what gets calculated for each group. A numeric field is commonly summarized by SUM, while a field containing names or order IDs can be counted. Depending on the data and the selected field, the editor offers summary choices such as SUM, COUNTA, COUNT, AVERAGE, MAX, and MIN. COUNT counts numeric values; COUNTA counts non-empty values. That difference matters if a column contains text, blanks, or mixed data.

For example, to find total sales by region, add Region to Rows and Amount to Values, then ensure the value is summarized by SUM. To estimate average order size, use AVERAGE instead. To count orders, add an order identifier to Values and choose a count method appropriate to the contents. If a number field unexpectedly appears as a count rather than a sum, inspect the source for text values or inconsistent entries and check the summary setting.

The editor also provides options to sort or show totals for row and column groups. Some value display options can express a result as a percentage of a row, column, or overall total. These are useful for comparing shares, but the underlying amount and the percentage answer different questions. Label or format the report so a reader can tell which one is being shown.

A practical example: sales by region and product

Imagine a source table with four headers: Date, Region, Product, and Amount. Each row represents one completed order. To compare product revenue across regions, create a pivot table with Region in Rows, Product in Columns, and Amount in Values summarized by SUM. Each interior cell then represents the total amount for one region-product pairing, while row and column totals show broader sums where enabled.

If the business question changes to “How many orders did each region receive?”, keep Region in Rows, remove Product from Columns if it is not needed, and add an order identifier to Values with a count summary. If there is no unique order ID but every source row represents exactly one order, a consistently filled field such as Product can be counted, though that choice relies on the field being present for every order. This example shows why the definition of one source row matters: counting records is only meaningful when each row corresponds to the thing being counted.

For a trend report, use Date as a row field and Amount as a summed value. If dates are listed individually, the report may be too detailed. Sheets supports grouping pivot-table dates into periods in suitable date-based pivot tables; grouping by a broader period can make a trend easier to read. Confirm that the source dates are recognized as dates, since text-formatted dates may not group as expected.

Use filters and date grouping

Adding a field under Filters lets you narrow the pivot table to selected source values, such as one region or one product category, without deleting records from the source. The filter is part of the report’s view, not a way to remove or secure the underlying data. For a report another person must understand, make the current filter state clear and avoid assuming that a hidden category is inaccessible.

Filters can also help you investigate a large dataset in stages. For instance, show all regions first, then select one region to inspect its products. If you need to compare selected categories side by side, it may be better to leave them in Rows or Columns rather than filtering most of them away.

Date grouping helps replace a long list of individual days with useful periods such as months or years, where the available grouping choices apply. Review the resulting labels and totals carefully, especially around year boundaries. A monthly total combines records from that month; it does not mean that every day in the month had the same activity.

Updates, source ranges, and common problems

Pivot tables in Sheets respond to changes in the data they summarize, but the source range matters. Editing a value inside the selected source range is different from adding new records below or beside it. If new rows fall outside the range, they may not appear in the report. Click the pivot table and review its source data range in the editor; expand the range to include the added records when necessary. A range that includes room for future records can reduce maintenance, but it should still point to the intended columns and header row.

If a total looks wrong, diagnose the source and the pivot settings rather than assuming the calculation is broken:

Confirm that the source range includes all intended rows and no unwanted total rows.
Check whether the value field is set to SUM, COUNT, or another calculation you actually need.
Look for numbers stored as text, blanks, misspelled categories, or inconsistent date values.
Check filters for selected values that exclude records from the displayed result.
Verify that repeated records are genuine separate transactions, not accidental duplicates.

Different spellings, extra spaces, or inconsistent labels can split one apparent category into several groups. For example, “North” and “North ” may be treated as different text values. Clean the source data if those labels are meant to be identical. A pivot table summarizes what the source contains; it cannot infer that two differently entered labels mean the same thing.

When a report looks stale after a range or data change, click within it and check the source range and editor configuration. Also verify that the source cells contain the latest values. A pivot table is not a substitute for correcting the source or expanding a range that omitted new records.

Charts, sharing, and privacy

A pivot table can be a useful basis for a chart when a visual comparison is clearer than a grid of numbers. Select relevant cells in the summarized results and choose Insert > Chart. Review the chart’s data range and labels; grand totals or blank categories can sometimes produce a less useful chart if included unintentionally. A pivot table is generally best for detailed comparisons, while a chart can make a trend or ranking easier to spot.

Google Sheets collaboration lets people with suitable access work in the same file, but ordinary sharing permissions still govern who can view or edit it. A pivot filter is not a privacy control: hiding a row category in a report does not make confidential source data safe to share. If some viewers must not see certain records, share a separate file containing only the data they are authorized to view, and check its sharing settings.

Pivot tables compared with alternatives

A pivot table is a strong choice when you want to explore a dataset interactively and summarize it by several categories. It is usually quicker to rearrange than a collection of formulas, and the source records stay intact. For a fixed report with a small number of totals, SUMIF or COUNTIF formulas may be easier to place exactly where you want them. For more complex data shaping or custom query-style results, Sheets also offers the QUERY function, though it requires learning its query syntax.

Filters and sorting are more suitable when you need to inspect or rearrange individual records rather than aggregate them. Charts work well for communicating a pattern but do not replace a detailed summary table. Excel also has pivot tables, with broadly similar row, column, value, and filter concepts; its desktop version includes additional capabilities for some advanced data models and analysis workflows. Google Sheets is convenient when browser-based access and live collaboration are priorities, while the best tool depends on the complexity of the data and the reporting needs.

Frequently Asked Questions

Can Google Sheets make a pivot table?
Yes. Select the source data and choose Insert > Pivot table. Pick a new or existing sheet, then add fields in the Pivot table editor.

Why is my pivot table not showing new rows?
The new records may be outside the pivot table’s source range. Click the pivot table, inspect the range in the editor, and extend it to include the added rows.

Why does my pivot table count instead of sum?
Check the summary method under Values and inspect the source column. If amounts are stored as text or mixed with nonnumeric entries, Sheets may not treat the field as a sum of numbers.

Can I edit the original data from the pivot table?
A pivot table is a summary, not an editing view of each source record. Make corrections in the source data; the pivot report reflects the data included in its range.

Can I use a pivot table to hide confidential data?
No. Pivot filters change what the report displays, but they do not protect the underlying source data. Share only files or ranges that recipients are allowed to see.

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.