Skip to content
Controllingblog

Home❧Software & Data❧Pivot tables: the 30 most important features for controllers

Pivot tables: the 30 most important features for controllers

Pivot tables summarise thousands of postings with a few clicks. These 30 features turn a quick overview into a report that grows with you every month.

Software & Data14 min readUpdated 14 Nov 2026
Illustration: laptop with dashboard, database and cloud +12% 4.2 M 98%

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.

=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.

=GETPIVOTDATA("Amount", $A$3, "Cost centre", "4100", "Month", "Jan")
Practical tipNever build pivot tables on exports you paste in by hand every month. Load the data with Power Query into a table and base the pivot on it. Next month, Ctrl+Alt+F5 is all it takes.

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.