Try the example, then check the result.
Download the fictional material and work through the exact steps below. App screens in the video are English illustrative diagrams, not a recording of the product interface. Controls can vary by version.
Use the answer check before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
Synthetic narration · English illustrative diagrams · English captions. Product menus may differ in your account.
Target result: the fictional sales PivotTable includes six orders and shows a $400 grand total after its source is changed from a fixed four-order range to an expanding Excel Table.
Download the practice workbook and plain-text answer check. The workbook has Sales, Rows to append, and Expected totals sheets. It deliberately does not contain a pre-built PivotTable; you create one to reproduce the problem. These instructions target Excel desktop. All orders are fictional. The video uses English illustrative diagrams, not application screen recordings or proof of a completed $400 PivotTable run. A separate localized desktop check confirmed the starting data and fixed source dialog only.
What the example contains
| Order | Region | AmountUSD | When added |
|---|---|---|---|
| S-101 | North | $120 | Initial |
| S-102 | South | $80 | Initial |
| S-103 | North | $60 | Initial |
| S-104 | East | $40 | Initial |
| S-105 | West | $70 | Later |
| S-106 | South | $30 | Later |
Swipe or scroll sideways to see every table column →
The first four orders total $300. The later two add $100, so all six total $400. The AmountUSD cells in the practice workbook are numbers, not text.
Reproduce the missing-row problem
- Open a copy of the workbook in Excel desktop. On
Sales, select A1:C5, including the header and four initial orders. - Choose Insert → PivotTable and create it on a new worksheet. Place
Regionin Rows andAmountUSDin Values. Confirm the value field says Sum of AmountUSD. The starting result should show North $180, South $80, East $40, and Grand Total $300. Microsoft's creation guide describes this flow and why numeric fields normally use Sum while text fields may use Count. - Copy the two data rows from
Rows to appendintoSales!A6:C7, immediately below the original four rows. The list now has six orders worth $400. - Return to the PivotTable and Refresh it. Because this practice PivotTable was deliberately created from
Sales!A1:C5, it may still show $300. Select it and inspect Analyze → Change Data Source. The two new rows are outside the original source boundary.
Refreshing reads the assigned source. If your PivotTable already points to an Excel Table or dynamic source, you may not reproduce this failure; use the initial fixed range exactly for the exercise.
Make the source grow with future rows
- On
Sales, select A1:C7, including all six orders. Choose Home → Format as Table and confirm My table has headers. Name the new Excel TableSalesTablefrom the Table Design tab. This is the source table, a different object from the PivotTable. - In Excel desktop, select the existing PivotTable. Choose Analyze → Change Data Source, enter
SalesTableas the table or range, and confirm. Some versions label the ribbon PivotTable Analyze. Microsoft's source-change guide documents changing an existing PivotTable to a different Excel Table or range. The web app can present different controls, so follow the labels in your installed edition. - Refresh the PivotTable. Compare its region amounts and grand total with the
Expected totalssheet. A table source includes newly added table rows when you refresh; Microsoft explains why Excel Tables work well as PivotTable sources.
Answer check
| Region | Before | After both rows |
|---|---|---|
| North | $180 | $180 |
| South | $80 | $110 |
| East | $40 | $40 |
| West | $0 | $70 |
| Grand total | $300 | $400 |
Swipe or scroll sideways to see every table column →
The complete source has six order rows. The increase is $70 West + $30 South = $100. If your total is $400 but South or West differs, inspect the pasted Region labels and confirm the PivotTable summarizes AmountUSD by Sum.
Common mistakes
- Only clicking Refresh: it cannot include rows beyond a fixed source range such as
Sales!A1:C5. - Converting only the old range to a Table: include the appended rows in
Sales!A1:C7before naming the source table. - Leaving the PivotTable pointed at the old range: the Table does not automatically replace an existing PivotTable's source. Use Change Data Source once.
- Seeing Count of AmountUSD: this can happen when Excel treats amount values as text. Verify the source data type and use Value Field Settings → Summarize Values By → Sum where appropriate.
- Assuming every Excel release auto-refreshes: Microsoft currently identifies automatic local-data PivotTable refresh as an Insider feature. Use an explicit Refresh and verify the result.
FAQ
Do I have to rebuild the PivotTable? No. Microsoft documents changing an existing PivotTable's source to another range or Excel Table. If the columns changed substantially, creating a new PivotTable may be easier.
Will a new row always enter SalesTable? Add it directly below the Table and verify the Table border expands. If it lands outside, resize the Table or append inside it before refreshing.
Why did my PivotTable update values but omit a new region? Existing cells inside the source may have changed, while the new row sits outside the source range. Inspect Change Data Source rather than assuming Refresh failed.
Official references
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.