How to Fix Excel Found A Problem with One or More Formula References

MS Excel is one of the pre-eminent applications to work with formulas and organize your data. However, like all other software, it is also prone to glitches and shortcomings. One such common error that users have been reporting so far is the excel worksheet reference trouble. It simply indicates that the workbook is either partially damaged or fully. The issue needs to be addressed as early as possible as it doesn’t let you save the file, resulting in information loss.

The guide will walk you through the potential reasons that generate the error and possible solutions to fix Excel found a problem with one of more formula references.

Part 1: What Causes Excel to Raise an Error about "One or More Formula References" when Saving?

There could be several reasons that might trigger the excel formula reference error while saving the workbook.

formula reference error

The users must learn about each one of them to fix the trouble easily. We have listed some of them here.

  • Incorrect Formula or Reference Cell

The basic reason that leads to the formula reference problem is the use of inappropriate formulas or reference cells. In other words, when your excel spreadsheet formula consists of a wrong cell reference, it leads to an error.

  • Inappropriate link embedding OLE

Sometimes, the excel worksheet reference error generates when you have used incorrect object linking or when the link embedding OLE to the application isn’t working properly.

  • File Corruption

Yet another potential reason behind the error is file corruption. The application may indicate the formula reference problem if the spreadsheet is partially or fully corrupted. It can happen due to many reasons like virus attack, power loss, inappropriate file transfer, etc.

  • No values in the range cells

If the user forgets to enter values in the named or range cells, it affects the excel format and leads to trouble.

Part 2: Troubleshooting the error with 8 Solutions?

So, now you are aware of how to trace errors in Excel, it’s time to enlist some of the effective methods to fix the problem. Pick any of them and get rid of the trouble soon.

Method 1: Isolate the Spreadsheet Tab

The simplest solution to deal with the formula reference error is to separate the specific spreadsheet tab that is generating the error message. For this, you just need to create a new tab and copy all the tabs from the old Excel workbook to it.

Keep checking which tab in the new workbook is creating the error. Since the error message appears at the time of saving the event, press the save option after copying down each of the tabs to locate the faulty option. 

Method 2: Recheck Formulas in Excel

Faulty or incorrect formulas is one of the primary reasons that generate the formula reference problem in MS Excel. To fix the trouble, the users need to re-check all the formulas and modify the incorrect ones.

However, if the error appears in a large excel workbook with multiple sheets, it’s quite challenging to locate the problem cell. In that case, you are suggested to run an Error Checking Option to scan the problematic formulas used in the worksheet.

Follow the steps to carry out the process.

  • Navigate to the "Formulas" tab in the menu bar and click the "Error Checking" option.

select error checking option
  • This button scans the entire sheet and pinpoints the cells with inappropriate formulas if any.

  • However, if no issue is found, it simply displays the message saying - "The error checking is completed for the entire sheet."

Method 3: Check the External Links

The users can fix the Worksheet reference in Excel error by reviewing the external links added to the spreadsheet. Sometimes, the formula reference trouble appears when the external links included in the workbook are corrupted, inappropriate, or contain errors.

Follow the instructions to check the external links.

  • Launch the MS Excel application and go to the "Data" tab.

choose the data tab
  • After that, click the "Queries & Connections" and then tap the "Edit Links" option.

 open edit links option
  • Check the external links and remove the faulty ones to fix the error.

Method 4: Review Charts

Does excel sheet reference in formula error still persist? Consider reviewing charts and checking if they cause trouble. Remember that it is a bit time-consuming and takes a while depending on the size of the Excel workbook. You can also check the specific location if it isn’t practically possible to track down the particular Excel chart object.

Here's the list of area locations where you should check the error.

  • To initiate the process, go to the "Select Data Source" Dialogue Box and check the Horizontal Axis formula.

select data source
  • After that, check the chart’s Secondary Axis.

  • Now, check the linked Data Labels, Chart Title, or Axis Labels and see if it resolves the error.

Method 5: Check the Name Manager

Wondering how to fix Excel and found a problem with one of more formula references? Try checking out the name manager and see whether it resolves a problem or not. The Name Manager dialog box mainly works with all the defined names and table names in an Excel spreadsheet. When the user mistakenly adds the wrong names, it causes formula reference trouble.

To check the Name Manager, follow the steps here.

  • Scroll through the "Name Manager" dialog box and check if any formula linked to your Named Ranges is having an error.

name manager dialog box
  • If yes, delete all Named Ranges that are showing such errors one by one. Open the excel workbook again to check whether the action fixes the trouble or not.

Method 6: Check Your Pivot Tables

Yet another best solution to fix Excel found a problem with one of more formula references is to re-check the pivot tables and make the modifications, wherever needed. Sometimes a small typo like a misplaced comma or unnecessary full stop can lead to such problems.

Follow the steps here.

  • Firstly, go to the PivotTable Tools and click on the Analyze > Change Data Source > Change Data Source…

  • Now, preview all the formulas and check if any of them is inappropriate or problematic. Check each and every formula thoroughly and make the changes if needed.

Other Workable Solutions – Wondershare Repairit

Are you still facing an excel worksheet reference error and wondering about how to resolve it? Fret not as we have got a solution. Consider trying the Wondershare Repairit software and root out the problem in no time. This third-party tool can repair multiple files at once to save your time and effort.

Features:

  • It can fix any kind of corruption.

  • The application is compatible with four major file formats, including PPTX, DOCX, XLSX, and PDF.

Follow the step-by-step instructions mentioned below to fix the formula reference error with Wondershare Repairit.

Step 1: Add Corrupted Files

  • First of all, download and install the Wondershare Repairit on your system. Launch the application and go to the side dashboard to select the "File Repair" option.

  • After that, click on the Add button to upload the corrupted files. As soon as you hit the button, a browser window will open that takes you to the folder where the corrupted excel file is stored.

xxx
  • Wait for a while until the tool finishes the uploading process. The users can then check the file details on the screen.

Step 2: Initiate the Repair Process

  • If you are satisfied with the details, move your cursor to the bottom and click on the "Repair" button to start the process.

xxx
  • You can check the progress from an on-screen indicator and can even cancel it if you want to. The tool will notify you when the process gets completed.

xxx

Step 3: Preview and Save

  • Click the "Preview" button located next to the repaired file and check if the file has been repaired successfully.

  • If you are satisfied with the result, click on the Save button to export the files to the dedicated folder.

xxx

Final thoughts: To-do list to avoid the Excel found a problem

Although you are now aware of all the practical solutions to fix excel and found a problem with one of more formula references, it’s recommended to know the precautionary measures to avoid the error in the future.

  • Always put quotation marks in the Excel formula around the text.

  • Do not forget to enter the equal (=) sign at the beginning of every formula.

  • Review the opening and closing parentheses to verify whether they are in pairs or not.

  • Use * symbol to apply the multiply function and not "X."

Wrapping up

Formula reference error is one of the most annoying problems that occur in MS Excel. It makes you unable to save the file content, resulting in data loss. Keeping its seriousness into account, we have compiled the best possible solution that can help you fix the error. We hope our guide will help you troubleshoot the problem without putting in much effort. Additionally, the post also outlines some precautionary measures to prevent the error from occurring again.

Although the methods listed above are capable of rooting out this excel problem, you should consider trying the Wondershare Repairit to fix Excel and find a problem with one or more formula references on the go. It is a reliable tool that can repair the corrupted excel file in three easy steps.

Other popular Articles From Wondershare