Pivot Table Issues

Fix the Excel PivotTable Automatic Subtotals Error

Maira MehtabMaira Mehtab Nov 22, 2026 868 views

Question details

The user needs to refresh an Excel PivotTable without an automatic subtotals error.

Fix the Excel PivotTable 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.
Before you start

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.

Solution 1Recommended

Remove Automatic Subtotals from the Source Data

Use this method when the PivotTable source includes subtotals created with Excel's Subtotal command.

1
Find the source range

Select the PivotTable, then open PivotTable Analyze > Change Data Source to inspect its source range, if available.

2
Remove automatic subtotals

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.

3
Check the data layout

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.

4
Refresh the PivotTable

Right-click the PivotTable and select Refresh. If needed, select PivotTable Analyze > Change Data Source and update the range before refreshing again.

Remove Automatic Subtotals from the Source Data
External data sources: If a query or external system supplies the subtotal rows, remove them in that source or query; editing worksheet output alone may not persist after refresh.
Free Microsoft Office alternative

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.

Review spreadsheet data and create PivotTables in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a familiar interface that makes switching from Excel easierGet a free, lightweight office suite
microsoft office alternative - wps office

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.