How to Fix Incorrect PivotTable Average Values in Excel
Question details
The user is experiencing incorrect overall average calculations in their PivotTables, despite the data appearing visually grouped and correct.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating the overall average of numerical fields using two connected PivotTables and a slicer.
- Observed behavior
- The PivotTable displays an incorrect overall average because duplicate records in the source data are secretly included in the underlying mathematical calculation.
Before modifying your data structure or deleting rows, it is highly recommended to save a copy of your current spreadsheet to ensure you do not lose any raw data while removing duplicates.
Remove Duplicate Records from Source Data
Clean your source data by identifying and removing duplicate rows to ensure your PivotTable averages are calculated correctly.
PivotTables calculate averages based on every row present in the source dataset. Even if duplicates are grouped together and appear as a single item visually in the PivotTable, they are all included in the underlying mathematical count, which ultimately skews the overall average.
Navigate to your source data worksheet and highlight the entire dataset you are using for the PivotTable.
Go to the 'Data' tab on the top ribbon and click on the 'Remove Duplicates' button.
In the dialog box, select the specific columns that define a true duplicate record and click 'OK' to delete them.
Return to your PivotTable, right-click anywhere inside it, and select 'Refresh' from the context menu to update the average calculations.

Normalize Data for Intentional Duplicates
Apply this method if the duplicate records in your source database are intentional (e.g., recurring sales transactions) and cannot be deleted.
Calculate Accurate PivotTable Averages with WPS Spreadsheet
WPS Spreadsheet offers a robust set of data cleaning tools, including an intuitive Remove Duplicates feature and powerful PivotTable capabilities, ensuring your data analysis is always accurate.
- 1. Open Your File: Launch WPS Spreadsheet and open the workbook containing your PivotTable and source data.
- 2. Clean Data: Highlight your source data, navigate to the 'Data' tab, and click 'Remove Duplicates'.
- 3. Update Calculation: Right-click on your PivotTable and select 'Refresh' to immediately reflect the accurate average.

Frequently Asked Questions
Why does my PivotTable show the wrong average but the correct sum?
While a sum simply adds all values together, an average divides the sum by the count of underlying records. If hidden duplicate rows exist in your source data, the count increases, which artificially lowers or skews the calculated average.
How can I easily find duplicates before removing them?
You can highlight duplicate values by selecting your source data, navigating to the 'Home' tab, clicking 'Conditional Formatting', choosing 'Highlight Cells Rules', and then selecting 'Duplicate Values'.
Does refreshing a PivotTable automatically remove duplicates?
No, refreshing a PivotTable only updates the table to reflect the current state of your source data. You must manually remove or consolidate the duplicates in your source database before clicking refresh.




