Excel Guides

Why Your Excel PivotTable Still Misses New Rows After Refresh

Friday’s shipment rows are in the workbook. You hit Refresh and the dashboard remains at 284 shipments. The PivotTable is still reading the same fixed range, which ends a row before the first Friday entry.

Open Change Data Source and compare its last row with the export. If the report uses a fixed cell range, point it at an Excel Table that includes all the current rows and refresh again.

Find the break between the export and the PivotTable

This export has one shipment per row and headers in row 1.

CheckOld sourceComplete source
RangeA1:F285A1:F310
Records284309
Last shipmentS-1284S-1309

The source range missed 25 rows. The PivotTable wasn’t failing to refresh. It was refreshing the same 284 records.

Recreate the 25 missing shipments

In a blank Excel desktop workbook, name a sheet Shipments. Paste this tab-separated block into A1. It creates six headers and the first synthetic record:

ShipmentID	ShipDate	Carrier	Lane	Status	Units
="S-"&(ROW()+999)	=IF(ROW()<=285,DATE(2026,11,12),DATE(2026,11,13))	DemoCarrier	Local	Shipped	1

Select A2:F310 and use Home → Fill → Down to copy row 2 through row 310. Format B2:B310 as dates. Keep this layout unchanged during the exercise; the formulas use row numbers. English function names and comma separators are shown; your Excel locale may require semicolons.

Create a standard worksheet PivotTable with the source explicitly set to Shipments!$A$1:$F$285. Put Carrier in Rows and ShipmentID in Values, summarized by Count. Leave all filters and slicers clear. The expected count is 284, even though the sheet contains 309 shipment IDs. This example deliberately counts IDs, with one unique, nonblank ID per shipment.

Check A285: it should be S-1284. The first omitted ID is S-1285 in A286, and the last is S-1309 in A310. Their dates in B286:B310 are all Friday, November 13, 2026. The old range contains Thursday’s November 12 records only.

On this blank practice sheet, use =COUNTA(A2:A285) for the old-source control of 284 and =COUNTA(A2:A310) for the full-source control of 309. The omitted block =COUNTA(A286:A310) must equal 25. These controls assume the generated IDs have no blanks or duplicates.

Change the PivotTable source to Shipments!$A$1:$F$310 and refresh it. The expected count is now 309. For the reusable version, convert A1:F310 to an Excel Table named ShipmentLog, change the PivotTable source to ShipmentLog and refresh. Creating a table nearby does not by itself prove the existing PivotTable now uses it. Reopen Change Data Source and verify the table name.

Inspect what the PivotTable is actually connected to

Click inside the PivotTable and check its data source. For a fixed worksheet range, compare its final row to the report’s last stable ID. If it is connected to Power Query or the Data Model, inspect that query or connection instead.

Create a source that expands with the file

Select the full records and create an Excel Table with unique headers. Change the source to the table and append future rows inside it or directly below it, checking that the table expanded. After refreshing, look for slicers or filters that can still hide the new records.

Check a stale and a current snapshot together

Keep a copy of what the report actually included, plus the latest export. This makes it straightforward to distinguish missing records from a filter or date issue.

Which shipment IDs are present in the latest export but absent from the source used in the report? Show the first missing row, affected dates and source cells.

Record the 309-row check in the refresh notes. Next Friday, check the last shipment ID again before distributing the dashboard.

For an ordinary worksheet total beside a filtered list, use the separate visible-row SUBTOTAL exercise. It shows why a $1,200 visible subtotal can coexist with a $1,500 East branch balance.

Microsoft on PivotTable sources · Microsoft on refreshing PivotTables

The figures and identifiers in this example are synthetic.

The record counts, ID sequence and dates above were independently checked. Native Excel PivotTable creation and refresh were not performed during this check, and a live Jolene.pro result could not be verified. These are expected results for a synthetic exercise.

Microsoft documents how to change the PivotTable source and use an Excel Table as the source. If the rows are included but the report counts invoice amounts rather than adding them, use the separate Count versus Sum practice guide. Find more examples in Jolene.pro Workbook Studies.

Try this with your own spreadsheet at Jolene.pro. Follow Jolene.pro on LinkedIn for more spreadsheet guides.

3 comments

Leave a comment