Select the PivotTable, go to the Analyze tab, choose Change Data Source in the Data group, and enter the new table or range. If you only need the latest data, select Refresh on the PivotTable Analyze tab or press Alt+F5. Both routes are below, with the one case where the source can't be changed.
The limit to know first: the Workbook Data Model
Before you try to change anything, check how the PivotTable was built. If it's based on the Workbook Data Model, you can't change its data source. Microsoft also suggests creating a new PivotTable if the columns in your source change substantially. That's from its page on how to change the source data for a PivotTable, and it saves a lot of head-scratching when the Change Data Source button seems to do nothing.
Change the source data: the steps
Use this when the PivotTable is pointing at the wrong range, or when your data has grown and you want the extra rows included.
- Select the PivotTable report.
- On the Analyze tab, in the Data group, select Change Data Source, then select Change Data Source again.
- In the dialog box, click Select a table or range.
- Enter the first cell of the new range in the Table/Range text box.
If you'd rather point the PivotTable at a different connection altogether, select Use an external data source instead, then choose Choose Connection.
Refresh instead: when the source is right but the numbers are old
Stale numbers need a refresh, not a new source. Microsoft sets out the options in its guide to how to refresh PivotTable data.
- Select the PivotTable.
- On the PivotTable Analyze tab, choose Refresh in the Data group.
- Shortcut: press Alt+F5, or right-click and choose Refresh.
| What you want | What to do |
|---|---|
| Refresh this PivotTable | Select it, then Refresh on the PivotTable Analyze tab, or press Alt+F5 |
| Refresh every PivotTable in the workbook | Select the Refresh arrow and choose Refresh All |
| Stop a long refresh | Select the Refresh arrow and choose Cancel Refresh |
| Refresh whenever the file opens | Select Options, then on the Data tab check Refresh data when opening the file |
Which one should you use?
Think of it as two separate questions. Where does the PivotTable look for its data, and has that data moved on since you last looked? The first is a source job and the second is a refresh job, and mixing them up is the most common reason people feel an edit hasn't worked.
The quickest test is to ask what changed. If rows were added to your data and the PivotTable doesn't show them, the range is probably wrong, so change the source. If the range is right and someone simply updated the numbers underneath, refresh.
It also helps to be deliberate about the order. Fix the source first if you need to, then refresh, then check the figures. Doing it the other way round just refreshes the wrong data.
Troubleshooting: when edits don't behave
Change Data Source won't let me change anything
Look at how the report was built. When it sits on the Data Model, the source stays locked, and starting afresh is the way round it.
My column widths and formatting keep resetting on refresh
Under Options, go to Layout & Format and check Autofit column widths on update and Preserve cell formatting on update. Those two settings are the ones Microsoft names for keeping your layout through a refresh.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
The refresh is taking ages
Use the little arrow beside Refresh and pick Cancel Refresh. That halts it, and you can inspect the source before trying again.
If you're still setting things up, our guide to making a pivot table in Excel starts from the beginning, and there's a version for Google Sheets too.
Frequently asked questions
What's the difference between changing the source and refreshing?
Changing the source tells the PivotTable to look at a different table or range. Refreshing keeps the same source and pulls in its latest data.
What's the shortcut to refresh a PivotTable?
Press Alt+F5 with the PivotTable selected. You can also right-click the PivotTable and select Refresh.
How do I update every PivotTable in the workbook at once?
Select the Refresh arrow and choose Refresh All.
Can I make a PivotTable refresh itself when I open the file?
Yes. Select Options, then on the Data tab check Refresh data when opening the file.
Why is my data source stuck?
That usually means the report is built on the Workbook Data Model, where the source is fixed. A brand new report is the workaround Microsoft suggests.
Do I have to change the source every time I add rows?
Only when the range doesn't cover the new rows. You enter the first cell of the range in the dialog. If the range already covers them, a refresh is all you need.
