A pivot table in Google Sheets summarizes a large dataset without requiring you to write a separate formula for every category. It can total sales by product, count orders by status, compare expenses by month or show average scores by team. Start with a clean table, select your data and choose Insert → Pivot table.
Quick steps: create a pivot table in Google Sheets
- Open a spreadsheet with column headers and consistent data types.
- Select the data range, including the header row.
- Choose Insert → Pivot table.
- Choose a new sheet or an existing sheet as the destination.
- In the pivot table editor, add a field under Rows to group the data.
- Add a numeric field under Values and choose SUM, COUNT or another aggregation.
- Optionally add Columns and Filters to compare groups or narrow the result.
Menu names and exact layouts can change between versions. The important concepts remain rows, columns, values and filters.
Practice dataset: sales by product and region
Create a small sheet with these columns:
| Date | Region | Product | Revenue |
|---|---|---|---|
| Oct 1 | East | Notebook | 120 |
| Oct 1 | West | Pen | 80 |
| Oct 2 | East | Notebook | 60 |
| Oct 2 | West | Notebook | 100 |
| Oct 3 | East | Pen | 40 |
Put the headers in A1:D1 and the five data rows in A2:D6. Select A1:D6 before inserting a pivot table. For this example, the date labels are illustrative; in a real workbook, enter valid date values rather than arbitrary text if you intend to group by month.
Example 1: total revenue by product
In the pivot editor, add Product to Rows and Revenue to Values. Set the Values aggregation to SUM. The resulting summary should show:
| Product | Sum of revenue |
|---|---|
| Notebook | 280 |
| Pen | 120 |
| Grand total | 400 |
The pivot table groups the three Notebook transactions and the two Pen transactions automatically. It does not change the original records. If you see a COUNT instead of a SUM, check the aggregation setting and verify that the revenue cells contain numbers rather than text.
Example 2: compare revenue by region and product
Add Region to Rows, Product to Columns and Revenue to Values, aggregated by SUM. Your summary will look like this:
| Region | Notebook | Pen | Total |
|---|---|---|---|
| East | 180 | 40 | 220 |
| West | 100 | 80 | 180 |
| Total | 280 | 120 | 400 |
Pivot tables are useful because this cross-tabulation requires only a few selections in the editor. You can switch the rows and columns to answer a different question without rebuilding the underlying dataset.
Example 3: count orders instead of adding revenue
To count transactions, add a field such as Order ID to Values and select COUNTA or an appropriate count aggregation. If your source data does not include an order ID, you can count nonblank entries in a consistently populated field. Be aware that counting rows is not the same as counting unique customers: one customer might place multiple orders.
If you need a distinct customer count, review the available aggregation options and the quality of your source identifiers. A pivot that counts every row cannot tell whether two rows belong to the same person unless the data makes that relationship clear.
Rows, columns, values and filters explained
| Area | Purpose | Example |
|---|---|---|
| Rows | Groups data vertically | Product or region |
| Columns | Creates side-by-side categories | Month or sales channel |
| Values | Calculates a result for each group | Sum of revenue |
| Filters | Limits which source records contribute | Only one country or year |
A common mistake is adding a text field to Values and expecting a sum. Text values are usually counted, while numeric fields can be summed or averaged. Always inspect the aggregation setting before trusting a report.
How to group pivot table dates by month
If your source column contains real spreadsheet dates, a pivot table can help analyze records over time. Add the date field to Rows, then use the grouping options available for date values to summarize by month, quarter or year. If grouping options are missing, check whether the source dates were imported as plain text.
For more predictable reporting across years, you can add a helper column such as =TEXT(A2,"yyyy-mm") and use that text label as a grouping field. Remember that the helper column returns text; keep the original date for actual date arithmetic.
How to sort and filter a pivot table
Use the pivot editor to sort row labels alphabetically or order groups by a calculated value when the available options allow it. For example, sorting products by sum of revenue can surface your largest categories. Filters can exclude test records or restrict the analysis to one region.
Check totals after filtering: A pivot total reflects the records currently included in the calculation. If you filter out a region, the grand total may no longer match the unfiltered source dataset, and that is expected.
How to update a pivot table when data changes
If you edit values inside the source range, the pivot table should reflect the updated data as the spreadsheet recalculates. However, newly added rows may fall outside a fixed source range. If your pivot still shows old totals, inspect the source range and expand it to include the new records.
For a frequently growing dataset, choose a source range that accommodates expected additions without pulling in unrelated columns or excessive empty cells. Consistent headers and data types make maintenance easier.
Pivot table troubleshooting
| Problem | Likely cause | What to do |
|---|---|---|
| Revenue is counted, not summed | Values aggregation set to COUNT or data stored as text | Choose SUM and convert source values to numbers |
| Some records are missing | Source range excludes rows or a filter is active | Inspect the range and filters |
| One category appears twice | Inconsistent spelling or hidden spaces | Clean the source labels |
| Dates will not group | Text instead of date values | Convert the source column to actual dates |
| Pivot is hard to read | Too many row and column categories | Reduce dimensions or apply a relevant filter |
| Unexpected blank category | Empty source cells | Fill or filter missing values |
Pivot tables vs SUMIF and QUERY
A pivot table is a strong choice for exploratory analysis because you can rearrange groups through a visual editor. SUMIF and SUMIFS are useful when you want a specific calculated total embedded in a fixed report. QUERY can express filtering and aggregation in a formula, but it has a learning curve. Choose based on how often you need to change the question.
For a simple report, you might use =SUMIF(C2:C100,"Notebook",D2:D100) to total Notebook revenue. A pivot table becomes more attractive when you want that total for every product and region without creating a separate formula for each category.
Frequently asked questions
Can Google Sheets make pivot tables?
Yes. Select a dataset and choose Insert → Pivot table, then configure rows, columns, values and filters.
Does a pivot table delete or rearrange source data?
No. It creates a separate summary view based on the source range.
Why does my pivot table show COUNT instead of SUM?
Check the Values aggregation setting and whether the source values are recognized as numbers.
Can I create a pivot table from multiple sheets?
A pivot table operates on a selected source range. If data is spread across tabs, you may need to combine it into one consistent source table first.
Can I use pivot tables to make charts?
Yes. You can build charts from summarized results, but check that your chart continues to reference the correct pivot output after changing its layout.
Official help and related reading
For interface details, see Google’s official pivot table guide. To build a more advanced report, combine pivot tables with our Google Sheets formulas guide and conditional formatting tutorial.
Other Articles
