Fix Data Source References Not Valid in Excel

If you see the error “Data source references are not valid” in Excel, it means Excel cannot find or read the data source behind a PivotTable, chart, or query. The fix is to update the source range, reconnect the file, or adjust the connection settings—here’s exactly how to do that.

What Causes This Error

This error usually appears when you refresh a PivotTable or an external data query. The most common causes are:

• The source range was deleted, renamed, or moved to another sheet.
• The source workbook or database file was moved, renamed, or is currently open with exclusive access.
• You’re using a dynamic named range that no longer exists or has a typo.
• The connection string points to an old server path or a file that has been replaced.
• The data is in a table that was converted back to a normal range, breaking the reference.

Excel stores the source reference as a static string. If anything about that path or range changes, the refresh fails and shows this message.

How to Fix a PivotTable Source Reference

If the error happens when you refresh a PivotTable, the quickest fix is to update the source range manually.

1. Select any cell in the PivotTable.
2. Go to PivotTable Analyze (or Options in older versions) > Change Data Source.
3. In the dialog, check the range shown. If it points to a missing sheet or wrong range, click in the field and select the correct cells on the worksheet.
4. Click OK, then refresh the PivotTable.

If the source is a named range, open Formulas > Name Manager and verify the range still exists and points to the right cells. A named range that references a deleted sheet will cause this error.

Fix for External Data Connections

When the error comes from a query (Power Query, Get & Transform, or a classic connection), the source file or database path is the problem.

1. Go to Data > Queries & Connections (or Connections in older versions).
2. Find the connection that failed and double-click it to open the properties.
3. In the Definition tab, check the connection string. Look for the file path or server name.
4. If the file moved, edit the path to the new location. If it’s a database, verify the server and database names are correct.
5. Click OK and try refreshing again.

For Power Query, open the Queries pane, right-click the query, and choose Advanced Editor. Check the source line—it will contain a file path or a web address. Update it to the current location.

Common Related Misconception: “The File Is Fine, So Why?”

A frequent misunderstanding is that the error means the data itself is corrupt. In most cases, the data is perfectly fine—Excel just can’t find the reference. This happens often when you email a workbook and the recipient opens it without the linked source file, or when you move the source file to a different folder. The error is about the reference, not the data quality.

How to Prevent the Error in the Future

You can reduce the chances of seeing this error by using best practices:

• Use Excel Tables (Insert > Table) for source data. Tables have structured references that automatically expand, and they are less prone to breaking than fixed ranges.
• Keep source files in a stable location—avoid moving or renaming them after setting up the connection.
• Use relative paths in connections when possible, especially if you share workbooks within a network folder.
• Refresh data before saving to ensure the connection is current.
• Check the Name Manager regularly for broken named ranges.

What to Do If the Fix Doesn’t Work

If updating the source range or connection still shows the error, try these steps in order:

1. Close and reopen the workbook—sometimes Excel holds a stale reference in memory.
2. Check if the source file is open—if it’s open in another instance of Excel, close it or make sure it’s not read-only.
3. Recreate the connection—delete the old connection and create a new one to the same source.
4. Use “Edit Links” (Data > Edit Links) to see if the link is broken. You can update the source or break the link if you no longer need it.
5. Repair the workbook—if the file itself is damaged, Excel may fail to read references. Use File > Open > Open and Repair as a last resort.

Frequently Asked Questions

Why does this error happen when I refresh a PivotTable?

Refreshing a PivotTable re-reads the source range. If that range no longer exists (deleted rows, moved sheet, renamed sheet), Excel cannot find it and throws the error. Always check the source range after editing the underlying data.

Can I fix this without changing the source data?

Yes. You only need to update the reference, not the data itself. Use “Change Data Source” for PivotTables or edit the connection string for external data. The data remains untouched.

Does this error mean my data is corrupted?

No. It means Excel cannot locate the reference, not that the data is damaged. The data is usually intact—only the path or range is broken.

How do I find the source of a PivotTable in Excel?

Select the PivotTable, go to PivotTable Analyze > Change Data Source. The dialog shows the current source range or connection name. You can also use the “Connections” pane to see all external links.

What if the source is in another workbook that I don’t have?

If you don’t have access to the source file, you cannot refresh the data. You can either obtain the file, or break the link and paste the data as values if you only need the current snapshot.

Is there a way to automatically update the source range?

Yes. Convert your data range to an Excel Table (Ctrl+T). Tables automatically expand, and PivotTables based on tables will include new rows without manual updates. This is the recommended long-term fix.

Related

• Fix System Thread Exception Not Handled Windows 10
• How to Deactivate iMessage on iPhone
• Safari Not Working on iPhone? Fixes That Work