Fix: Data Source References is Not Valid in Excel
Some users have been getting the “Data source reference is not valid” when trying to create a pivot table from a range in Excel. This particular error is reported to occur with multiple Excel and Windows version – so the issue is neither OS or Excel version specific.
What is causing the “Data source reference is not valid” Error in Excel?
We investigated this particular error message by looking at various user reports and the repair steps that they followed in order to resolve the issue. As it turns out, there are several common culprits that will end up triggering this particular error message:
- Excel file name contains square brackets – There are a set of forbidden characters that are not supported by Pivot tables. One popular reason why you might be seeing this particular error is if the excel file name contains ‘[‘ or ‘]’. In this case, the issue can be resolved by editing the file name and removing the square brackets.
- Excel file is not saved on the local drive – You might be seeing this particular error message if you attempt to create a Pivot Table from an Excel file opened directly from a website or an email attachment. In this case, the file will be opened from a temporary folder, which doesn’t have the same permissions as a regular folder. If this scenario is applicable, you can resolve the issue by saving the .xlsx file on your local drive first.
- The pivot table data refers to a non-existent range – A range that is not defined properly can also be causing this particular error. If you’re trying to create a Pivot table with a range that wasn’t defined, you’ll be by the same error message. In this case, you can resolve the issue by defining the rage before attempting to create the PivotTable.
- The data source refers to a named range that contains invalid references – Another possible reason why this error occurs is when the range is defined but it references to invalid values. In this case, you can use the Name Manager to identify the values referenced and make the necessary modifications to resolve the issue.
If you’re currently struggling to resolve the Data source reference is not valid error, this article will provide you with several troubleshooting steps that will help you identify the problem that is triggering the error message. Below you’ll find a collection of methods that other users in a similar situation have used to get this resolved.
For the best results, follow the methods in the order that they are presented. You should eventually stumble upon a fix that will be effective in your particular scenario.
Let’s begin!
Method 1: Removing brackets from the file name
One of the most popular reasons that will end up triggering the Data source reference is not valid error is an incorrect Excel file name. If the reports are generated by a report automation system and the name contains forbidden characters like square brackets ‘[]‘, you’ll get this error message whenever you try to create a PivotTable.
If this scenario is applicable to your current situation, you will be able to resolve the issue by modifying the name of the .xlsx file to remove the forbidden characters. Here’s a quick guide on doing this:
- Close the Excel window that is currently using the file. If the file is in use, you won’t be able to rename it.
- Use File Explorer to navigate to the location of the Excel file. Once you get there, right-click on it and choose Rename.
- Next, go ahead and remove brackets from the name of the file since Pivots table is not configured to support them.
Removing the brackets from the file name - Attempt to recreate the Pivot table again and see if you’re still encountering the error.
If you’re still encountering the Data source reference is not valid error or this method wasn’t applicable to your particular scenario, move down to the next method below.
Method 2: Saving the file on the local disk
This issue might also occur if you are opening a file directly from a website or directly from an email attachment. In this case, the file will be opened from a temporary file, which will end up triggering the Data source reference is not valid error.
If this scenario is applicable to your current scenario, you will be able to resolve the issue by saving the Excel file to a local drive first. So, before attempting to create the Pivot table, go to File > Save as and save the file on a physical location (on your local drive).
Once the Excel file has been locally saved, recreate the steps that were previously triggering the Data source reference is not valid error and see if you can create the Pivots table without encountering the error.
If this method wasn’t applicable to your current situation, move down to the next method below.
Method 3: Ensuring that the range exists and it’s defined
Another reason why you might encounter the “Data source reference is not valid.” error when trying to insert a Pivot Table is a non-existing / not defined range.
To put this into perspective, let’s say that you’re trying to create PivotTable. Naturally, you go to Insert PivoTable, you select the toggle associated with Select a table or range and set the Table/Range to ‘test’. Now, if you’ve based the values on the ‘test’ range and it doesn’t exist, you will see the “Data source reference is not valid.” error as soon as you click Ok.
If this scenario is applicable, you will be able to work-around the issue by defining a range before attempting to create a pivot table. Here’s a quick guide on how to do this.
- Click the Formulas tab from the ribbon bar, then click on Name Manager from the list.
- Inside the Name Manager window, click on New and name the range that you’re going to create. Then, use the Refers to box to set the cells that you want to use for the range. You can either type it out yourself or use the built-in selector.
- Now that the range is defined, you can successfully create the pivot table without encountering the same error message.
If this scenario was not applicable or you’re still encountering the Data source reference is not valid error even after performing the steps above, move down to the next method below.
Method 4: Ensuring that the reference for the named range is valid
Even if you have successfully defined the range but you are still seeing the Data source reference is not valid error, chances are you’re seeing the message because the named range is referring to some cells whose values aren’t valid.
To rectify this, go to Formulaes > Name Manager and see if the range is referring to the cells that you want to analyse through the pivot table. If you see any inconsistencies, use the Refer to: box to make the switch to the correct value.
Once the modifications are made, attempt to create a Pivot Table again and see if you’re still seeing the same error message.
