Fix the Excel PivotTable Automatic Subtotals Error
Question details
The user needs to refresh an Excel PivotTable without an automatic subtotals error.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Refreshing a workbook that contains a PivotTable.
- Observed behavior
- Excel reports that it cannot create a PivotTable because the source data contains automatic subtotals.
Identify the affected PivotTable and its source data. If the source uses an external connection, check where that data is generated before editing the worksheet.
Remove Automatic Subtotals from the Source Data
Use this method when the PivotTable source includes subtotals created with Excel's Subtotal command.
Select the PivotTable, then open PivotTable Analyze > Change Data Source to inspect its source range, if available.
Select a cell in the source data, go to Data > Subtotal, and choose Remove All. This removes the automatic subtotal rows and outline created by the Subtotal command.
Inspect the source range for any remaining manually entered subtotal or grand total rows. Remove them or exclude them from the source, keeping one header row and one record per row.
Right-click the PivotTable and select Refresh. If needed, select PivotTable Analyze > Change Data Source and update the range before refreshing again.

Test a Clean Source Range in a Copy of the Workbook
Use a cleaned copy to isolate the issue if removing automatic subtotals does not resolve the error.
Work with PivotTables in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to review data and work with PivotTables, making it easier to move between office suites. It does not directly fix an Excel-specific refresh error.

Frequently Asked Questions
How do I find the source data for my PivotTable?
Select the PivotTable and choose PivotTable Analyze > Change Data Source. For a PivotTable using an external connection, inspect the connection or query that supplies its data.
Will Remove All delete my original source records?
Data > Subtotal > Remove All removes automatic subtotal rows and the outline created by the Subtotal command; it does not remove the underlying records. Manually entered totals must be handled separately.
What if the subtotal rows return after refresh?
The rows may be coming from a query or external data source. Edit the query or source report to exclude the subtotals, then refresh the data and PivotTable.
Can I keep totals in my PivotTable?
Yes. Remove subtotal and grand total rows from the source data, then use the PivotTable's own subtotal and grand total settings to display calculated totals in the report.




