Faragiri

Learn Pivot tables Excel

A supermarket buying office in Parana: what the source table has to look like, what each of the four zones does, and the three defaults that produce a wrong summary.

7Sections
4Min read
4Depth

Facundo has 4,318 delivery lines from 62 suppliers in a buying office in Parana, and a question that takes eleven rows to answer: which suppliers were late, by how much, in which month.

A pivot table does that in about forty seconds, and it does it wrong about a third of the time for reasons that are all in the source data rather than in the pivot.

What the source has to be

One header row, no blank rows, no merged cells, and one kind of thing per column. That is the entire requirement and every pivot problem starts with a violation of it.

Source data hygiene

Four structural requirements before inserting a pivot table

Every pivot table failure begins in the source range rather than in the pivot configuration.

Pivot engine reliability
  • Single header rowTwo header rows create confusing generic names like Column1 and Column2
  • No blank or merged cellsMerged cells destroy rectangular grid coordinates needed for grouping
  • One data type per columnMixing text like '3 days' into numeric columns silently forces Count instead of Sum
  • Wrap range with Ctrl+TConverting the range into an Excel table makes the pivot range expand automatically

Raw worksheet data

Taking two seconds to press Ctrl+T ensures future rows are included in the pivot without rebuilding.

Facundo's export had two header rows, because the system printed a title above the headings. A pivot built on it offered fields called Column1 and Column2, which is the tell for that specific fault.

One kind of thing per column matters most. A column holding both 3 and "3 days" and "-" is three kinds of thing, and a pivot summing it will silently count what it cannot add.

Selecting the range and pressing Ctrl+T first is worth the two seconds: a pivot built on a table grows with it, and a pivot built on A1:H4318 does not.

The four zones

Rows is what you want one line per. Facundo's is supplier, so eleven suppliers means eleven rows.

4,318 delivery lines

Where four thousand rows go on the way to eleven

Each stage is a decision made in one of the four zones, reducing thousands of delivery records to eleven actionable rows.

  1. The export, as it arrivesRaw delivery lines arriving from the purchasing system4,318
  2. After Filters removes last yearFiltered out before calculation rather than hidden afterwards2,960
  3. After Rows groups by supplierConsolidated into 62 distinct supplier rows62
  4. After lateness banded in threesGrouped numeric intervals condensed into 11 summary rows11

Four numbers, four zones, and not a single manual formula required.

Columns is a second grouping, laid sideways. Month across the top turns a list into a grid, and it is the zone people overuse: more than about eight columns and the thing is unreadable.

Values is what gets calculated, and it is the zone that has to be checked rather than accepted. It defaults to Sum for numbers and to Count for anything else, and that default is where most wrong pivots come from.

Filters removes rows before any of it happens, which is different from hiding them afterwards, and the difference matters: a filtered pivot's percentages are computed on what remains.

Facundo's own chain runs 4,318 lines, 2,960 after the year is filtered out, 62 supplier rows, and 11 once lateness is banded in threes. Four numbers, four zones, and no formula anywhere.

The three defaults that lie

Count of instead of Sum of. If one cell in a numeric column is text, Excel treats the column as text and counts it. A total of 4,318 where a currency figure was expected is that fault every time.

Deceptive summary defaults

Three calculation defaults that produce misleading summaries

Excel defaults to Count on mixed columns, averages averages improperly, and never refreshes automatically.

  • 4,318Count substituted for SumA single text entry in a numeric column causes Excel to count all rows instead of adding amounts
  • 3.2Average days per deliveryThe true row-level lateness average across individual shipment lines
  • 8.7Average days per supplierThe distorted average of supplier averages that weights small vendors equally with large ones
  • 200Unrefreshed source rowsNew records added to source data that remain completely invisible until Refresh is pressed

A pivot table showing last month data is not in an error state; it looks identical to a current one until refreshed.

Averages of averages. Setting Values to Average gives the average of the rows in each group, which is not the average per unit unless every row represents the same quantity. Facundo's late days averaged 3.2 per delivery and 8.7 per supplier, and both numbers are correct answers to different questions.

And the pivot that does not refresh. It is a snapshot taken when it was built, so adding 200 rows to the source changes nothing on screen until Refresh is pressed. A pivot showing last month's data is not an error state; it looks exactly like a current one.

Grouping, which is where the time goes

Right-click a date field in Rows and choose Group, and Excel offers months, quarters and years. That replaces a helper column with =MONTH() in it, and it is the single most useful thing in the whole feature.

Field grouping methods

How right-click grouping transforms dates and numbers

Grouping dates eliminates manual =MONTH() helper columns, while grouping numbers turns raw rows into distributions.

Right-Click Grouping

  • Date groupingReplaces helper formulas with built-in time hierarchies
    • MonthsGroups daily timestamps into monthly totals
    • QuartersAggregates months into fiscal quarters
    • YearsConsolidates multi-year historical trends
  • Numeric groupingConverts thousands of continuous values into distributions
    • Order value bandsGroups transaction sizes in steps of 5,000
    • Lateness intervalsBands delivery delays into three-day brackets

Right-click grouping converts thousands of distinct timestamps or numbers into clear distributions in seconds.

Numbers group too, into bands: order values in steps of 5,000, or lateness in bands of three days. That converts a list of 4,318 different numbers into a distribution, which is usually the question somebody actually asked.

Show values as, which nobody finds

Right-click a number in the Values area, choose Show Values As, and the same field can display as a percentage of the column total, of the row total, of a parent row, or as a running total.

Calculation display modes

Four calculation perspectives inside Show Values As

Right-clicking a value field exposes contextual percentages without writing custom formula columns.

Show Values As
  • % of Column TotalComputes each cell's share of the entire vertical column sum
  • % of Row TotalShows the distribution of each category across horizontal dimensions
  • % of Parent Row TotalCalculates each supplier's specific late delivery rate within its parent group
  • Running TotalAccumulates progressive totals across sequential monthly periods

% of Parent Row Total turns raw late delivery counts into individual supplier lateness rates with zero formulas.

Facundo's most-used view is % of Parent Row Total, which turns late deliveries per supplier per month into each supplier's own lateness rate, and it requires no formula at all.

When a pivot is the wrong tool

When the summary needs to feed another calculation, because pivot output moves when the data changes and a formula pointing into it breaks. GETPIVOTDATA exists for that and is awkward; a SUMIFS against the source is usually better.

Tool selection criteria

When a pivot table is the wrong solution

Pivot tables excel at interactive exploration, but shift unpredictably when feeding downstream formulas.

  • Pivot Table StrengthsExploratory aggregation
    • Ad-hoc multi-dimensional slicing and filtering
    • Instant distribution banding on dates and numbers
    • Quick summary views for human readers
  • When to Choose Other ToolsDownstream feeds and pipelines
    • Feeding other formulas where cell coordinates shift unexpectedly
    • Awkward GETPIVOTDATA calls best replaced by direct SUMIFS
    • Weekly recurring raw exports better automated with Power Query

Workflow Boundaries

If the summary feeds downstream workbook calculations, SUMIFS against the source table is more dependable.

And when the same summary is needed every week from a new export, which is a repeatable-import problem rather than a summarising one.

What to build them with, as of 2026

Insert, then PivotTable, in every version of Excel since 2007, which replaced a page of SUMIF formulas that had to be rewritten whenever a category appeared.

Feature maturity progression

The modern pivot toolstack from 2007 to 2026

Modern pivot tables rely on structured tables for dynamic sizing, slicers for interactivity, and assistants for zone layout.

  1. 042026 Assistant PromptsModels configure zones quickly but cannot spot text numbers corrupting calculationsZone Setup
  2. 03Excel 2010 SlicersReplaced creating separate workbook copies with clickable visual filter buttonsReader UI
  3. 02Ctrl+T Structured TableEnsures source range expands automatically as new rows arriveRange Stability
  4. 01Excel 2007 PivotTableReplaced sprawling manual SUMIF grids with dynamic multidimensional aggregationCore Engine

An assistant can configure your four zones in seconds, but only clean source data prevents Count from replacing Sum.

Ctrl+T on the source first, which makes the pivot's range grow by itself.

Slicers, from Excel 2010, which are filter buttons a reader can press without knowing what a pivot is. They replaced sending four versions of the same summary to four people.

A model asked how to configure a pivot for a stated question will name the zones correctly, free on every major assistant. It cannot see that one cell in your numeric column is text, which is what turns Sum into Count and produces the wrong summary this page is about.

Getting the export into one header row and one type per column is text, dates and the imported mess, and doing that transformation the same way every week is repeatable imports and transformations. Turning the eleven rows into something a reader sees at a glance is choosing one, and formatting it honestly, under the most used analysis tool in the world.

Alongside this

Above this