8 Fixes To Resolve “Excel Found A Problem With One Or More Formula References” Error

Are you guys looking for some easy tricks to fix “Excel found a problem with one or more formula references” error? Well in that case this blog will surely help you to get rid of this persistent Excel error message.

So, read the complete tutorial and try all the given solutions for fixing up Excel formula references error. Before diving into the solution let’s run a quick eye over the scenario which many users like you complaining about.

 User Overview:

Upon saving my Excel sheet, I receive the following error message:

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

“Error Checking” (under “Formulas” → “Formula editing”) returns

The error check is completed for the entire sheet.

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

Excel in Office 365 ProPlus, Windows 7.

source:

https://superuser.com/questions/1003139/what-causes-excel-to-raise-an-error-about-one-or-more-formula-references-when

To fix corrupt Excel file, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

What Does Excel Found A Problem With One Or More Formula References In Worksheet Error Means?

In Excel, formulas play a great role and thus many experts consider that Excel is all about formulas. Though Excel provides so many formulas and functions to work flawlessly in this application. But sometimes silly mistakes in these formulas start showing annoying errors like “Excel found a problem with one or more formula references in worksheet”.

Error declaration:

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

Screenshot of the error:

Excel found a problem with one or more formula references in this worksheet

Reasons Of  “Excel Formula References Error”:

The reason behind getting such an error can be the following:

  • when your Excel spreadsheet formula having a wrong cell reference
  • maybe you have used object linking or link embedding OLE to the application which is not working.

Like this, there are many more reasons behind such Excel Formula References Error.

After catching a complete idea about the root causes of this excel error. Now it’s high time to run down into the fixes section. So that you can easily troubleshoot “Excel found a problem with one or more formula references”.

How To Fix “Excel Found A Problem With One Or More Formula References” Error?

How To Fix “Excel Found A Problem With One Or More Formula References” Error

Fix 1# Isolate The Specific Spreadsheet Tab

If in case you have an enormous number of files with so many tabs then start isolating some specific tab that is generating this error message. Well to perform this you just need to copy your tabs from the old workbook to a new one. Keep checking which tab in the new workbook throws the error message.

Press the save option after copying down each of the tabs. As it is seen that error message usually appears at the time of saving the event.

Fix 2# Check External Links

The second option to fix Excel found a problem with one or more formula references” is by picking out the external links having errors.

To check out the external links containing errors, you just need to follow this step:

In your Excel application, go to your Data Tab >> Queries & Connections >> Edit Links.

If you encounter any fault in the Excel external links then remove it.

Fix 3# Check The Name Manager

Another very effective way to troubleshoot Excel found a problem with one or more formula references error is by checking out the name manager.

For this, you just need to scroll through the Name Manager (it’s a dialog box that helps you to easily manage the entire of your named ranges). If any formula associated with your Named Ranges is having an error (ie #N/A, #REF, #VALUE!, #NAME?, #DIV/0).

Then delete all the named ranges one by one which is showing such error. and after every deletion also check whether your action fixes the issue or not.

The second option is to filter out the Named Ranges having errors. Well, it is quite a faster way to check out the error showing Name Manager.

Fix 4# Check Your Charts

It’s a quite difficult task to track down which excel chart object is actually causing this error. Mainly when you have so many charts on the given tab.

Generally, it is seen that if a chart is having some kind of error then it will pop-up after you select the chart or when you insert the Select Data Source dialog box.

Below here is the list of area locations where you need to check for errors.

  • In the source data of the chart.
  • in each of the chart series formula present within the Select Data Source dialog box.
  • Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
  • Also, check-in the chart’s Secondary Axis.
  • Also check in the linked Axis Labels, Data Labels, or in the Chart Title.

If in case all the above trick fails to identify the error causing chart. Then in that case try Copy/Pasting the chart and watch out whether the new copy is also showing the same error message.

Fix 5# Check Your Pivot Tables

Chances are also that problem is somewhere residing in your Pivot Table’s Data Source formula. So, choose your Pivot Table and go to the PivotTable Tools >> Analyze >> Change Data Source >> Change Data Source…

Now, here you need to check out whether any of your formula is having the error or not.

Check Formulas in Excel

Sometimes small and silly formula errors in excel can also lead to big consequences. Whether you have misplaced the comma or entered a wrong reference; all these will severely hamper your results. So, to avoid such a situation it’s better to check your formula and make it error-free. Learn how it is to be done:

Fix 6# F2 – See formulas in Excel

For checking out the formula of some specific cell:

Firstly, you need to choose the cell after then press the F2 key from your keyboard.

This will highlight the cells referenced in the formula. After then you can make easy check and editing of your excel formulas.

Fix 7# Control + ~ To show all Formulas

Choose any cell of your excel spreadsheet and from your keyboard press the Ctrl and tilde ( ~ ) button simultaneously.

This will present you with all the formulas having on your worksheet. To go back to the normal view just press the Ctrl and Tilde ( ~ ) button once more.

You can get this tool also from the Formula Auditing group present within the Formula tab.

Fix 8# Trace Precedents and Trace Dependents

In Excel, Precedents is used for the cell referenced in the formula. Suppose you are using a formula =C6+7 then in that case C6 is a precedent.

Whereas, in Excel terms, Dependents is used for the cells which depend on its content from another cell. If the above-mentioned formula is written within the cell C9. Then in that case C9 will dependent on C6.

For showing precedents in your workbook cell:

  • choose the cell having formula and tap to the Formula
  • Now from the Formula Auditing group select the Trace Precedents.
  • Arrow will indicate the precedent cells. You can toggle this feature off just by tapping on the Remove Arrows option of the same Formula Auditing group.

For using the Dependents in your workbook cell:

  • Choose the cells which are already referenced in some other cell’s formula.
  • Now go to the formula tab and then choose the Formula Auditing Group.
  • Hit the Trace Dependents option. This will figure out the cells which depend on that particular cell.

Automatic Solution: MS Excel Repair Tool

Besides trying the manual solution to fix Excel found a problem with one or more formula references error. If unfortunately, none of the above solutions works to fix it. In that case, chances are also that may be any of your Excel objects is somehow got corrupted. And thus, it is showing such formula references error.

To deal with such a situation we always recommend our users to go with reliable and professional MS Excel Repair Tool. This is the best repair utility to fix all sorts of issues, corruption, and even errors in the Excel file. With the help of this, the users can repair the Excel file easily and also restore the entire corrupted data including cell comments, charts, other data, and worksheet properties. it is the best professionally designed program to easily repair corrupt .xls and .xlsx files.

Try Now       Buy Now
* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

excel-repair-main-interface-1
stellar-repair-for-excel-select-file-2
stellar-repair-for-excel-repairing-3
stellar-repair-for-excel-preview-4
stellar-repair-for-excel-save-5
stellar-repair-for-excel-saving-6
stellar-repair-for-excel-repaired-7
previous arrow
next arrow

Tips To Minimize The Occurrence Of Excel Formula Errors

Here are some few helpful tricks to minimize the occurrence of Excel formula errors. so, read it and do follow it.

  • Start every formula in Excel by putting an equal sign in the beginning.
  • Make use of the * symbol to apply multiplication, not an X.
  • Check out the opening and closing parentheses that they are in pairs or not.
  • Put quotation marks in formulas around the text.

Conclusion:

I have tried my level best to cover all the fixes to troubleshoot this “Excel Found A Problem with One Or More Formula References” Error. but if you come across some other issue within your Excel spreadsheet then please let us know just by asking it in our comment section.



Priyanka is a content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.