Pivot Table Issues

How to Fix Excel Pivot Table Showing Wrong Sum

Chanuka Geekiyanage Sep 27, 2026 869 views

Question details

The user needs to understand and resolve the issue of an Excel pivot table calculating a total that does not match a separate calculation from the same dataset.

How to Fix an Excel Pivot Table Showing the Wrong Sum

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing a pivot table sum to a separate, manual calculation of the same underlying dataset.
Observed behavior: The pivot table displays a different sum than the expected result, likely due to caching, formatting, or filtering issues.

Before you start

Ensure you have saved your workbook and that no other users are actively modifying the source data if you are working in a shared file.

Solution 1Recommended

Refresh the Pivot Table and Update the Source Range

The most common reason for a sum mismatch is an outdated pivot cache or new data being added outside the pivot table's original source range.

Pivot tables do not automatically update when you change the source data. Furthermore, if you added new rows of data at the bottom of your dataset, the pivot table might not be including those new rows in its calculation.

Refresh the data

Click anywhere inside your pivot table. Go to the PivotTable Analyze tab on the ribbon and click 'Refresh', or right-click a cell inside the pivot table and select 'Refresh'.

Check the data source range

On the PivotTable Analyze tab, click 'Change Data Source'. A dialog box will appear, and the source data will be highlighted. Verify that this highlighted range includes all your current raw data rows.

Refresh the Pivot Table and Update the Source Range

Use Excel Tables for Dynamic Ranges: Converting your source data to an Excel Table (by pressing Ctrl+T) ensures that any new rows are automatically included in the pivot table the next time you refresh it.

Solution 2

Correct Value Field Settings and Text Values

If the source column contains numbers formatted as text, the pivot table may default to counting the cells rather than summing them, or it might exclude them entirely.

Solution 3

Clear Hidden Filters and Check Calculated Fields

Active filters might be hiding data from your totals, or a custom calculated field may contain a logic error causing incorrect sums.

Smart Data Analysis Tool

Create and Manage Accurate Pivot Tables with WPS Office

WPS Office Spreadsheet provides a robust, easy-to-navigate environment for creating and refreshing pivot tables. It handles large datasets efficiently and ensures accurate data summarization without the hassle of unexpected caching errors.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the dataset.
2. Insert a Pivot Table: Select your data range, navigate to the Insert tab on the top ribbon, and click on 'PivotTable'.
3. Configure your fields: In the PivotTable Field List on the right, drag your fields into the Rows and Values areas. Ensure the Values field is configured to 'Sum'.
4. Refresh effortlessly: Right-click anywhere on the PivotTable and select 'Refresh' to instantly update your totals whenever the source data is modified.

Fully compatible with Microsoft Excel (.xlsx) formats and pivot caches.Intuitive PivotTable creation with automatic range detection.Lightweight software that processes complex calculations quickly.Free and easy to use across Windows, Mac, Linux, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Excel pivot table counting instead of summing?

This usually happens when the source column contains blank cells, text, or numbers formatted as text. Excel automatically defaults to 'Count' in these cases. To fix it, ensure all cells in the column contain valid numbers, then right-click the pivot table values, select 'Value Field Settings', and change it to 'Sum'.

Can hidden rows affect my pivot table sum?

If rows are simply hidden manually in the source worksheet, the pivot table will still include them in its calculations. However, if those rows are excluded via an active filter applied to the source table or the pivot table itself, they will not be summed.

How do I find out if my pivot table is using a calculated field?

Click anywhere in your pivot table, navigate to the PivotTable Analyze tab, click on 'Fields, Items & Sets', and select 'List Formulas'. Excel will generate a new worksheet detailing all the calculated fields and their respective formulas used in that pivot table.

Will a pivot table automatically update when I change the source data?

No, pivot tables do not update automatically in real-time. You must manually refresh them by right-clicking inside the pivot table and selecting 'Refresh', or by clicking 'Refresh All' on the Data tab.

Excel & Data Problems