Fix an Excel Table That Does Not Expand or Update a PivotTable
Question details
The user needs an Excel table to include newly entered rows consistently and update a related PivotTable with the new data.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- New records are added below an Excel table that supplies data to a PivotTable.
- Observed behavior
- Excel formats the first new row but later rows may remain outside the table, so the PivotTable does not include them after refresh.
Click inside the data range and confirm it is an Excel table by checking whether the Table Design tab appears. Also identify the PivotTable that should use this table as its data source.
Enable automatic expansion for new Excel table rows and columns
Use this when rows entered directly below a table are not automatically becoming part of the table.
Excel can automatically extend a table when you type immediately beside or below it. The AutoFormat As You Type setting must be enabled for this behavior to work consistently.
In Excel, select File > Options.
Select Proofing, then click AutoCorrect Options.
Open the AutoFormat As You Type tab and select Include new rows and columns in table.
Click the first empty row directly below the table and enter data in the adjacent table columns. Confirm that the table border and formatting extend to the new row.
Click anywhere in the PivotTable, then select PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.
Set the PivotTable source to the Excel table name instead of a fixed range
Use this when the table expands correctly but the PivotTable still excludes added records.
Update Table-Based PivotTables Easily with WPS Spreadsheets
WPS Spreadsheets lets you organize source data as a table, update the PivotTable source when needed, and refresh summaries after adding records. Its familiar spreadsheet interface helps you move from Excel workflows with minimal adjustment.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the .xlsx or .xls workbook containing the data and PivotTable.
- 2. Add records to the source data: Enter the new records in the rows directly below the existing source table, keeping the same column structure.
- 3. Verify the PivotTable source: Click the PivotTable and use the PivotTable tools to review or change its data source so it includes the complete source table or data range.
- 4. Refresh the PivotTable: Right-click inside the PivotTable and select Refresh to update its totals, fields, and records.
Frequently Asked Questions
Why does Excel format one new row but not include later rows in the table?
The automatic table expansion option may be disabled, or rows may be entered with a blank row or blank column separating them from the table. Enable File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type > Include new rows and columns in table, then enter data immediately adjacent to the table.
Does refreshing a PivotTable automatically expand its source range?
No. Refresh only reloads records within the current source. If the source is a fixed range, first use PivotTable Analyze > Change Data Source to include the added rows or use the Excel table name as the source.
How can I tell whether my PivotTable uses an Excel table as its source?
Click the PivotTable and select PivotTable Analyze > Change Data Source. The Table/Range box should show a table name, such as Table1, rather than a fixed reference such as $A$1:$F$100.
Can I manually expand an Excel table when automatic expansion does not work?
Yes. Click inside the table, open Table Design, select Resize Table, and extend the displayed range to include the new rows. Then refresh the related PivotTable.
