Sometimes Excel finds a problem if you are working with a large a complex spreadsheet, and – upon saving, modifying, or opening – 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.
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 references error
What is the fastest way to find what is causing this problem?
- Should I scan to complete Workbook to fix the error?
- Is my entire Workbook is crashed?
- Is there a solution to fix the formula references error?
Let us get started! Read our guide, and you will learn how to fix this formula references error.
Scan the Workbook using Microsoft Inquiry
The first thing that you need to use is 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:
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.
Check External Links (and internal too) when Excel finds a problem
Steps to check external links:
- On the ribbon, locate the Data Tab
- Choose Queries & Connections
- 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!
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.
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 contains errors (i.e., #N/A, #REF, #VALUE!, #NAME?, #DIV/0)
Tip: The error occurs from the name manager. Mostly. If you have a defined range and there is no data, then 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 has potential risk. 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 appeared.
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 are going to delete the original tab of data, 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:
- Select your Pivot Table
- Click Analyze > Change Data Source > Change Data Source
- 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:
- Save your Workbook and use a temporary name, like ‘CheckMyBook’
- Try to Delete worksheets (tabs) one after the other (one at a time!)
- Repeat Step1 and Step2 until you find the corrupted Worksheet
Once you know the location of the problem is, 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.