Summary: This article helps you in exploring different ways to fix Excel Data source reference is not valid error. Apart from this, get complete detail on why you are getting this error and how to fix pivot table data source reference is not valid error.
Many Excel users are reporting there are encountering the “Data source reference is not valid” error while trying to create a Pivot table in Excel.
Commonly the issue appears while opening the Excel workbook that contains square brackets () in the name and trying to create Pivot Tables by using data from within the workbook.
To extract data from corrupt Excel file, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Well, this is a very irritating error but is common as it is not limited to any particular Excel version. Let describe Excel data source reference is not valid error best with the user personal experience.
Recently, I have been getting this error message when I try to create a Pivot Table from Table in excel 2007. I have checked every single column and there is no empty one. I am really getting annoyed as the same data which have imported from CRM system sometimes work and other times does not work. If I also convert my data in to a range and try to insert PT, I get the same error.
Posted in AccountingWEB community…
Anyways this is not something that can’t be fixed, check out the possible fixes to troubleshoot pivot table data source reference is not valid error.
But before moving further, know what is causing Data source reference is not valid error in Excel.
Why I Am Getting The “Data Source Reference Is Not Valid” Error In Excel?
Well, the most common cause of the error is the square brackets (invalid characters) in the Excel workbook file name.
However, it is investigated despite this there are several other culprits as well that trigger data source reference is not a valid error message.
Here check them out:
1: Excel file not saved on the local drive –
If you are trying to create a Pivot Table from an Excel file opened directly from the website or an email attachment then you may encounter the particular error message. As in this case, the file will open from the temporary folder and this won’t have the same permissions as a regular folder.
If you are having the related scenario then resolve the Excel data source reference not a valid error by saving the file on the local drive first.
2: The pivot table data refers to a non-existent range –
If the range is not defined properly then this what caused the particular error. And you are trying to create a Pivot table with an undefined range then the same error appears. And to fix the error define the range before trying to create the Pivot table.
3: The data source refers to a named range that contains invalid references –
This is another possible reason responsible for the Data source reference is not valid error. Well, if the range is defined but references to invalid values, the error crop up. So to fix it make use of the Name Manager to identify the values referenced and other necessary modifications to troubleshoot the problem.
4: Excel file name contains square brackets –
Well, I discussed it earlier, if the name in Excel workbook contains square brackets then pivot table data source reference is not the valid error appears. Despite this there set of prohibited characters that Pivot tables won’t support. So to fix this Pivot table issue, edit the file name and remove square brackets and other forbidden characters.
So, these are some of the common reasons why you are getting the data reference is not a valid error in Excel. Now to resolve the error message, check out the possible fixes worked for the users to encounter a similar situation.
Make sure to follow the fixes in the order they are presented.
How To Fix “Data Source Reference Is Not Valid” Error In Excel?
Here are the following fixes which you need to try for fixing up this Excel data source reference is not valid error.
Let’s discuss all these fixes briefly….!
Fix 1# Remove File Name Brackets
As discussed above this is the most common reason that triggers the pivot table data source reference is not valid error.
So, while creating a Pivot Table make sure the file won’t contain the forbidden characters. Apart from this check out the steps to modify the name of the XLSX or XLS file for removing the prohibited characters.
Follow the steps to do so:
- Close the Excel window currently using the file.
- Now utilize File Explorer to locate the Excel file location > and as you go there> right-click on it > select Rename.
- And remove brackets from the file name and try recreating the Pivot table again.
Hope this works for you to fix the reference is not a valid Excel pivot table, but if still the error is not resolved then follow the next solution.
Don’t Miss: Ways to Repair MS Excel Pivot Table Corruption
Fix 2# Saving File On The Local Disk
You can also encounter the error if opening the file directly from the website or from an email attachment. And in this case, the file opened from a temporary file that at the end cause the Data source reference is not valid error.
Now, to resolve the issue save the Excel file to the local drive. And before creating the Pivot Table, go to File > Save as and save the file on the local drive.
As the file is saved, follow the steps that were causing the Data source reference is not valid error earlier.
It is estimated, you can now recreate the Pivots table without encountering the error.
Fix 3# Assure The Range Exists And Is Defined
If you are trying to insert Pivot Table in a non-existing or not defined range then this is another reason guilty for Excel data source reference is not valid error.
Let describe it best by creating a scenario, you are trying to create PivotTable, you will go to Insert PivotTable > select the toggle related with Select a table or range and set Table/Range to ‘test’.
And, if you have based the values on ‘test’ range and it won’t exist, you would encounter the “Data source reference is not valid” error just after clicking OK
However, if dealing with the same scenario then follow the below-given steps to fix the Excel error
Follow the steps to do so:
- From the ribbon bar click the Formulas tab > click on Name Manager.
- Now inside the Name Manager Window click on New > name the range you will create. And use the Refers to the box for setting the cells you desire to use for the range. You can type it yourself or utilize a built-in selector
- Now the range is defined, you can create the Pivot table without getting the error.
Hope after defining a range before trying to create a Pivot table, you can fix the pivot table data source reference is not valid Excel error.
Fix 4# Assure The Reference For Named Range Is Valid
Well, if after defining the range you are still getting the data source reference is not valid error, then the chances are you are getting the error message because the named range is referring to some cells whose values are not valid.
To correct this simply go to Formulas > Name Manager and check if the range is referring to the cells that you need to analyze through the pivot table. Well, if you identify any conflicts > then use the Refer to box to make the switch to the correct value.
And as the modifications are made, try to create a Pivot Table again and check if the Excel data source reference is not the valid error is fixed or not.
I Hope, the given solutions will work for you to fix the Excel data source reference is not valid error.
Fix 5# Utilize Excel File Recovery Tool
Chances are also that due to the occurrence of corruption issue in the Excel pivot table you are getting this data source reference is not valid error. So if the above fixes fail to resolve the issue then try the Excel Recovery Tool to fix any type of corruption issue that occurred in the pivot table.
This is an advanced repair tool that allows repairing corrupted, damaged, inaccessible Excel files and some sort of errors in Excel. Not only this also recover the lost, deleted, corrupted Excel file data without any modification.
You can recover chart, chart sheet, table, cell comment, image, cell comment, formula, and sort & filter, and all data components from corrupt Excel files. It is easy to use and supports all Excel versions.
Steps to Utilize Excel Recovery Tool:
Now It’s Your Turn:
So, this is all about the Excel data source reference is not valid error. I tried my best to put together the working solutions to fix the error.
Make use of the given solutions one by one to fix the error and start creating a Pivot Table without any error.
Hope the article helped you to solve all your queries. You can read one of our previous articles related to the Excel Pivot table issue PivotTable Field Name is not valid.
Despite this, I love to hear from you, feel free to share your valuable comments, queries, suggestions in the below comment section.