Summary: In this article, know how to fix the “Data source reference is not valid” in Pivot table, learn the complete information why you are getting the error and how to fix the 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.
You can read one of our earlier article PivotTable Field Name is not valid.
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, below 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 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 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 than resolve the Excel data source reference not 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 causing the particular error. And you are trying to create a Pivot table with an undefined range than 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 modification 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
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 makes 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 reference is not 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 than 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 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 > than use the Refer to: box to make the switch to 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 valid error is fixed or not.
Hope, the given solutions will work for you to fix the Excel data source reference is not valid error.
Utilize Excel File Recovery Tool:
You can make use of the professional recommended Excel Recovery Tool; this is an advanced repair tool that allows repairing corrupted, damaged, inaccessible Excel file 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 file. It is easy to use and support 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.
If, in case you have any additional questions concerning the ones presented, then visit our Repair MS Excel Ask Question