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 creating Pivot Tables, Text boxes or dealing with broken hyperlinks in 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 to Tools > References.
Exit Design Mode
Many users stumble on this error while creating a text box from the Developer tab. All the ribbon items get grayed out after Excel displays the alert. While you can move between all tabs, you cannot open the File tab.
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 the Reference Isn’t Valid error.
- Click the OK button in the warning message box.
- Select Cancel (X) next to the formula bar.
- From the Developer tab, deselect Design Mode.
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 as Set Cell doesn’t hold a formula. You will also get this alert if you’ve entered a formula instead of a value in the By Changing cell.
To fix this issue, check if you’ve embedded formulas in all cells in the Set Cell range. Additionally, verify that your By Changing cell holds 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.
- Open your worksheet.
- Head to Insert > PivotTable.
- Enter a valid named range in the Table/Range section.
Rename File
You will encounter the Data source reference is not valid error 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 creating Pivot Tables that need to take your current worksheet as a reference.
You need to remove the squared bracket from your file name to solve this error.
- Open File Explorer to locate your document.
- Right-click on your Excel file, then click F2.
- Erase the squared brackets from your file name and hit Enter.
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.
- Launch your Worksheet.
- Locate the hyperlink from your sheet, then right-click on it.
- Choose Remove hyperlink.
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, use Ctrl + S to save it to your device.