Pivot tables

A pivot table turns a long transaction list into an answer in a few drags. For a sales register with 40,000 rows, it replaces a page of SUMIFS formulas and recalculates instantly.

Build one in four steps

1. Click inside your data, then Insert, PivotTable.
2. Drag Region to Rows, Month to Columns, Sales to Values.
3. Add Slicers for interactive filtering.
4. Right-click a value, Show Values As, % of Grand Total.

Before you start, select the data and press Ctrl+T to turn it into a Table. A pivot built on a Table picks up new rows automatically when you refresh; one built on A1:F5000 does not.

The four drop zones

Zone	Put here	Example
Rows	What you group by, down the page	City, Salesperson
Columns	A second grouping, across	Month, Category
Values	The numbers	Sum of Amount, Count of Invoice
Filters	A page-level filter	Financial year

Value field settings

Double-click a value heading to change the aggregation from Sum to Average, Count, Distinct Count, Max or Min, to rename it ("Sales Rs") and to set the number format. Do the formatting here, never cell by cell: a refresh would wipe cell formatting away.

Distinct Count (which needs "Add this data to the Data Model" when the pivot is created) answers "how many unique customers bought this month?", something plain Count cannot do.

Show Values As

This menu is the most underused feature in Excel.

Option	Answers
% of Grand Total	What share does each city contribute?
% of Parent Row Total	Within a region, how do its cities split?
Difference From (previous)	How much did sales change month on month?
% Difference From	Growth percentage
Running Total In	Cumulative sales through the year
Rank Largest to Smallest	Rank products by revenue

Drag the same field into Values twice to see the amount and its percentage side by side.

Grouping

Right-click a date in Rows and choose Group to roll days into Months, Quarters and Years. Numbers can be grouped too, which is a quick way to bucket order values into ranges of Rs 5,000.

Calculated fields

PivotTable Analyze, Fields, Items & Sets, Calculated Field adds a formula computed on the pivot's totals:

Name:    GST
Formula: = Amount * 0.18

Name:    Realisation
Formula: = Collected / Amount

Remember that a calculated field works on the summed values, so use it for ratios of totals, not row-level logic.

Refresh and layout

Data changed? Right-click, Refresh, or Refresh All (Ctrl+Alt+F5) for every pivot in the workbook. Set PivotTable Options, Data, Refresh data when opening the file. For a table you intend to copy elsewhere, switch to Design, Report Layout, Show in Tabular Form and Repeat All Item Labels.

Common mistakes

• Blank or merged header cells in the source; Excel refuses to build the pivot.
• Leaving a filter on and reporting a partial figure as the total.
• Numbers stored as text, so Values defaults to Count instead of Sum.
• Typing formulas next to a pivot: they break as the pivot grows. Use GETPIVOTDATA or a separate sheet.