How to Change Pivot Table Data Source in Excel

Your painstakingly crafted PivotTable is finally done, but suddenly the underlying data has changed. New sales numbers for the quarter have been added, the data has moved to a new sheet, or you need to point it to an entirely different file. This article will show you exactly how to change your PivotTable's data source in Excel so you don’t have to rebuild your report from scratch.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why You'll Need to Change a PivotTable Data Source

PivotTables are incredibly powerful, but they are only as good as the data they're connected to. As your business data evolves, your PivotTable's source will inevitably need to be updated. It’s a common task every Excel user faces.

You’ll typically need to do this when:

• More data has been added: The most frequent reason. You've added new rows (like weekly sales figures) or new columns (like a new product category) to your dataset. Your original PivotTable doesn't automatically know about this new data.
• Your data's location has changed: Perhaps you copied the source data to a new worksheet to clean it up, or you're now working with a file someone else sent you.
• You're swapping datasets: You might want to point a beautifully formatted PivotTable report to a new month's data that follows the exact same structure as the previous month. Instead of rebuilding, you can just swap the source.
• The file was moved or renamed: If your PivotTable is linked to an external data source, changing the file's name or location will break the connection, requiring you to update it.

Knowing how to handle these situations efficiently can save you an enormous amount of time and frustration. Let’s look at the best ways to do it.

Method 1: Using the "Change Data Source" Feature

This is the most direct method for pointing your PivotTable to a new range of data. It's perfect for one-off adjustments or when you need to quickly redefine the analysis area.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide

Let's say you have a dataset of sales from January to March, and you've just added the data for April. Here's how to include the new month in your existing PivotTable.

1. Select your PivotTable: Click anywhere inside your existing PivotTable. This is the crucial first step. If you don't do this, you won't see the specific PivotTable contextual tabs appear in Excel's ribbon.
2. Navigate to PivotTable Analyze: Once your PivotTable is selected, two new tabs will appear at the top of Excel: "PivotTable Analyze" and "Design". Click on the PivotTable Analyze tab. Note: In older versions of Excel, this tab might be named "Analyze" or "Options".
3. Click "Change Data Source": In the "Data" group within the PivotTable Analyze tab, you'll see a button labeled Change Data Source. Click it.
4. Update the Data Range: A dialog box named "Change PivotTable Data Source" will appear, showing the current data range (e.g., 'Sheet1'!$A$1:$E$100). Now, you have two options:
5. Confirm the Change: After you've defined the new range, click OK.
6. Refresh Your PivotTable: While Excel often does this automatically after changing the source, it’s good practice to do a manual refresh to be sure. With your PivotTable selected, right-click on it and choose Refresh or go to the PivotTable Analyze tab and click the Refresh button.

Your PivotTable will now update to reflect all the new data you've included. Any fields based on the new data will now be available in your PivotTable Fields list.

Method 2: Dynamically Updating with Excel Tables (The Best Practice)

While the "Change Data Source" button works perfectly fine, it's a manual process you have to repeat every time new data is added. A much smarter and more automated approach is to convert your source data into an official Excel Table first. This is a game-changer.

When you base a PivotTable on an Excel Table, the PivotTable automatically knows when you add new rows or columns. You won't have to change the data source manually ever again - you'll only need to refresh it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Convert Your Source Data into an Excel Table

If your source data is just a plain range of cells, here’s how to upgrade it:

1. Select your data: Click any single cell within your data range.
2. Insert a Table: Go to the Insert tab in the ribbon and click Table. A "Create Table" dialog box will appear.
3. Confirm your range: Excel will automatically guess your data range. Make sure the selection is correct and — this is important — that the My table has headers box is checked if your data has column titles.
4. Click OK: Your data is now formatted as an official Excel Table, complete with alternating row colors and filter dropdowns.
5. (Optional but Recommended) Name Your Table: Click anywhere inside your new table, and a "Table Design" tab will appear in the ribbon. On the far left, you’ll see a "Table Name" box. Change the generic name (like "Table1") to something descriptive, such as "SalesData" or "MarketingSpend" and press Enter. This makes it much easier to reference later.

Step 2: Connect Your PivotTable to the Excel Table

Now that your data is in a properly named table, you can connect your PivotTable to it.

1. Click on your existing PivotTable to activate it.
2. Go to the PivotTable Analyze tab and click Change Data Source.
3. In the Range box, delete the old cell range (e.g., 'Sheet1'!$A$1:$E$150).
4. Type in the name you gave your table in the previous step (e.g., SalesData).
5. Click OK.

Your PivotTable is now dynamically linked to the Excel Table.

The Magic of Automatic Updates

So, what was the point of all that? From now on, whenever you add a new row of data directly below your table or a new column next to it, the table will automatically expand to include it.

The next time you've added new data, you don’t have to use "Change Data Source" at all. Simply right-click on your PivotTable and click Refresh. Every new piece of data will be instantly included. This technique is more efficient, less error-prone, and is considered the professional standard for working with datasets in Excel that are updated frequently.

Troubleshooting Common PivotTable Source Issues

Sometimes, things go wrong. Don’t panic - these issues are very common and usually simple to fix.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Issue 1: I refreshed my PivotTable, but my new data isn't showing up.

• Most Likely Cause: Your new data was added outside of the PivotTable's defined range, and a dynamic Excel Table was not used. Excel doesn’t know the data is there.
• The Fix: Use Method 1 above ("Change Data Source") to manually expand the range to include the new rows/columns. Even better, take this opportunity to convert your data range into an Excel Table so you don't face this problem again.

Issue 2: Excel shows a "(blank)" item in my filters.

• Most Likely Cause: Your data source range includes empty rows or columns at the end. Your PivotTable is treating these empty cells as a data category named "(blank)".
• The Fix: Go back to Change Data Source and adjust your range to be more precise, including only the cells that contain actual data. Again, using an Excel Table often prevents this, as it typically manages its own boundaries more intelligently. Another common cause is your dataset having rows where some key fields are empty, find them and either delete the row or fill in the missing key information.

Issue 3: I get a "Reference is not valid" error message.

• Most Likely Cause: This error often appears if the source worksheet or the entire source file has been deleted, moved, or renamed. The PivotTable is trying to find data at a location that no longer exists.
• The Fix: You'll have to use the Change Data Source button to re-establish the connection. If the data is in an external file, you may need to browse to find that file on your computer and re-link the proper sheet and range.

Final Thoughts

Updating your PivotTable's data source is a fundamental Excel skill. While the "Change Data Source" button offers a quick fix for redefining a data range, converting your data to an official Excel Table provides a far more robust and automated solution for long-term reporting.

We know that manually keeping track of source data, refreshing reports, and building PivotTables drains hours every week, especially when that data is scattered across tools like Google Analytics, Shopify, and your CRM. We created Graphed because we believe your time is better spent on strategy, not on data wrangling. You can connect your marketing and sales platforms in seconds, then simply describe the dashboard you want in plain English, and our tool builds it for you in real time, completely eliminating the busy work of managing and refreshing reports.