Compare & combine files · 5 min read

PivotTable Missing New Rows After Refresh? Check Its Source Range

You append this month’s records, click Refresh, and the PivotTable still shows last month’s total. The missing rows may sit outside its source, even though they look like part of the same worksheet list.

JHSOFT editorialUpdated September 27, 2026

WORKED EXAMPLESynthetic data

order_id,region,amount
O101,East,40
O102,West,60
O103,East,20
O104,West,30
O105,East,50
O106,West,10

The initial four orders total 150: East 60 and West 90. After including O105 and O106, six orders total 210: East 110 and West 100. These totals assume all PivotTable filters are cleared and amount uses Sum.

Download pivot-orders-initial.csv ↓Download pivot-orders-expanded.csv ↓

Reproduce the missing-row problem

Use a fresh desktop Excel workbook for this example. Enter the initial file’s header and four records in A1:C5. Keep order_id and region as text and amount as numeric values. Name the worksheet OrdersData. Select A1:C5, choose Insert > PivotTable, and place the report on a new worksheet.

Put region in Rows and amount in Values. Confirm the value field says Sum of amount rather than Count of amount. With no filters, East is 60 and West is 90. Now append the expanded file’s two extra records at A6:C7. Do not paste its header a second time. The worksheet total has changed, but a source still ending at row 5 cannot read those additions. A desktop Excel 16.0 build 5569 reproduction on September 19, 2026 kept the total at 150 after refreshing that fixed source, then returned 210 after the source was expanded through row 7.

The old source includes four orders and excludes the two appended orders worth 60. — Illustrated example. Synthetic source-boundary example. Both ranges include one header row.

Repair the fixed range once

Click inside the existing PivotTable. On PivotTable Analyze, choose Change Data Source. Some versions label the contextual tab Analyze. Inspect Table/Range instead of assuming the highlighted source reaches the last record.

For this example, replace OrdersData!$A$1:$C$5 with OrdersData!$A$1:$C$7. Include the header once and every data row, without unrelated notes or a worksheet grand total.
Confirm the change, then right-click inside the PivotTable and choose Refresh.
Clear PivotTable field filters, slicers, and timelines while checking the example. A filter can hide a newly added record even when the source includes it.
Verify East 110, West 100, and Grand Total 210. If the total is 6, the value field is probably counting records instead of summing numeric amounts.

Use an Excel Table for future additions

A Table gives the source a name and a boundary that can grow. Converting the worksheet list is only half the repair: explicitly check that the existing PivotTable now refers to the Table name rather than its old address.

On OrdersData, select A1:C7 and choose Insert > Table. Confirm My table has headers. Under Table Design, set the Table Name to Orders.
Return to the PivotTable and open Change Data Source. Set Table/Range to Orders, confirm, and refresh.
Append subsequent records immediately below the last Table row, or insert Table rows. Check that they are inside the Table; matching colors alone are not proof.
If a paste falls outside the Table, click within it and use Table Design > Resize Table to include the intended rectangle. Then refresh the PivotTable.

Separate source coverage from refresh timing

A Table’s growth and a PivotTable’s refresh are separate events. Keep an explicit Refresh step in the reporting checklist, especially when another person opens the workbook in a different Excel version.

As checked on September 19, 2026, Microsoft’s support page labels PivotTable Auto Refresh as available to Microsoft 365 Insider participants. Do not assume every installation updates immediately after an edit. The older refresh-on-open option also differs from refreshing whenever source cells change. None of these settings fixes a source that excludes the new rows.

Situation	Check	Action
Fixed address ends before new rows	Table/Range ending row	Expand the range or use a Table
Table source; new rows outside it	Actual Table boundary	Resize the Table, then refresh
Correct source; total still old	Refresh and report filters	Refresh, clear filters, compare totals
Power Query or Data Model source	Connection and query output	Refresh upstream data; inspect that source
Counts instead of monetary totals	Value field aggregation and types	Use numeric amount with Sum

Reconcile an independent count and total

Check the input separately from the report. For the six-row Orders Table, the formulas below should return 6, 6, and 210. If COUNT is smaller than ROWS, at least one amount is blank or nonnumeric; identify it before trusting a sum.

Also inspect both appended IDs. A grand total can accidentally remain correct if one record is duplicated and another is omitted. The regional subtotals give a second check, and the source IDs give a third. Keep these checks beside a recurring report so the next refresh can be reviewed quickly.

Independent checks for the Orders Table

=ROWS(Orders[order_id])
=COUNT(Orders[amount])
=SUM(Orders[amount])

Adding an East order of 50 and a West order of 10 changes the total from 150 to 210. — Illustrated example. Synthetic sums for the two downloadable order files.

Recognize when the source is an external query

This walkthrough targets a PivotTable built from a worksheet range or Table in the same workbook. If Change Data Source shows a connection or Data Model, inspect the query and loaded result instead. Changing an unrelated worksheet range will not repair that connection.

For query-backed reports, wait for the upstream refresh to complete, confirm that its output contains the new IDs, and then refresh the report. Preserve the existing workbook before rebuilding anything; a replacement PivotTable can require recreating its fields, filters, and dependent report references.

Tested source-range boundary

In desktop Excel 16.0 build 5569 on September 19, 2026, refreshing the PivotTable left the total at 150 while two appended records were outside the fixed A1:C5 source. After expanding the source through row 7, the PivotTable total became 210.

This is one synthetic workbook and one Excel build, not a test of every interface or data model. For your workbook, first inspect the actual PivotTable source and record counts; then refresh after changing the source to include the new rows.

Common questions

Why does Refresh All still miss new rows?

A fixed source range can exclude them, or the report can filter them out. Inspect the source’s final row and clear report filters before concluding that refresh failed. For an external query, first confirm its refreshed output contains the new records.

Does formatting the source as a Table fix my existing PivotTable automatically?

Do not rely on that alone. Open Change Data Source and confirm the PivotTable uses the Table name. Then check the Table includes the appended records and refresh the report.

Should I use an entire column as the PivotTable source?

A defined Table is usually easier to audit. An entire-column source can include unrelated entries and many blanks. For this example, Orders contains only the three intended columns, one header, and six records.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →