A pivot table turns a ledger export into a review in seconds, as long as you set it up so it cannot quietly go out of date.
At month-end, pivot tables summarise ledger exports by account, cost centre or supplier, show month-on-month movements and support variance reviews. Put the source data in an Excel table so new rows are included, refresh before every use, check the pivot grand total against the trial balance and use GETPIVOTDATA or SUMIFS to feed reports so layouts can change safely.
Rows: account and account name. Values: sum of amount. Filter: period. Switch to Design > Report Layout > Show in Tabular Form and Repeat All Item Labels so the result can be copied or looked up cleanly. Check that the grand total is zero for a full trial balance, or agrees to the ledger total for an extract.
Rows: account. Columns: period. Values: sum of amount. Then add the amount field a second time and set Show Values As > Difference From, base field period, base item (previous). You now have each month's balance and the change from the month before side by side. Sort by the largest movements and explain them. See balance sheet flux analysis for how to set review thresholds.
Rows: cost centre, then account. Columns: period. Values: sum of amount. Add a slicer for cost centre (PivotTable Analyze > Insert Slicer) so budget holders can review their own area. Use Show Values As > % of Row Total to see how spend is spread across the year.
Rows: supplier, then invoice number. Values: count of amount and sum of amount. Filter the count for values greater than one to list invoice numbers posted more than once for the same supplier. It is a fast first pass before the fuller tests in finding duplicate invoices.
If the source has transaction dates rather than periods, right-click a date in the pivot and choose Group, then Months and Years. Make sure the dates are real dates, not text, or grouping will not be offered.
Cell references into a pivot table, such as =Pivot!C14, break as soon as the pivot changes shape. Use GETPIVOTDATA, which finds the value by its labels:
=GETPIVOTDATA("Amount",Pivot!$A$3,"Account","4000","Period","2026-10")
Match the item types to the source: if the account or period fields hold numbers or dates, pass numbers or dates, not text. Alternatively, build the report with SUMIFS directly on the table, which does not need a refresh:
=SUMIFS(GL[Amount],GL[Account],A5,GL[Period],C$1)
Pivot calculated fields work on the summed values, not row by row. A calculated field such as Price * Quantity multiplies total price by total quantity, which is usually wrong. Add the calculation as a column in the source table instead, then summarise it.
Add these checks to the month-end close checklist so they happen every month.
Pivot tables work the same in UK and US Excel. Regional settings change date formats and the argument separator in formulas such as GETPIVOTDATA, so check both when sharing files across countries.
Reviews every balance sheet reconciliation with an on-device AI and 20 review tests, flags vague and wrong-account reconciling items, and builds a risk-ranked review queue. Runs in your browser on your own computer: nothing is uploaded.
12 questions on how strong your close controls are. Free to use online.
The Month-End Close & Controls System tests the trial balance against the sum of reconciled balances, keeps every open item on one exception dashboard and blocks sign-off while exceptions remain open. Excel, no macros, one-time price.
It was probably built on a fixed range. Convert the source to an Excel table with Ctrl + T, point the pivot at the table name and refresh.
Add the amount field twice and set the second to Show Values As, Difference From, with period as the base field and previous as the base item.
No. Use GETPIVOTDATA or SUMIFS on the source table, so the report still works when the pivot layout changes.
Last updated 11 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.