Pivot Table

A pivot table summarizes a table of rows into groups without writing a formula for each one. Microsoft describes it as “a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data”. You choose what to group by and what to summarize, and the grouping can be changed by dragging a field rather than rewriting anything.

Four areas define the layout. Rows holds the fields that become groups; Values holds what gets summarized; Columns splits each group across a second dimension; Filters restricts the whole table. Adding the same value field more than once — once as a sum, once as an average, once as a count — is what turns a single grouping into a comparison, and it is usually the fastest way to see whether a low average reflects performance or group size.

Two defaults are applied silently and worth knowing. Numeric fields “placed in the Values area are displayed as a SUM”, while “if Excel interprets your data as text, the data is displayed as a COUNT”. If a field should total but shows a count, inspect both the source values’ types and the current summary function. Excel’s default depends on the detected type, and users can also select COUNT explicitly. Grouping a date field by year or month is the other common step, and it is what makes a trend visible in a sheet of individual dates.

Its relationship with the source data matters in both directions. A pivot table does not alter what it reads — it works “on a snapshot of your data, called the cache, so your actual data doesn’t get altered in any way” — which makes it the safest way to explore a sheet someone else built. That same snapshot goes stale: when rows are added, “any PivotTables that were built on that data source need to be refreshed”. A pivot read after an import but before a refresh is a summary of the previous data.

References:

• Create a PivotTable to analyze worksheet data
• Create and use pivot tables in Google Sheets