Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»Reference Isn’t Valid Excel Error – 6 Ways to Fix it

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

Asmi DhakalBy Asmi DhakalJanuary 6, 2023
reference isn't valid excel

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. 

Table of Contents

  • Exit Design Mode
  • Referred Cell Does Not Include Formula: Goal Seek
  • Change Pivot Table Named Range
  • Rename File
  • Remove Hyperlinks
  • Save the File

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. 

  1. Click the OK button in the warning message box.
    Reference isnt valid text box error excel
  2. Select Cancel (X) next to the formula bar.
    Cance Button Formula Bar
  3. From the Developer tab, deselect Design Mode.
    Exit Design Mode Excel

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.

Goal Seek Excel

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. 

Name Manager Excel

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.

  1. Open your worksheet.
  2. Head to Insert > PivotTable.
    Insert Pivot Table Excel
  3. Enter a valid named range in the Table/Range section.
    Create Pivot Table Excel

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.

  1. Open File Explorer to locate your document.
  2. Right-click on your Excel file, then click F2.
    Rename Excel File
  3. Erase the squared brackets from your file name and hit Enter.
    Renamed Excel File

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.

  1. Launch your Worksheet.
  2. Locate the hyperlink from your sheet, then right-click on it.
  3. Choose Remove hyperlink.
    Remove Hyperlink Excel

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.

Save excel file

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. 

fix
Asmi Dhakal
  • Twitter
  • LinkedIn

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at asmi@technewstoday.com

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
how to set print area in excel

How to Set Print Area in Excel

January 5, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
usb a vs usb c

USB A Vs USB C – What’s the Difference?

January 31, 2023
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
You may also like
Razer Mouse Not Working

Razer Mouse Not Working? Here’re 9 Ways to Fix It

February 3, 2023
how to make cursor bigger

How to Make Cursor Bigger

February 3, 2023
surface-screen-flickering

Why is My Surface Screen Flickering? 6 Ways to Fix It

February 3, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

Type above and press Enter to search. Press Esc to cancel.