Excel PivotTables: Review Expenses without Double-Counting

A practical guide to excel pivottables, with a worked example, evidence checklist, common mistakes and steps to prepare a defensible working.

A supplier register contains a duplicate invoice and two credit notes. The useful result is a working that explains the facts, the calculation or classification, and the evidence behind the conclusion. This guide shows how to prepare that working and where a reviewer should investigate before accepting the result.

Scope and applicable period

Practical software and accounts workflow. Features depend on the actual product, edition, subscription and version. The example illustrates a control or computation, not certification of an installation.

Verification date: 11 October 2026. Figures and rates identified as assumptions are teaching examples; apply the stated conditions and the actual facts to a real assignment.

The key principle

A PivotTable summarises its source data; it does not remove duplicated transactions or decide whether a row is valid. Define the source at the correct grain, such as one accounting line per row, and distinguish line-level amounts from document totals repeated on each line. Refresh after changes and inspect whether the value field uses Sum or Count. Text amounts can produce a misleading count.

Worked example

Item	Value or fact	What it means
Invoice total	₹10,000	One document
Two item rows	₹6,000 and ₹4,000	Sum equals ₹10,000
Repeated header total	₹10,000 on each row	Incorrect sum becomes ₹20,000
Correct source amount	Line amounts only	Preserves document total

If the input repeats a document total, changing the PivotTable layout will not correct the underlying duplication. Select one consistent amount field and reconcile the aggregate with the ledger before using vendor or month summaries. Inspect a few high-value documents and credit notes through the drill-down detail. Use a proper source table or update the source range so that later rows are included. Preserve the input population and the refresh date with the review file.

A practical sequence

Define the transaction grain and inspect duplicate document and line identifiers. Use the intended numeric amount column and verify Sum rather than Count.

Build the category or month layout, review credits and filters, and refresh after changes. Reconcile the Pivot total to the validated source and inspect selected drill-down records.

Do not resolve a discrepancy by changing the report layout until you have checked whether the source itself repeats amounts.

Validate the output as well as the operation

Software can organise or calculate data quickly, but its result depends on the input population, configuration and the user’s decisions. Retain the original source, test a few known records and reconcile control totals after import or transformation. Check version support before distributing formulas or prescribing menu commands. Review missing, duplicated and unusually changed records rather than focusing only on a successful operation. When a workflow requires approval, access control or retention, confirm the actual product capability and supplement it with a documented team procedure where needed. A technically successful import is not the final accounting review.

Evidence checklist

Keep the following records linked to the same entity, period and working version. Identify missing items explicitly; a checked box should mean the document was examined and supports the stated conclusion.

Source-grain definition
Unique document and line identifiers
Amount-type checks
Source/Pivot total bridge
Refresh date and filters

Common mistakes and how to avoid them

Summing repeated invoice headers. Compare the conclusion with the source-grain definition and resolve any conflicting facts.
Accepting Count as total expense. Trace the affected item to the amount-type checks before finalising the working.
Leaving a hidden filter in a final report. Use the refresh date and filters to make the final position and remaining exceptions clear.

Before you finalise

Recheck the example’s assumptions against the actual assignment, resolve the identified exceptions and make the final figure or conclusion traceable to its source. Preserve the reviewed version and the reason for material changes. For this task, the refresh date and filters should agree with the conclusion presented to the client, reviewer or authority.

Frequently asked question

Can a PivotTable prove a duplicate is wrong? It can identify patterns, but review document and line identifiers to distinguish legitimate split lines from duplication.

Sources and further reading

Microsoft — sum values in a PivotTable