Fix Excel found a problem with formula references in this Worksheet

It can happen that Excel found a problem with one or more formula references in this worksheet upon saving, modifying, or opening. Fix it!

Sometimes, you are working with a large complex spreadsheet, and you receive the following error message:

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.”

Excel Found A Problem With Formula References In This Worksheet

Nice! Like it or not, the issue is permanent, and the dialog box appears. Try to save the Workbook! The result is the same. You can not find the source of the error; run the error checker countless times. The clock is ticking, and you are very frustrated. I agree with you, even if I have an advanced Excel user. In this article, we will find the solution.

Troubleshooting with Excel found a problem with one or more formula reference error

What is the fastest way to find out what is causing this problem?

  • Should I scan to complete the Workbook to fix the error?
  • Is my entire Workbook crashed?
  • Is there a solution to fix the formula reference error?

Let us get started! Read our guide, and you will learn how to fix this formula reference error.

Scan the Workbook using Microsoft Inquiry

First, you need to use a nice COM add-in, Microsoft Inquiry. It is useful if you have 10-20 Worksheets in your Workbook. Take a closer look at the corrupted spreadsheet!

Run the Workbook analysis! It can help you isolate the Worksheet, named range, cell reference, or formula containing errors.

The result looks like this:

Isolate The Specific Spreadsheet Tab

In the example, you can find a lot of formula errors. The right-side pane helps you to locate and fix the missing formulas on the Calculation Worksheet.

Steps to check external links:

  1. On the ribbon, locate the Data Tab
  2. Choose Queries & Connections
  3. Click Edit links

In some cases, you will find unwanted and cross-linked connections. Break the links, and your file is ready to use again!

break link if Excel Found A Problem With Formula References In This Worksheet

Tip: Before breaking your links, you can fix the reference error. Open the source file and update values. If it is not helpful, break the link between the Worksheets.

Check The Name Manager

The Name Manager is a helpful tool. For example, with its help, you can create named ranges. But it is a potential place for issues.

In the next example, you will learn how to manage the “Excel found a problem with….” messages.

named range

The list looks great and error-free.

But take a closer look at the filter function on the top-right corner! From the list, choose ‘Names with Errors.’ Then, delete or fix all the named ranges that refer to containing errors (i.e., #N/A, #REF, #VALUE!, #NAME?, #DIV/0)

named range filter

Tip: The error occurs from the name manager. Mostly. If you have a defined range and no data, it will come. Check your dynamic ranges. Fill the cells in some data for testing; then, it will resolve.

Check your Hidden and VeryHidden Worksheets

Hidden Worksheets have potential risks. If you have a simple hidden Worksheet, right-click on an active Tab and use the Unhide command.

First, move the data to a visible Worksheet, then delete them. From now on, the uninformative error message no longer appears.

What about very hidden Worksheets?

Tip: If you have very hidden sheets, click Alt + F11 to reach the VBE editor.

Locate the ‘Visible’ property and use the drop-down list. Select the ‘XlSheetVisible’ option. That’s all!

Check Your Charts

It’s not easy to track the errors that caused Chart objects if you have many charts on a single Worksheet. To speed up the recovery process, select the chart and take a closer look at the Select Data Source dialog box.

Follow the checklist below:

  • Try to find the corrupted data source
  • Audit each chart series formula
  • Check for errors in the horizontal and vertical axis formula
  • Update the source of linked Data Labels, Axis Labels
  • Check the Chart Title

Case study: For example, you have a data tab, and you want to move this data to new tabs. All charts work fine. If you delete the original data tab, you will get the same error message. Check every chart and data series to find the missing reference and isolate which charts are causing the issue. Update your axis labels!

Tip: in some cases, all of your ideas misfired.

Back to the roots: delete the chart, save the file, and this time, no invalid reference error message!

Check Shared Excel files

You can work carefully with shared Excel Workbooks.

Let us see another real-life example:

I have just worked with my team on a Project Worksheet with Dropbox. A team member copied the data (which uses data validation) and pasted it into another Workbook. Everything was OK until someone moved, deleted, or renamed the source file.

What was the problem? The data validation list was pasted as a link and pointed to the source Worksheet.

So, keep your eyes on the shared files!

Check Your Pivot Tables

Check your Pivot tables data sources using these steps:

  1. Select your Pivot Table
  2. Click Analyze > Change Data Source > Change Data Source
  3. Check the formula which contains error(s)

The most common problem with Pivot tables is when you are referencing across workbooks. Not a good idea.

Define a named lookup range using a formula instead of a hard-coded reference. Use absolute cell references, for example, $A$1

Tip: Without using a complex dynamic range for its table range, VLOOKUP will not necessarily function correctly once a PivotTable is re-arranged because GETPIVOTDATA directly accesses the desired data value regardless of the orientation of the PivotTable.

What if nothing works: Excel found a problem somewhere

A simple workaround:

  1. Save your Workbook and use a temporary name, like ‘CheckMyBook’
  2. Try to Delete worksheets (tabs) one after the other (one at a time!)
  3. Repeat Step1 and Step2 until you find the corrupted Worksheet

Once you know the location of the problem, open the original Workbook and fix the problem.

Final thoughts: To-do list if Excel found a problem

We recommend you follow the checklist as mentioned above. The possible workarounds cover all the fixes to troubleshoot this “Excel Found A Problem with One Or More Formula References” Error. We hope that the tutorial was helpful to you and as a result of the demonstrated methods, you can get rid of this problem.

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.