Excel PivotTable Missing New Rows? Fix the Source and Refresh

Refresh does not fix a source range that stops above your new records. Check the PivotTable’s source first. If it is a fixed address such as Sales!$A$1:$C$9, rows 10 and 11 are outside that source even after refreshing. Convert the full source to an Excel table, point the PivotTable at the table name, and refresh again.

There are two separate checks: does the source include the records, and has the PivotTable refreshed from that source? Microsoft’s guidance recommends Excel tables because added table rows are included when a PivotTable refreshes. Create a PivotTable.

Start with eight records and a known total

Download the feature-practice sales CSV. This synthetic file has the original eight records used below. Import or paste them with a single header row at A1:C9, then name the sheet Sales. Amount must contain numbers, not numeric-looking text. Use a comma delimiter when importing the CSV so each field lands in its own column.

Region,Channel,Amount
North,Online,120
North,Retail,80
South,Online,90
South,Retail,140
North,Online,60
South,Online,30
North,Retail,40
South,Retail,20

Before building the PivotTable, =SUM(C2:C9) should give 580, and =COUNT(C2:C9) should give 8. See SUM and COUNT if the numeric check fails. A total alone can conceal a text amount that SUM has ignored.

Build the original summary

1. Select A1:C9 and choose Insert > PivotTable. Verify the source address, and put the PivotTable on a new worksheet. This example uses an ordinary worksheet range, not the Data Model or an external connection.
2. Place Region in Rows, Channel in Columns, and Amount in Values.
3. Open the Amount value field’s settings and select Sum if it has defaulted to Count. Leave Show Values As at its normal calculation rather than a percentage.
4. Compare every subtotal below, not only the grand total.

Microsoft documents the Rows/Columns/Values layout and warns that text in a value field can cause Count rather than Sum. PivotTable creation and value settings.

Reproduce the missing-record problem

Back on Sales, add these two records directly below the original data, in A10:C11:

The new worksheet total is 700: 580 + 70 + 50. Right-click inside the PivotTable and choose Refresh. If its source is still A1:C9, the expected PivotTable total remains 580. Repeating Refresh does not extend that fixed address.

Select a cell in the PivotTable and use PivotTable Tools > Analyze > Change Data Source in Excel 2013 for Windows. Inspect the Table/Range field before editing it. Modern Windows Excel commonly labels the contextual tab PivotTable Analyze. Change the source data.

If your existing source already names a table, inspect that table’s boundary instead. New rows can sit immediately beneath a table without having become part of it, especially after unusual paste operations or disabled expansion settings.

Replace the fixed range with an expanding table

1. On Sales, select A1:C11, including both new records. Choose Insert > Table or press Ctrl+T, and confirm My table has headers. Do not include a manually typed grand-total row.
2. With a table cell selected, use the contextual Table Tools > Design tab in Excel 2013 and set the Table Name to SalesData. In modern Windows Excel this is generally Table Design. Use that name exactly in the formulas below.
3. Return to the PivotTable and open Change Data Source. Replace the fixed address with SalesData, confirm, and refresh the PivotTable.
4. Reopen the source dialog to verify that it now refers to the table, rather than simply a longer fixed range.

The table-creation mechanics are documented in Create and format tables; changing the PivotTable source is a separate operation. Merely styling the source cells with alternating colors does not create an Excel table.

The refreshed result should be:

Useful independent controls are =SUM(SalesData[Amount]), expected 700, and =ROWS(SalesData[Amount]), expected 10. A targeted SUMIFS check also helps locate a missing category:

=SUMIFS(SalesData[Amount],SalesData[Region],"North",SalesData[Channel],"Online")

Expected result: 250. ROWS counts the table’s data rows; it does not prove Amount is numeric, so retain the COUNT check when cleaning imported data.

Test the next append, not just today’s repair

Add North, Retail, 25 as one more table row. Enter it in the first row directly below the table and confirm that the table expands. If it does not, use Table Tools > Design > Resize Table in the legacy ribbon and include the full A1:C12 range. Modern help calls this Table Design > Resize Table. Resize a table.

Refresh again. The expected results are 11 source records, North/Retail 145, North total 395, South total 330, and grand total 725. This second append demonstrates that the source design continues to work. Save the working file as .xlsx; CSV cannot store the table or PivotTable.

Other reasons records appear to be missing

• The table contains the record, but the pivot excludes its category: inspect report filters, row/column filters, and slicers. On a working copy, clear the relevant filter and compare the result. A filter in the source worksheet is not a substitute for reviewing the pivot’s filters.
• A category appears twice: compare spelling and spaces in the source. North and North should not be assumed to be the same clean label. Correct the source deliberately, then refresh.
• Count of Amount appears: inspect text values and blanks before simply switching aggregation. Changing Count to Sum cannot recover a number that is still stored as text.
• Refresh reports that cells would be overwritten: leave room around the PivotTable or move it to a clear worksheet. Do not delete unrelated content just to make it fit.
• Change Data Source is unavailable or behaves differently: check whether this is a Data Model, OLAP, or external-source pivot. This local worksheet-range repair does not replace connection or model maintenance.
• A connection refresh is still running: wait for the relevant source update to finish before judging the PivotTable result. Source inclusion, source retrieval, and pivot refresh are different stages.

Excel 2013 versus current Microsoft 365

The procedure targets a standard worksheet PivotTable in Excel 2013 for Windows. Use explicit Refresh after edits. Microsoft’s current help describes newer automatic-refresh functionality and identifies an Insider availability limitation; do not assume those controls exist in Excel 2013 or every Microsoft 365 build. Right-click > Refresh is the simple common action. Refresh PivotTable data.

Current Microsoft Support pages generally list newer supported editions. For the legacy labels, Microsoft’s PivotTable compatibility guidance retains the PivotTable Tools > Analyze > Change Data Source labels. Its Excel 2013 data tutorial documents Ctrl+T and Table Tools > Design > Table Name. Only those table-creation steps are relevant here; no Power View or Data Model setup is required. These references and any Microsoft 365 screenshot are not native Excel 2013 test evidence.

Not tested in Excel. Official documentation was reviewed October 16, 2026. The eight-record benchmark and both appended-record scenarios were independently calculated outside Excel. No PivotTable cache, table expansion, native refresh, or application screenshot was produced for this guide.