A pivot table summarises a long list – postings, invoices, orders – by any attribute: revenue by customer and month, costs by cost centre, receivables by due date. Without a single formula. Here are the features controllers actually need, grouped by task. Menu paths refer to Microsoft 365; some tabs have slightly different names in older versions.
Back to the overview: Excel for controllers: the 30 most important functions
Basics
1. Insert a pivot table
Click into the data and choose Insert → PivotTable. Format the source as a table first (Ctrl+T) so new rows are included automatically.
2. The four areas
Drag fields into Filters, Columns, Rows and Values. Typical in controlling: cost centre in rows, month in columns, amount in values.
3. Recommended PivotTables
Insert → Recommended PivotTables suggests ready-made summaries. Handy for getting to know an unfamiliar file.
4. Refresh
Pivot tables do not recalculate on their own. Alt+F5 refreshes the selected pivot, Ctrl+Alt+F5 refreshes all pivots and queries in the file.
5. Change data source
PivotTable Analyze → Change Data Source points the pivot at a new range. With a formatted table as source you will rarely need it.
6. Refresh on open
In PivotTable Options → Data, tick “Refresh data when opening the file” so nobody sees outdated numbers.
Calculating values
7. Value field settings
Right-click a value → Value Field Settings: sum, count, average, max or min. Set the number format for the whole column there too.
8. Show values as: % of grand total
Shows each value as a share of the total – for example each customer's share of revenue. Right-click → Show Values As.
9. % of row or column total
Share within a row or column, for example the cost structure of each month in percent.
10. Difference from
Subtracts the previous month or year – the fastest variance analysis there is. Base field month, base item “(previous)”.
11. % difference from
Like “difference from”, but in percent – the growth versus the previous month or year.
12. Running total in
Accumulates values across months, giving you a year-to-date curve without formulas.
13. Rank
Rank Largest to Smallest gives every customer or item its rank – the basis for an ABC analysis.
14. Calculated field
PivotTable Analyze → Fields, Items & Sets → Calculated Field calculates with whole fields, e.g. contribution margin = revenue − variable costs.
15. Calculated item
Calculates within a field, e.g. a “Plan − Actual” row from the items Plan and Actual. Use sparingly; it slows the pivot down.
16. Distinct count
Counts each customer once, even with 50 invoices. Only available if you tick “Add this data to the Data Model” when inserting the pivot.
Grouping, filtering, sorting
17. Group dates
Right-click a date → Group: by months, quarters and years. Daily postings become a monthly overview.
18. Group manually
Select several items → Group Selection. Useful for combining cost centres into divisions or countries into regions.
19. Group numbers into ranges
Put amounts or days into bands, e.g. receivables overdue 0–30, 31–60 and over 60 days.
20. Slicer
PivotTable Analyze → Insert Slicer: big filter buttons that colleagues can use without Excel know-how.
21. Timeline
Insert Timeline is a slicer for date fields: pick months, quarters or years with a slider.
22. Report connections
Right-click a slicer → Report Connections: one click then filters several pivot tables at once – the basis of any Excel dashboard.
23. Top 10 filter
Filter arrow → Value Filters → Top 10: the ten largest customers, suppliers or cost types. The number is up to you.
24. Label and value filters
Filter by text (e.g. accounts starting with “6”) or by amount (e.g. variances above 10,000).
Layout and output
25. Tabular form and repeat item labels
Design → Report Layout → Show in Tabular Form and Repeat All Item Labels. Only then can a pivot be processed further cleanly.
26. Subtotals and grand totals
Show or hide under Design. For reports usually subtotals at the top, for further processing switch them off.
27. Empty cells and column width
In PivotTable Options: “For empty cells show: 0” and untick “Autofit column widths on update” so the layout stays stable.
28. Drill down with a double-click
Double-clicking a number creates a new sheet with all postings behind it. Ideal for explaining an unusual variance right away.
29. Show report filter pages
With a field in the filter area, PivotTable Analyze → Options → Show Report Filter Pages creates one sheet per cost centre – a whole report pack in one click.
30. GETPIVOTDATA
Pulls a specific value from the pivot into a formatted report sheet. The formula stays correct even if the pivot layout changes.
When pivot tables hit their limits – several data sources, millions of rows, interactive dashboards – Power BI is the next step. The logic of rows, columns and values stays the same. For classic variance analysis see also budget vs actual.
Frequently asked questions
Why does my pivot table show old numbers?
Pivot tables do not recalculate automatically. Alt+F5 or Data → Refresh All brings them up to date. You can also set them to refresh when the file opens in PivotTable Options.
How do I count unique values in a pivot table?
Tick “Add this data to the Data Model” when inserting it. Value Field Settings then offer “Distinct Count”.
What is the difference between a slicer and a filter?
Both filter the same data. A slicer is clearly visible, works with one click and can control several pivot tables at once.