This useful application MS Excel uses tables to store and analyze mathematical data using formulas and functions and your office work is incomplete without this app. Like all software, it can have glitches, some users reported that while saving the Excel file they encounter an error ‘Excel found a problem with one or more formula references in this worksheet’. This problem can also happen due to damage or corruption of the workbook. In this article, you will know how to resolve this issue.
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 one or more formula references in this worksheet
There are many reasons for these glitches like incorrect formulas or reference cells, file corruption, incorrect link embedding OLE, and no values in the range of cells. However, the good thing is, you can easily resolve this error. However, if none of this is the cause, then we can say that there is nothing with your workbook, it’s just that MS Excel has some corrupted or missing files, in that case, we will see how to repair the app itself. So, if Excel found a problem with one or more formula references in your worksheet, follow the prescribed solutions.
- Check Formulas
- Check individual sheets
- Verify External Links
- Review Charts
- Repair MS Excel
Let’s implement these solutions to resolve the issue.
1] Check Formulas
It would be quite difficult to find the cell that is creating the issue when working on multiple sheets and a large excel workbook and incorrect formulas in excel is the primary reason that generates the error. In this scenario, you can use Error Checking feature to identify the formula that is creating the issue.
- Find out the Formulas tab, go there and click on the ‘Error Checking’ button
- This will do the scan on the sheet and it will show the issues if it found any. If it could not find any issue it shows the following message:
The error check is completed for the entire sheet.
You will see what are the issues in your workbook, if there are any, make the required adjustments and you will be good to go.
2] Check Individual Sheet
The error in question may happen due to faulty sheets and you can resolve the issue by copying the content of each sheet into a new excel file. This way you will easily troubleshoot this error and this way you’ll know which sheet is causing the error.
3] Verify External Links
The external reference formula allows you to refer to the content of cells in another workbook and creates an external link. This means that your cell contains data that is part of another worksheet. However, these external links can become corrupted, inappropriate, or have some errors, hence causing the issue. To resolve this issue, follow the prescribed steps.
- Open the MS Excel application and go to the “Data” tab.
- Click on “Queries & Connections” and then click on the “Edit Links” option.
- Now check the external links and remove those link which is causing an error.
Hopefully, this method will work for you.
Read: How to convert currencies in Excel?
4] Review Charts
Sometimes, charts can also cause the issue and Excel users can review the charts to verify the cause of formula reference errors in Excel. However, if you are working on multiple and large Excel workbooks, then it will take a long time to find the culprit. Therefore, to save you time, we have mentioned below some of the specific locations:
- Horizontal axis formula inside the Select Data Source dialog box
- Secondary Axis
- Linked Data Labels, Axis Labels, or Chart Titles.
If you see an error at any of the mentioned locations, make the required adjustments and you will be good to go.
5] Repair MS Excel
If your error is still unsolved and you have used all the above methods then we recommend you reset or repair Excel. Microsoft also has a feature that instead of repairing the entire office, allows you to reset individual apps. However, we recommend you first try to repair the app, as we don’t want to reset any of the modifications and alterations you made to the app.
Hopefully, you will be able to resolve the issue using the solutions mentioned in this post.
