[Solved] Excel Found A Problem With Formula References In This Worksheet

How To Troubleshoot This Persistent Error Message

Not this dialog box again!

If you are reading this article, you have probably been frustrated with a persistent error message that Excel keeps showing you when your spreadsheet opens stating:

Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.

~Love Microsoft Excel

Well, thank you, Excel for giving us a blanket, catch-all statement that is essentially telling us, “I’ve found an issue with your spreadsheet, I’m not going to tell you where it is or even what type of object is causing this…but enjoy scouring the entire file! Oh, and I almost forgot, I’m going to keep reminding you about this error until it’s fixed.”

So after clicking the OK button about ten times, you’ve probably spent more than a little time trying to figure out what is causing this message that keeps popping up.

I have been in your shoes feeling this exact frustration. Hopefully, this article can guide you through the different parts of your spreadsheet that may be housing any invalid formula references.

Steps to Locate Formulas With Reference Errors Within Your Excel Spreadsheet

Step 1: Isolate The Specific Spreadsheet Tab

Sometimes if you have an enormous file with lots of tabs, you may want to start by isolating the specific tab(s) that are causing this error message. You can do this by copying over your tabs one by one and determining which tab in your new workbook triggers the error message. Make sure you save after each tab is copied over in case the Saving event is what triggers the error message.

Step 2: Check External Links

Navigate to your Data Tab >> Queries & Connections >> Edit Links and see if any of your links contain errors in them. If you find any, simply remove (or break) those particular links. This may or may not solve the issue, but at the very least you are getting rid of some garbage that could be causing corruption to your file down the road.

Step 3: Review The Name Manager

It is a good idea to scroll through the Name Manager (dialog box that allows you to manage all your named ranges) and see if any of the formulas associated with your Named Ranges include errors such as:

• #N/A
• #REF!
• #VALUE!
• #NAME
• #DIV/0

Go ahead and delete all of the Named Ranges with errors and see if that action fixes your issue. There is even an option to filter on Named Ranges with errors as shown below that can make this process even faster. 

Step 4: Check Your Charts

I’m not going to lie, Chart object errors causing this message are the most painful to track down, especially if you have a large number of charts on a given tab. Typically, if the error is within a chart it will pop up after you have selected the chart or when you are entering the Select Data Source dialog box.

Below is a list of locations to check for errors.

• Check for errors in the chart’s source data (aka the cells feeding your chart)
• Check for errors in each chart series formula (within the Select Data Source dialog)
• Check for errors in the horizontal axis formula (within the Select Data Source dialog) 
• Check for errors in the chart’s Secondary Axis (if applicable)
• Check for errors in any linked Data Labels, Axis Labels, or the Chart Title

Finally, if all else fails try Copy/Pasting the chart and see if the new copy gives you the same error message. If not, simply delete the old chart and use the copy going forward.

Step 5: Check Your Pivot Tables

There could be an error residing in your Pivot Table’s Data Source formula. Select your Pivot Table and navigate to PivotTable Tools >> Analyze >> Change Data Source >> Change Data Source… and see if your formula has any errors in it.

Any Other Solutions I Missed?

I believe I have covered all the problem areas I have seen in my years of working with Excel, but if you have come across other locations within your spreadsheet file that have caused this error message to show, please let us all know about it in the comments section below.

I wish you the best of luck and I hope this article gets you heading in the right direction!

Chris 🙂

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Dynamic Array Formula Spill Ranges With Charts

Linking Dynamic Array Formulas into Chart Series With the introduction of Dynamic Array functions in Excel, your spreadsheets can now...

How To Write A Checkmark Symbol Formula in Excel

Incorporating Checkmark Symbols Into Your Spreadsheets Symbols are great to incorporate into your Excel spreadsheets as they often can add...

4 PRO Ways to Create A Filtering Search Box [Excel Download]

Creating A Search Box In Excel It's unfortunate Excel does not have a form control search box (maybe in the...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.