Some Excel customers have reported experiencing the Data source reference is not valid error whereas making a pivot desk in Excel. We are going to study why this error happens and how one can resolve it in this put up.
Why is my knowledge source reference not valid?
The frequent cause behind the Data source reference is not valid error in Excel is the truth that the filename of the Excel workbook accommodates sq. brackets that are invalid characters. One other major cause for a similar error is that the vary from which you are attempting to insert a pivot desk doesn’t exist or is undefined. Or, the reference for the named vary you’re utilizing to your pivot desk is invalid. In addition to that, it can be the case that you’re attempting to open the file from an URL or attachment in an electronic mail.
Now, in any situation, this put up will allow you to resolve the error. Right here, we’ll focus on working fixes to eliminate the “Data source reference is not valid” error in Microsoft Excel.
Data source reference is not valid in Excel
In case you are experiencing the Data source reference is not valid error when making a pivot desk in Microsoft Excel, you’ll be able to observe the beneath fixes to resolve it:
- Delete brackets from the filename.
- Save the workbook in your native disk.
- Guarantee that the vary exists and is outlined.
- Be sure that the reference for the outlined vary is valid.
- Restore the Excel file.
1] Delete brackets from the filename
This error is prone to happen if the filename of the Excel workbook consists of an invalid character i.e., sq. brackets. Therefore, if the situation applies, you’ll be able to change the filename of the problematic Excel file and delete brackets from it.
To do this, first, you should shut Excel and ensure the problematic workbook is not open in some other program. Now, open File Explorer utilizing Win+E and navigate to the folder the place you have got saved the Excel file. Subsequent, right-click on the file, select the Rename choice, take away the brackets from the filename, and press the Enter button.
After renaming the file, launch Excel and open your file. Test if you’ll be able to create a pivot desk with out the “Data source reference is not valid” error. If not, you’ll be able to transfer on to the subsequent repair.
2] Save the workbook in your native disk
It is likely to be the case that you’re opening the Excel file straight from an internet source or electronic mail attachment. If that’s the case, you’re prone to get the “Data source reference is not valid” error. So, if the situation applies to you, save the workbook in your native disk first after which open it in Excel to create a pivot desk. Open the file, click on on the File > Save as choice, after which put it aside in your native drive. As soon as carried out, test if the error is now resolved.
Learn: How you can repair the #REF error in Excel?
3] Guarantee that the vary exists and is outlined
You’ll face this error in case you are attempting to create a pivot desk in a variety that is not outlined or non-existing. For instance, you’re inserting a pivot desk by clicking on Insert > PivotTable > From Desk/Vary. Now, you have got entered the title of a desk or vary contained in the Desk/Vary as “TWC” underneath the Choose a desk or vary choice. However, the TWC vary doesn’t exist. So, you’re prone to encounter the “Data source reference is not valid” error.
Therefore, to keep away from this error, you should be certain that the vary from which you are attempting to insert a pivot desk exists and is outlined. To outline the vary, you’ll be able to observe the beneath steps:
- Firstly, open your Excel file and faucet on the Formulation tab from the ribbon bar.
- Now, press the Title Supervisor choice from the Formulation tab.
- Subsequent, click on on the New button and enter the title of the vary you’re creating.
- After that, contained in the Refers to field, enter the cells you’re going to use in the created vary. To do this, you’ll be able to both use the built-in selector or manually enter the vary.
- Lastly, press the OK button to finish the method.
As soon as carried out, you’ll be able to attempt inserting a pivot desk from the outlined vary and test if the error is resolved or not.
4] Be sure that the reference for the outlined vary is valid
Along with the above repair, you should be certain that referenced cells (Confer with) for the created vary comprise valid values. Else, you’ll expertise the Data source reference is not valid error. So, ensure the reference for the outlined vary is valid. Right here’s how:
- First, click on on the Formulation tab > Title Supervisor choice.
- Subsequent, choose the vary you’re utilizing to your pivot desk and test the entry for the Confer with worth.
- If the reference is incorrect, double-click on the vary after which make the modifications to the entry accordingly.
- When carried out, you’ll be able to attempt inserting a pivot desk and hopefully, the error will likely be resolved now.
Learn: Excel ran out of assets whereas trying to calculate formulation.
5] Restore the Excel file
If the above options didn’t give you the results you want, attempt repairing the problematic Excel file. It is likely to be the case that your file is corrupted which is why you retain getting this error. So, you’ll be able to restore the Excel file and get well knowledge to repair the error.
To do this, you should utilize the native function in Microsoft Excel. First, shut the problematic file and click on on the File > Open choice. Now, select the corrupted file, press the arrow current subsequent to the Open button, and choose the Open and Restore choice. See if this helps. If this methodology doesn’t work, you may as well attempt utilizing a third-party Excel restore instrument and test if it really works.
Hope, this put up helps you resolve the Data source reference is not valid error in Microsoft Excel.
Now learn: Error, Reference source not discovered – Microsoft Workplace challenge.
How do I repair the Reference is not valid in Excel?
You may repair the “Data source reference is not valid” error in Excel by eradicating sq. brackets from the filename of your Excel file. Other than that, save the Excel file in your native drive, outline the vary you’re utilizing in your pivot desk, and ensure the referenced cell values for the outlined vary are valid. We’ve got mentioned these and extra options in element beneath. So, allow us to try.
