In this article we will see about reference isn’t valid excel and also see the solution. Data source reference is not valid” is one annoying problem that can be hard to get rid of, if you don’t know the source problem. In this article, you also see our another link what is spreadsheet. So, as the name suggests the error occurs when Excel tries to refer to location that is no longer available, invalid or doesn’t exist.
Reasons and solution for this error:
Now this error can occur due to several reasons are here:
Reason 1: REF categories named with
Named ranges are great tools when it comes to managing structured data. When you name a given range, Excel tags that specific name in the given range. By default, Excel adjusts the named ranges when you delete named ranges or add rows. But if you delete the entire category, the named range loses its context. Not all formulas, validations and conditional formatting will work based on that range. And if you try to copy that sheet or file you may get “The reference is not valid” error.
Solution- named range “context invalid error”
To resolve this type of error, use the shortcut CTRL+F3 . This will open the Name Manager. Check the names and there are categories. Remove or fix all named ranges that contain #REF! Save your file. This will remove the “Data source reference is not valid” error.
Reason 2: Reference is not valid error due to table name change
Tables in Excel help a lot in data management. But they can cause reference errors. It is similar to named ranges. When you use table names in formulas, data validation, conditional formatting, and when you delete the table or make some major changes, Excel can’t validate via reference error.
Solution to avoid reference error due to table:
To avoid such errors, you should check all formulas, validations, conditional formatting that refer to a table that has changed long ago. Change or remove such formulas. This will solve the “Data reference not valid error”.
Reason 3: Pivot tables data source reference is not valid
when we create a pivot table it requires a data range. Using that data range excel creates the pivot reports. Now if you try to create pivot table with invalid range or refresh pivot table that refers to a range that no longer exists, this can cause “Reference isn’t valid excel” error.
Solution for pivot table reference Error:
Resolve this type of error and if you have several pivot tables in a workbook that refers to multiple ranges, named range or data tables, make sure that they refer to valid range. When you refresh pivot tables and this error occurs, it is possible that you have changed some range or names in the data mode. Check one by one each of them and save your excel file. This will solve reference error problem.
Reason 3: Invalid data source reference:
Excel has features to import data from other sources. All these features can be accessed from the Data tab. “Get External Data”, “Power Query”, and “Power Pivot” are such examples. These features allow users to maintain data outside of Excel and import them when needed. When the data source does not exist at the defined location, it is obvious that Excel shows “The data source reference is not valid”.
Solution to data source reference is not valid:
To solve this error, go to Data then Get External Data then Existing Connection. A window will open that will show the existing connection to the workbook. Check the connections and if you don’t need them anymore, remove them. If you do need them. Go to the location of the data source to check if that exist or not.
Reason 5: Macro using undefined reference
If the error “Excel reference is not valid” occurs when running a macro, there can be several reasons for this. That macro references a range that no longer exists in the system. This can be easily resolved by clicking on Debug method. You have accessed an object whose reference has not been added. E.g. if you want to use active data object, you need to add reference to that object first.
Solution to macro using undefined reference
To add a reference to an object, open the VBE by using the Alt+F11 keys. Now go to Tools in the menu, click on References. This will open a dialog box containing all the available resources. Find the item you need. Check it and click on OK button. This will add a reference to that object.
Another way to fix reference isn’t valid Excel:
You can try the following steps for it:
- Try pressing F9 to force the workbook to recalculate and see if this fixes the issue.
- Try typing in =CurrentCell() into a blank cell in the Excel workbook. If it returns a correct result then everything is working.
Hope ! this article is helpful to you.