When using Excel, the “Data source reference is not valid” error message usually indicates a problem with the data source or a link to an external data source.
This error often occurs when inserting a Pivot Table from a table or range in Excel.
This error can be frustrating, but this tutorial presents six common reasons and potential fixes you can try to resolve the problem.
Reason #1: Not Selecting Table or Range When Inserting a Pivot Table
One common cause of the “Data source reference is not valid” error is forgetting to select a table or range when inserting a Pivot Table.
If you are trying to create a PivotTable using a table called “Sales” and you forget to enter the name of or select the table in the Table/Range field on the “PivotTable from table or range” dialog box and click “OK,” Excel will display a warning message box letting you know that the data reference is invalid.
How to Fix?
- Click on any cell within the table or data range before inserting a Pivot Table. Excel will automatically detect the range of your data. Alternatively, manually select the table or data range by clicking and dragging to highlight the desired range.
- Define the name of the range before creating the PivotTable using the below steps:
- On the “Formulas” tab, click”Name Manager” on the “Defined Names” group or press the key shortcut Ctrl + F3.
- Click “New” on the “Name Manager” feature.
- On the “New Name” feature, enter a name for the table or range on the “Name” field and use the range selector on the “Refers to” box to select the table or range you want to use and click “OK.”
The range is now clearly defined, and you can use its name in creating PivotTables without encountering the “Data source reference is not valid” error message.
Also read: How to Group by Months in Excel Pivot Table?
Reason #2: Specifying Incorrect Table/Range When Inserting a PivotTable
If you enter a non-existent table or range on the “PivotTable from table or range” dialog box, Excel will return the “Data source reference is not valid” error.
If, for example, you are trying to create a Pivot Table using a table called “Sales” and you mistakenly enter a non-existent table, “Promotions” on the Table/Range field on the “PivotTable from table or range” dialog box and click “OK,” Excel will display a warning message box letting you know that the data reference is invalid.
How to Fix?
Ensure that the table/range you specified on the “PivotTable from table or range” is correct.
Also read: How to Change Data Source in Pivot Table
Reason #3: Square Brackets or Other Invalid Characters in Filename
Suppose you try to create a PivotTable using data from an Excel workbook that has invalid characters such as square brackets in its name (for example, “Sales ”).
An error message saying “Data source reference is not valid” will appear in that case.
You may encounter this issue when attempting to open an Excel workbook directly from a website or an email attachment.
The browser makes a duplicate of the file in the temporary directory and changes the file name to include square brackets.
You can also encounter the error if you open a PivotTable directly from the website or online.
How to Fix?
To prevent this issue, choose “Save” instead of “Open” when the browser prompts you to access the file.
This choice initiates the process of saving the file on the local disk.
To save the file to the local drive, use the steps below:
- Open the Excel file.
- Click “File” on the Ribbon.
- Click “Save As” on the left and “Browse” on the right pane and go to the folder where you want to store the file.
- Name the file without any square brackets or other invalid characters and click “Save.” Excel won’t let you save a file with square brackets or other invalid characters in its name on your local disk.
Reason #4: Data Source is Deleted, Moved or Renamed
If the data source has been deleted, relocated, or renamed, the reference to it becomes invalid.
One instance is when you use a named range as the data source and modify or delete the name, rendering the reference invalid.
How to Fix?
To remedy this situation, you may either revert to the original name or update the reference with the new name.
Reason #5: The Data Source is Online or on a Network Location
If the data source is not stored on a local drive but rather on a network or online location, it could result in issues regarding the connection or access to the data source.
This could also result in you getting the Data Source Reference is Not Valid Error
How to Fix?
To resolve this, you can transfer the data source to a local drive or ensure that the connection is stable and secure.
You must also ensure you have the necessary permissions to access the data source.
Reason #6: Excel Workbook is Corrupted
Corrupted files can cause many problems, such as the error message “Data source reference is not valid.”
How to Fix?
- If you suspect a virus or malware is causing the error, it’s advisable to conduct a virus scan. Running an antivirus scan might remove the virus or malware and resolve the issue.
- Create a new file and insert a PivotTable. The original file may be corrupted if the PivotTable works without issue in the new file. To fix this, utilize Excel’s repair feature or restore from a backup.
When Excel detects a corrupted workbook upon opening, the software will automatically activate File Recovery mode and try to fix it.
If the File Recovery mode fails to initiate, you may attempt the manual process below to recover your workbook and potentially resolve the “Data source is not valid” error:
- To open the Backstage window, click on the “File” tab.
- To open the file, click on “Open” on the left sidebar of the Backstage window. Then, select “Browse.”
Go to the folder in which the file requiring repair is located.
- Select the file and choose the “Open and Repair” option from the “Open” drop-down menu at the bottom.
- When a warning message box pops up, click the “Repair” button.
In this tutorial, we have discussed six reasons why the “Data source reference is not valid” error may occur in Excel and provided solutions for each one.
We hope that you have found this tutorial helpful.
Other Excel articles you may also like: