| Concept | Description |
|---|---|
| Structure | |
| What a PivotTable Does | Group-then-aggregate across categories, the same operation as group_by with summarise in R |
| Building on a Named Table | A named Table as source picks up new rows on refresh where a fixed range does not |
| The Four Zones | Rows and Columns group, Values aggregates, Filters applies to the whole table at once |
| Aggregation Settings | |
| Summarize Values By | Sum, Average, Count, Max, Min, and the sample or population standard deviation |
| Show Values As | Percent of total, row, column or parent, difference from a baseline, running total, and rank |
| Showing Count Alongside Average | Adding the same field twice shows the aggregate and the number of records behind it |
| Grouping and Filters | |
| Grouping Dates | Right-click a date to group by year, quarter or month, nesting when several are selected |
| Grouping Numbers into Bands | Grouping a numeric field by an interval builds a frequency distribution without formulas |
| Slicers and Timelines | Visible filter buttons that can drive several PivotTables at once through Report Connections |
| Extending and Fixing | |
| Calculated Fields | Computed from field sums rather than row by row, giving a weighted ratio |
| GETPIVOTDATA | Retrieves a value by field and item name so it survives the table being rearranged |
| Refreshing | The table caches its source; edits do not appear until a refresh is run |
| PivotCharts | A chart bound to the table, filtering together, and the basis of a slicer-driven dashboard |
| Common Failures | Most failures trace to the source data: text in numeric columns, blanks, or trailing spaces |
17 PivotTables and PivotCharts
Average yield by district, split by irrigation type, with a count of farms in each cell. Written as formulas that is a grid of AVERAGEIFS calls, one per cell, each with its criteria hard-coded, and the whole thing has to be rebuilt when someone asks for the same breakdown by crop instead.
A PivotTable produces it by dragging four field names, and the rebuild takes about three seconds. For summarising a dataset across categories, nothing else in Excel comes close.
The operation underneath is group-then-aggregate, the same thing aggregate() does in base R and group_by() with summarise() does in dplyr.
17.1 Building One
Click any cell inside FarmData, then Insert, then PivotTable. Excel proposes the detected range and offers a destination. A new worksheet is the safer choice, since a PivotTable placed next to existing data will overwrite it as it grows.
Point it at a named Table rather than a range. FarmData as a source picks up new rows on refresh; A1:K61 does not.
The empty PivotTable appears with a field list beside it. Drag District to Rows, YieldQtPerHa to Values, and the district averages appear once the value field is switched from Sum to Average. Drag Irrigation to Columns and the table becomes a cross-tabulation.
Four rules govern what the source data must look like, and all four were covered in Basics of Excel: one header row, no blank rows or columns inside the range, no merged cells, and one variable per column. A PivotTable built on a sheet that breaks any of them either refuses to build or produces a field list full of entries named Column1.
17.1.1 The Four Zones
Rows and Columns define the grouping. Values holds what gets aggregated. Filters puts a field above the table, applying to everything at once.
More than one field can sit in a zone. District above CropCode in Rows nests crops inside districts, and the Design tab controls whether that displays in compact, outline, or tabular form. Tabular form puts each field in its own column with repeated labels, which is the only form that exports usefully to another tool.
17.2 Value Field Settings
A numeric field dropped into Values defaults to Sum. A text field defaults to Count. Neither default is necessarily what was wanted, and the sum of a column of yields per hectare is meaningless in most contexts.
Right-click any value cell, then Value Field Settings, or click the field in the Values zone.
Summarize Values By offers Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp. The distinction between StdDev and StdDevp is the sample against population question from Essential Formulas and Functions.
Show Values As is the more useful half, and the more overlooked. It leaves the aggregation alone and changes how the result is expressed:
| Option | What it gives |
|---|---|
| No Calculation | The raw aggregate |
| % of Grand Total | Each cell as a share of everything |
| % of Column Total | Each cell as a share of its column |
| % of Row Total | Each cell as a share of its row |
| % of Parent Row Total | Share within its group, for nested fields |
| Difference From | Change against a chosen baseline category |
| % Difference From | The same as a percentage |
| Running Total In | Cumulative down a field, useful over dates |
| Rank Largest to Smallest | Rank within the group |
| Index | Relative importance, adjusting for row and column totals |
The same field can be added to Values twice with different settings, giving average yield and count of farms side by side, which is how a summary table should normally be read. An average over three farms and an average over twenty deserve different amounts of trust, and showing the count is what makes that visible.
Rename the header while there. Sum of YieldQtPerHa is Excel’s label, not a report heading. The Custom Name box at the top of the dialog accepts anything except the exact name of an existing field, so Avg Yield (qt/ha) works while YieldQtPerHa alone does not.
17.3 Grouping
Dates. Drop a date field into Rows, right-click any date, then Group. Excel offers Years, Quarters, Months, Days, Hours. Selecting several nests them, so Years plus Months gives months within years rather than January of every year pooled together. Recent versions group dates automatically on insertion, which can be undone through Ungroup.
Running this on the MonthlyPrices sheet with Month in Rows, grouped by Year and Quarter, and Price in Values set to Average, gives a quarterly price series in about four clicks.
Numbers. Right-click any value in a numeric row field and choose Group to set a starting point, an ending point, and an interval. Grouping YieldQtPerHa starting at 20, ending at 60, by 10 produces four bands and counts farms in each, which is a frequency distribution built without a single formula. That distribution is the same object described in Measures of Distribution.
Manual grouping. Select several row labels, right-click, and choose Group to bundle them under a heading that can be renamed. Three districts grouped as Coastal and two as Interior creates a zone breakdown without touching the source data.
17.4 Sorting and Filtering Inside a PivotTable
Row and column labels sort through their dropdown arrows, and value columns sort by right-clicking a value and choosing Sort. Sorting by a value column reorders the row labels by their results, which is how a ranked summary is produced.
Label Filters act on the category names, Value Filters on the aggregated numbers. Value Filters include Top 10, which despite the name accepts any count or percentage and can work on any value field in the table.
Right-click, Filter, Keep Only Selected Items works on a selection of row labels for a quick subset.
17.4.1 Slicers and Timelines
Slicers are filter buttons that sit beside the table rather than hiding in a dropdown. Select the PivotTable, then PivotTable Analyze, then Insert Slicer, and pick the fields.
Two advantages over dropdown filters. The current selection is visible, so nobody reads a filtered table thinking it shows everything. And one slicer can control several PivotTables at once through Report Connections, which is what turns a sheet of separate tables into a dashboard where every element responds to one click.
Timelines are the date equivalent, giving a sliding scale across months, quarters, or years. Insert Timeline, on the same tab, and the field must be a genuine date rather than text.
17.5 Calculated Fields
PivotTable Analyze, then Fields, Items, and Sets, then Calculated Field adds a field computed from others.
Total production from yield and area:
= YieldQtPerHa * AreaHaSpend per quintal:
= FertilizerSpend / YieldQtPerHaOne behaviour matters and catches people out. A calculated field operates on the sums of its inputs, not on the individual rows. A field defined as FertilizerSpend / YieldQtPerHa in a district row returns the sum of spend divided by the sum of yield across that district, which is a weighted ratio, not the average of the per-farm ratios. The two differ whenever farm sizes differ, and the weighted version is usually the one wanted for a district summary. It is worth knowing which is being reported.
Where the per-row calculation is genuinely needed, add a column to the source data instead and let the PivotTable aggregate it.
17.6 GETPIVOTDATA
Clicking a PivotTable cell while building a formula elsewhere produces something like:
=GETPIVOTDATA("YieldQtPerHa", $A$3, "District", "Guntur")rather than a plain B5. This is deliberate. The function retrieves a value by its field and item names, so it keeps pointing at the right number when the PivotTable is rearranged and the value moves to a different cell. A plain reference would silently start reporting whatever landed in B5 afterwards.
It is genuinely better for a report cell that must stay correct. It is a nuisance when dragging a formula across a range, since the field names are hard-coded and do not adjust as it fills.
To switch it off, go to PivotTable Analyze, open the dropdown beside Options, and untick Generate GetPivotData. Typing the cell reference by hand also avoids it.
17.7 Refreshing
A PivotTable holds a cached copy of the source data. Editing the source changes nothing in the table until it is refreshed. Right-click and Refresh, or Alt + F5, or Refresh All at Ctrl + Alt + F5 for every PivotTable in the workbook.
To refresh on open, go to PivotTable Analyze, Options, Data, and tick Refresh data when opening the file.
Where the source is a plain range rather than a Table, added rows fall outside it and refreshing alone does not help. Change the source through PivotTable Analyze, Change Data Source, or convert the range to a Table once and stop having the problem.
Stale PivotTables are among the most common causes of a wrong number reaching a report. The table looks current, carries a sensible date in the header, and reflects data from two weeks ago.
17.8 PivotCharts
Select the PivotTable, then PivotTable Analyze, then PivotChart. The chart is bound to the table, so filtering either one updates both and the chart carries its own field buttons.
Column charts suit category comparisons, line charts suit grouped dates, and stacked columns show composition. Pie charts remain a poor choice for anything beyond three or four categories, for reasons covered in Charts and Visualization.
A dashboard is typically two or three PivotCharts plus a slicer connected to all of them, arranged on one sheet with gridlines hidden. That is the entire construction, and it takes about fifteen minutes.
17.9 When Something Goes Wrong
| Symptom | Cause |
|---|---|
Field list shows Column1, Column2
|
The header row is missing or blank |
| A numeric field defaults to Count | The column contains text, often a stray N/A
|
| New rows do not appear | Source is a range, not a Table, or the table was not refreshed |
| Cannot group dates | The date column is text, not dates |
| Cannot group numbers | The column contains text or blanks |
(blank) appears as a category |
Empty cells in the row field |
| Totals look wrong | A filter or slicer is active somewhere off screen |
| Percentages do not sum to 100 | Show Values As is set against the wrong total |
| Duplicate-looking rows | Trailing spaces make Guntur and Guntur distinct categories |
Nearly every entry traces back to the source data rather than the PivotTable. A table that will not group dates is reporting that the date column is text, which is worth knowing regardless.