Excel triggers the“..Reference isn’t Valid”error when it cannot locate the cell or range you referenced. While this normally is the case, you will also run into this issue if your Excel file name includes invalid characters.
You may encounter the reference error while creatingPivot Tables,Text boxesor dealing withbroken hyperlinksin the Excel program. While you could exit the error message and enter correct references, in some cases this is not possible as all options in the ribbon get grayed out.
Before heading to the fixes, check if your reference is listed as broken or missing. You can check your reference from the Visual Basic for Application (Alt + F11) window. Head toTools > References.
Exit Design Mode
Many users stumble on this error while creating a text box from theDevelopertab. All the ribbon items get grayed out after Excel displays the alert. While you can move between all tabs, you cannot open theFiletab.
Even though this error seems out of the blue, it is actually because of a minor human error. When you insert the text box, you must exit the Design mode before you start entering data into the box. However, if you enter your value before exiting the Design mode, you will actually enter your data in the formula bar.
The formula bar initially contains the formula referencing the location of the text box in the grid. When you enter a value that does not refer to a location, Excel will trigger theReference Isn’t Validerror.
Referred Cell Does Not Include Formula: Goal Seek
If you run into this error while using Goal Seek (VBA or What-if analysis), it may be because one of the cells you referred to asSet Celldoesn’t hold a formula. You will also get this alert if you’ve entered a formula instead of a value in theBy Changing cell.
To fix this issue, check if you’ve embedded formulas in all cells in theSet Cellrange. Additionally, verify that yourBy Changing cellholds a numeric value, not a formula. After making these changes, run the command again.
Change Pivot Table Named Range
You may have entered an invalid named range while creating a pivot table in Excel. When creating a Pivot Table, you need to enter the cell or named range that exists in the grid.
You must have made a typo while referring to a named cell range, causing Excel to fail to locate your range to create a Pivot Table. Verify your named range from the Name Manager before you create a Pivot Table.
Create a new PivotTable to change your named range.
Rename File
You will encounter theData source reference is not validerror message if your file name has squared brackets. Excel cannot take references from your worksheet if your username contains this special character. Therefore, you cannot perform actions such as creatingPivot Tablesthat need to take your current worksheet as a reference.
You need to remove the squared bracket from your file name to solve this error.
Remove Hyperlinks
You could also be dealing with broken hyperlinks if you receive the reference isn’t valid error. The file you linked could be missing or corrupt, making your reference invalid.
You can work around this issue by removing the problematic hyperlinks from your worksheet. If you do not know which hyperlink may be causing this issue, your best bet is to remove all existing hyperlinks.
Save the File
Excel may also trigger this error if you try to make changes in an Excel file that isn’t stored on your computer. If you cannot save the document, the creator of the document may have not given you adequate permission.
Check if your file is currently running on Read-Only mode. If you haven’t saved the file locally, useCtrl + Sto save it to your device.
