Excel PivotTable Grand Total Does Not Match Source Data: Fix

You built a PivotTable to summarize your data, but the Grand Total row shows a different number than the total you calculate directly in the source range. This mismatch can happen even when your source data appears clean and consistent. The root cause is almost always duplicate entries, hidden data, or calculation settings inside the PivotTable itself. This article explains why the Grand Total can be wrong and provides the exact steps to make it match your source data.

Why the PivotTable Grand Total Differs From the Source Data

A PivotTable Grand Total is the sum of all visible values in the Values area after all filters, grouping, and calculations have been applied. The source data total is the raw sum of the original range. When these two numbers differ, one of these conditions is usually present:

Duplicate Rows in the Source Data

If your source data contains identical rows, the PivotTable sums each row individually. The Grand Total then reflects the total of all rows, not the distinct values. For example, if an invoice appears twice, the Grand Total includes that invoice twice, while your manual SUM on the source also counts it twice. In this case the totals actually match, but the mismatch appears when you expect a distinct count.

Hidden or Filtered Rows in the Source

If you applied a filter or manually hid rows in the source worksheet, the PivotTable still reads all rows in the defined source range. The Grand Total includes hidden rows. If you sum only visible cells in the source, that sum will be smaller than the PivotTable Grand Total.

Custom Calculations in the Value Field

When you change a value field to show % of Column Total, Running Total, or Difference From, the Grand Total no longer shows a simple sum. Excel calculates the Grand Total using the same custom calculation, which rarely matches the raw source total. This is the most common cause of a mismatch that confuses users.

Incorrect Source Range

If you added rows or columns to the source data after creating the PivotTable, those new entries are not included unless you update the source range. The Grand Total then excludes those new values, causing a mismatch with the current source range.

Blank Cells or Text in Numeric Columns

If the source column contains blank cells or text entries, the PivotTable ignores them in the sum. The Grand Total will be lower than a manual SUM that treats blanks as zero. Also, if the source column contains numbers stored as text, the PivotTable may exclude those rows entirely.

Steps to Make the Grand Total Match the Source Data

1. Check the Value Field Calculation
   Right-click any value cell in the PivotTable. Select Value Field Settings. On the Show Values As tab, confirm the setting is No Calculation. If it is anything else, change it to No Calculation and click OK. The Grand Total now shows a standard sum.
2. Verify the Source Range
   Click anywhere inside the PivotTable. Go to PivotTable Analyze > Change Data Source. In the Table/Range box, verify the range includes all rows and columns of your source data. If new rows were added, update the range or convert the source to a Table using Ctrl+T. Click OK to refresh.
3. Remove Duplicate Rows From the Source
   Select the entire source data range. Go to Data > Remove Duplicates. In the dialog, choose the columns that define a duplicate row. Click OK. Excel removes duplicate rows and displays a count of removed items. Refresh the PivotTable by right-clicking it and selecting Refresh.
4. Check for Filtered or Hidden Rows in the Source
   Select the source data range. Press Alt+; (semicolon) to select only visible cells. Look at the status bar sum. If that sum differs from the PivotTable Grand Total, clear all filters in the source by going to Data > Clear. Unhide any hidden rows by selecting the entire sheet and going to Home > Format > Hide & Unhide > Unhide Rows.
5. Convert Text to Numbers in the Source
   Select the numeric column in the source data. Look for a yellow warning icon in the top-left corner of any cell. If present, click the icon and select Convert to Number. Alternatively, use the formula =VALUE(cell) in a helper column and copy it down. Refresh the PivotTable.
6. Turn Off the Grand Total and Re-Enable It
   Right-click the PivotTable and select PivotTable Options. Go to the Totals & Filters tab. Uncheck Show grand totals for rows and Show grand totals for columns. Click OK. Open the same dialog again and re-check both options. This forces Excel to recalculate the Grand Total from scratch.

When the Grand Total Still Does Not Match

The Grand Total Shows a Running Total Instead of a Sum

If you changed the value field to Running Total, the Grand Total shows the last running total value, not the sum of all items. To fix this, right-click a value cell, go to Value Field Settings, and on the Show Values As tab, select No Calculation. Then click OK.

The Source Data Contains Subtotals or Pre-Calculated Totals

If your source data includes rows that are themselves subtotals of other rows, the PivotTable double-counts those values. Remove any subtotal rows from the source range. Use Data > Subtotal to clear all subtotals, or delete the rows manually. Then refresh the PivotTable.

The Grand Total Includes Items That Are Filtered Out

If you apply a Row Label filter, the Grand Total still sums all items that pass the filter. To exclude filtered items from the Grand Total, right-click the Grand Total cell and select Remove Grand Total. Then add a new Grand Total using PivotTable Analyze > Field Settings > Layout & Print > Show item labels in tabular form. This forces the Grand Total to respect the filter.

The PivotTable Uses a Data Model Instead of the Sheet Range

If you checked Add this data to the Data Model when creating the PivotTable, the Grand Total uses the data model’s calculation engine. This engine can produce different results due to distinct count logic. To fix this, create a new PivotTable without the Data Model. Uncheck the Data Model option in the Create PivotTable dialog.

Quick Repair vs Online Repair: Key Differences

Item	Quick Repair	Online Repair
Description	Repairs Office installation without downloading files	Downloads and reinstalls all Office components
When to use	Minor PivotTable calculation glitches	Persistent PivotTable errors after all other fixes
Time required	5-10 minutes	30-60 minutes
Internet required	No	Yes
Effect on add-ins	Preserves all add-ins	May reset some add-ins

If none of the PivotTable-specific fixes resolve the mismatch, run an Office repair. Go to Control Panel > Programs > Programs and Features. Select Microsoft 365 and click Change. Choose Quick Repair first. If the problem remains, run Online Repair.

You now have a clear process to identify why the PivotTable Grand Total does not match your source data and the exact steps to correct it. Start by checking the value field calculation and the source range, as these two settings cause most mismatches. For a more robust workflow, convert your source data to a Table using Ctrl+T so the PivotTable automatically expands when you add new rows.