Pivot tables: reconcile totals before sharing a dashboard

A PivotTable summarizes its selected source and filters. It does not establish that the source is complete, unique or aligned with the business question. Reconcile the underlying population and aggregation before adding charts or sharing the workbook.

The synthetic spreadsheet quality workbook provides a useful test. Its clean Sales table has seven invoice lines across January and February, with one pending line and one unmapped category. Its raw table also contains a repeated line delivery.

Build the intended January paid view

Select a cell in Sales on Clean_Lines and create a PivotTable from that table. Place Category in Rows and NetPaise in Values. Confirm that the value aggregation is Sum. Filter Status to Paid and InvoiceDate to the January interval.

For clarity, a helper MonthStart column can use DATE(YEAR([@InvoiceDate]),MONTH([@InvoiceDate]),1), then the PivotTable can filter that field to January 1, 2026. Use actual date values and include the new column in the source table before refreshing.

Expected output:

The result is a synthetic invoice-value report in paise, not a statement about recognized revenue. The commercial meaning of Paid and the treatment of tax or refunds would need separate definitions in a real dataset.

Microsoft's PivotTable creation guidance covers creating and refreshing the summary. The exact interface varies across Excel editions.

Confirm the aggregation instead of trusting the label

If NetPaise was imported as text, Excel may use Count rather than Sum. A result of four could then be the number of eligible line values, not 47,500 paise. Renaming that field “Sales” does not change its aggregation.

Inspect Value Field Settings and the source column type. Fix the import or conversion rule and refresh. Avoid manually replacing a few visible text cells while leaving the underlying refresh process unchanged.

The fixture's clean NetPaise column is numeric. Its expected controls are independently calculated in verification-results.json, so you can compare the PivotTable against a separate computation.

Distinguish line count from invoice count

January paid activity has four lines but three invoices. I001 contains two legitimate lines. Counting InvoiceID values in a normal line-level PivotTable counts four populated cells; it does not automatically count distinct invoices.

Use an explicitly distinct-count-capable model or a separately prepared invoice-grain table when the measure is invoice count. Verify the feature available in your Excel edition. Do not remove duplicate InvoiceID rows from the sales table, because that would delete legitimate line value.

Likewise, average line value is not average invoice value. The desired average invoice value here is 47,500/3, approximately 15,833.33 paise; dividing by four answers a different question.

Inspect filters and the source boundary

Confirm that the source is Sales rather than RawSales. The replay-inclusive source would overstate January paid value by 10,000 paise. Check report filters, slicers and hidden selections when totals differ; a visible chart title may not describe the current filter state.

Use the table as the source so the source definition can grow with new rows, then refresh the PivotTable. Appending a row to the worksheet outside the actual source range does not guarantee inclusion. Verify the table boundary and refreshed result rather than relying on appearance.

Keep a report-as-of note and source-loaded-through value. Refreshing a PivotTable does not prove that the upstream extract has received all expected records.

Test the report with a controlled addition

Add a temporary January Paid Software line worth 1,000 paise inside the Sales table. Refresh and expect Software 30,000 and the grand total 48,500. Remove the test row and refresh back to the original controls.

This is an application test to perform in Excel; the generated workbook does not contain a preverified PivotTable. The lab verifies data arithmetic and structure, while your target Excel version supplies the actual PivotTable behaviour.

Exercise: switch the status filter to include Pending and explain the total of 55,500. Then include February and identify the Unmapped category instead of allowing it to disappear from the report.

NeuraPath's Data Analytics with Generative AI course connects spreadsheet dashboards with analytical quality. A reviewable dashboard includes reconciliation evidence and explicit metric definitions alongside its visuals.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Excel SUMIFS with dates and inconsistent category labels.
• Continue with Power Query types: protect identifiers with leading zeros.