The error occurs when a pivot table column is fed a raw (non-aggregated) value instead of an aggregated measure such as SUM, COUNT, or AVERAGE.
The “Pivot value must be an aggregate” error in Google Sheets appears when you attempt to place non-aggregated data inside the Values area of a pivot table. Because pivot tables are designed to summarize— not simply list— data, every value you add must pass through an aggregation function such as SUM, COUNT, or AVERAGE. Failing to do so confuses the pivot-table engine, which then throws this error. This guide walks you through the underlying causes, practical fixes, and proven best practices so you never see the message again.
Google Sheets has a strict rule: every field dropped into the Values section of a pivot table must be an aggregate measure. This ensures that the resulting pivot table is a concise summary rather than a raw data dump. The error generally stems from one of three situations:
Open the Pivot Table editor and:
This instantly resolves the error if a simple numeric column caused it.
Text cannot be summed or averaged. If you need the number of textual entries—names, IDs, statuses— apply COUNT (ignores blanks) or COUNTA (includes blanks) as the aggregation.
If a column contains both numbers and text, Sheets may refuse to auto-aggregate. Clean the data first:
=VALUE(cell)
.Once the data type is uniform, re-add the column to Values and specify an aggregation.
In the Pivot Table editor, calculated fields must also produce aggregates. For example, if your calculated field is
=Price * Quantity
wrap it in SUM()
:
=SUM(Price * Quantity)
Sheets now sees the field as an aggregated measure.
Double-check that the field is in the Values area and not in Rows or Columns. Only Values demands aggregation; moving the field may eliminate the requirement altogether.
If you frequently need advanced aggregations, consider the QUERY()
function before feeding data to a pivot table:
=QUERY(A1:E, "SELECT B, SUM(C) WHERE D = 'Completed' GROUP BY B", 1)
This pre-aggregates data, letting the pivot table consume a cleaner, already-summarized dataset, minimizing errors.
For dynamic calculations, combine ARRAYFORMULA()
with aggregation:
=ARRAYFORMULA(SUM(FILTER(C2:C, D2:D = "Completed")))
You can then insert the result directly into a pivot table or a dashboard cell.
sales_amount
vs. data1
) so your intent is obvious when aggregating.TRIM
, CLEAN
, VALUE
) or Apps Script before pivoting.Google Sheets’ insistence on aggregated values in pivot tables might feel restrictive, but it safeguards the integrity of your summaries. By ensuring every field in Values passes through an aggregation function—and by keeping your data clean and well-typed—you can build robust pivot tables without ever encountering the “pivot value must be an aggregate” error again.
Pivot tables are a cornerstone of spreadsheet analytics. The aggregate-value requirement is fundamental to producing accurate summaries. Misunderstanding it leads to broken reports, incorrect metrics, and lost trust in your data. Mastering this fix keeps dashboards reliable and prevents hours of troubleshooting when stakeholders spot discrepancies.
Pivot tables are designed to summarize data. Without aggregation, you’d merely be listing rows, which defeats the purpose. Aggregation functions—SUM, COUNT, AVERAGE—compress many rows into a single metric so patterns become visible.
Yes, but you must aggregate them with COUNT or COUNTA. Otherwise, the pivot engine has no mathematical operation to perform on the text.
Clean the data so that the column contains one data type. Convert text numbers with VALUE(), remove stray characters, or split mixed data into separate columns.
Google Sheets allows up to 20 value fields per pivot as of the current release. Keep your pivot lean for readability and performance.