Google Sheets Pivot Tables: How to Make and Use One

To make a pivot table in Google Sheets, select a data range with a header row, choose Insert > Pivot table, select where to place it, and click Create. In the Pivot table editor, add fields under Rows, Columns, Values, or Filters to summarize and explore your data without changing the original records.

What a pivot table does

A pivot table reorganizes a list of records into a compact summary. Instead of manually adding up every sale or counting each order, you choose fields from your source data and let Sheets group and calculate them. You can change the arrangement by moving fields between the editor’s sections; the source table remains intact.

For example, a sales list might have one row per order, with columns for date, region, salesperson, product, quantity, and revenue. A pivot table can show total revenue by region, then split each region by product. A different arrangement can count orders by salesperson or show average revenue by month. The underlying records do not have to be sorted into groups first.

Pivot tables are useful for questions such as “Which region had the highest sales?” or “How many orders did each product receive?” They are summaries, not replacements for the detailed data: the individual transactions remain in the source range.

Prepare the source data

Good source data makes a pivot table easier to build and less likely to produce confusing results. Organize the records as a rectangular table: one record per row, one type of information per column, and a single header row. Avoid blank rows or columns within the table, merged cells, and multiple header rows.

Use a distinct, nonblank name for each column, such as Date, Region, Product, and Revenue.
Keep values in a column consistent. For example, enter revenue as numbers rather than mixing numbers with text such as “$25 approx.”
Check for inconsistent labels such as “North,” “north,” and “N.” Sheets may treat differently written values as separate categories.
Make sure dates are recognized as dates and quantities or amounts as numbers. A value stored as text may not sum as expected.
Keep each row at the same level of detail. If a row represents an order line, do not also add a totals row to the source list.

You can select a cell inside a continuous block of data before choosing Insert > Pivot table, or select the range explicitly, such as A1:F500. Check the range shown in the creation dialog. Including an existing grand-total row in the source can make the pivot table count or sum that total along with the underlying records.

Create the pivot table in Google Sheets

Open the spreadsheet in a web browser and select a cell in the source data, or highlight the complete data range, including its headers.
Choose Insert > Pivot table from the menu bar.
Review the data range in the dialog. Choose New sheet to put the pivot table on a separate tab, or choose Existing sheet and specify a location if it should sit on a current tab.
Click Create. Sheets opens the Pivot table editor, usually alongside the new pivot table.
In the editor, click Add beside Rows, Columns, Values, or Filters, then select a source column. Add more fields and adjust their settings to shape the summary.

A new sheet is a practical choice when you want to keep the source records untouched and avoid placing a summary over existing content. If you choose an existing sheet, leave enough empty cells around the destination: the pivot table may expand when you add categories, and existing content in its output area can obstruct it.

Choose rows, columns, values, and filters

The four sections in the Pivot table editor control different parts of the result. Understanding their roles is the key to building a useful report.

Rows creates groups listed vertically. Add Region to see one row per region, for example.
Columns creates groups across the top. Add Product to compare products side by side within each row group.
Values performs a calculation on a field, such as summing Revenue or counting Order ID.
Filters limits which source records contribute to the pivot table. For instance, filter to one region or exclude a product category.

After adding a field under Values, check the calculation setting. Numeric fields commonly use SUM, while text fields commonly use COUNTA or another count option. You can change how a value is summarized in the editor; the available choices depend on the field’s contents. If Revenue is unexpectedly counted rather than added, check whether its cells are stored as numbers and review the summary setting.

For a simple revenue report, add Region under Rows and Revenue under Values, then ensure Revenue is summarized by SUM. To see the same totals split by product, add Product under Columns. To compare order volume instead of money, add an order identifier under Values and select a count calculation. A count of a field counts populated entries, so blank identifiers can lead to a lower count than expected.

Build a practical sales summary

Imagine a source sheet with these columns: Order Date, Region, Product, Salesperson, Order ID, and Revenue. Each row represents one product line on an order. To find revenue by region and product, create a pivot table and arrange it like this:

Rows: Region
Columns: Product
Values: Revenue, summarized by SUM
Optional filter: Salesperson, if you want to inspect selected representatives

The result gives a total for each region-product combination, with row and column totals where applicable. If a region has revenue of $1,200 for Product A and $800 for Product B, its row total should be $2,000, assuming both figures come from the same included records and there are no other product categories. This makes a useful cross-check against a direct total of the source data.

For a second view, remove Product from Columns, add Salesperson to Rows, and use Order ID under Values with a count calculation. That answers how many order lines each salesperson handled, not necessarily how many distinct orders they handled. If one order can contain several product lines, the same Order ID may appear more than once. Be clear about the question being answered: counting rows, counting filled IDs, and counting unique orders are different tasks.

Sort, filter, and refine the results

Use the settings for a row or column field in the Pivot table editor to change how its categories are ordered. Depending on the field and settings, you can sort by a label or by a summarized value, such as total revenue. Sorting by the measure is useful when the goal is to identify the largest categories rather than list them alphabetically.

A filter in the editor applies to the source records used in the pivot table, not just to the visual order of its labels. Add a field under Filters and select the values to include or exclude. This is helpful for focusing a report on a particular year, region, or product. You can also use the filter controls available for row or column fields to restrict displayed categories. Check all active filters when a total seems lower than expected.

For a time-based report, use a properly recognized date column as a row field and inspect the available field options in the editor. If the source dates are inconsistent or stored as text, the categories may not group as expected. A reliable approach is to clean the date column in the source first, then verify the resulting categories before sharing the summary.

To make the report easier to read, format the pivot table’s displayed number cells with the toolbar’s number-format controls, such as currency or percent, as appropriate. Formatting changes how a value looks, not the underlying calculation. Avoid typing notes or extra values into cells that the pivot table occupies, since changes to its size or layout can conflict with those entries.

Keep the pivot table current

A pivot table uses a source range. Changes made to values within that range can be reflected in its summary, but adding new records below the selected range is a common reason recent data does not appear. For example, if the source was set to A1:F500 and new rows are entered at row 501, those rows are outside that range.

When new records are missing, inspect the source range shown in the Pivot table editor and update it to include the added rows. A range with spare rows can reduce this maintenance, provided those rows fit the sheet’s layout and the source headers remain at the top. Also check whether a filter excludes the new records or whether their category values differ from existing labels.

When source values are corrected, check whether the result changes as expected. If the table appears stale, close and reopen the spreadsheet or revisit the pivot table, then verify its range and settings. Do not assume that a missing category means the source record has disappeared; it may be outside the range, blank in the relevant field, or excluded by a filter.

Common problems and how to fix them

The pivot table has no useful field names: Confirm that the first row of the selected range contains headers and that none are blank. Recheck the start of the range if the actual headers are on a later row.
A total is a count instead of a sum: Select the value field in the editor and inspect its summary calculation. Also check that the source values are numeric rather than text.
The same category appears more than once: Look for extra spaces, inconsistent spelling, punctuation, or different data types in the source labels. Standardize those entries in the source.
New rows are missing: Expand the source data range to include them, then check filters. The pivot table cannot summarize records it was not set up to include.
The total seems too large: Check for a totals row included in the source, duplicated records, or detail rows that represent multiple lines per order. Confirm the intended unit of analysis.
The pivot table will not fit in its location: Move it to a new sheet or clear the cells where its output needs to expand. Existing content can interfere with the generated result.

When checking a surprising total, compare the pivot table with a small, manually inspected subset of the source. Confirm which rows qualify, what each row represents, and which field is being summed or counted. This is more reliable than changing settings until the result merely looks plausible.

Pivot tables compared with other approaches

A pivot table is a convenient choice when you want interactive summaries and may need to rearrange categories. It is generally quicker to explore than building multiple separate formulas, especially when you want to compare several fields or filter the report. Its main tradeoff is that the summary’s structure is controlled through the editor rather than being a regular list of formulas in each result cell.

Ordinary formulas can be better when you need a fixed dashboard, a calculation that follows custom rules, or a result positioned precisely within a designed sheet. Functions such as SUMIF or COUNTIF can calculate specific totals, but you must decide which categories and conditions to include. Google Sheets also offers other ways to analyze data, including charts and the QUERY function; these can suit repeatable reports or specific transformations but may take more setup or formula knowledge.

Google Sheets pivot tables and Excel PivotTables share the central idea of summarizing fields, but they are not the same interface. In Sheets, use Insert > Pivot table and configure the Pivot table editor in the browser. In Excel for Microsoft 365 on Windows, the corresponding feature is created from Insert > PivotTable, with Excel’s own dialog and field list. Instructions for one program should not be assumed to match the other menu by menu.

Frequently Asked Questions

Can I make a pivot table in Google Sheets for free?

Pivot tables are a built-in Google Sheets feature. You can create one from the spreadsheet’s Insert menu; the steps above do not require an add-on or a separate pivot-table application.

Can I create a pivot table from multiple sheets?

The standard pivot-table setup starts from a selected source range. If the records are split across tabs, first combine them into one suitable source range, then create the pivot table from that range. Make sure the combined records use consistent headers and column types.

Why is Google Sheets counting instead of summing?

The field may be set to a count calculation, or its source values may be stored as text. Check the field’s summary option in the editor and ensure the source cells contain numeric values. Text such as a currency symbol typed into the value itself can prevent normal numeric handling.

Will a pivot table include new rows automatically?

Only records inside its source range can be included. If you add rows beyond that range, edit the range in the Pivot table editor so the new records are covered. Also check that filters do not exclude them.

Can I edit the values directly in a pivot table?

A pivot table is a generated summary, so it is not the right place to correct an individual transaction or type a replacement total. Edit the source data instead, then check the pivot table’s range and settings to confirm the summary reflects the corrected records.

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.