When saving the Excel workbook, you can encounter the “Excel found a problem with one or more formula references in this sheet” error. Due to this, you’re not able to save the workbook. The complete error message appears as:
“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.”
This error usually occurs when the Excel application fails to read the formulas used in the Excel workbook. This Excel formula reference error can appear due to incorrect cell references, corruption in the Excel workbook, or any other formula-related issues in the workbook. The error can also occur in Excel due to:
- Incorrect references to a wrong cell.
- Invalid range or object linking.
- Issues with cell range (for example, cells might be empty).
- Incorrect formulas in the cell reference.
This article will cover the troubleshooting methods to effectively fix the error.
Contents
Methods to Fix “Excel Found a Problem with One or More Formula References in this Worksheet” Error
Here are some methods you can try to resolve the “Excel found a problem with one or more formula references” error.
Method 1: Check Individual Sheets
If you are getting this error while saving a workbook with multiple sheets, then first try to find out if the issue is related to an individual sheet. Checking individual sheets can simplify the troubleshooting process. To do this, you can copy the content of each sheet into a new Excel file and then try to save the file. When you find the faulty sheet, you can take the backup of the sheet’s content and remove the sheet from the workbook.
Method 2: Evaluate Formulas in your Excel File
Any issue with formula references can also lead to this error. You can check the formulas in the workbook. If you have a large Excel file with multiple formulas, finding the problematic formulas in the cell becomes quite complicated. In such a situation, you can use the error-checking option. This option helps identify and fix the issues with formulas in the Excel worksheet. To run the error-checking function in Excel, follow the below steps:
- On the Excel ribbon, navigate to
- Click the Error Checking
- This will scan the complete sheet to find the issue in the formulas. If there is no problem in the sheet, then you will see the error check is complete message.
- Click OK and try to save the file.
Method 3: Check External Links in Excel File
You can encounter the “Excel found a problem with one or more formula references in this worksheet” error due to incorrect external links in the Excel file. These problematic external links can affect the formulas and references within the Excel file and cause formula-related errors. Follow the below steps to verify the external links in your workbook:
- Go to the Data tab and then click Edit Links.
- Verify the links and remove the faulty links, if any. Then, try to save the worksheet.
Method 4: Check Pivot Tables
The “Excel found a problem with one or more formula references in this worksheet” error message can also occur when working on Pivot tables. It usually occurs when there are issues with formulas, cell data references, or calculations performed by the pivot tables. Ensure you follow Pivot Tables’ correct formula syntax rules and behavior. To check your Pivot tables, follow these steps:
- In the workbook, go to PivotTable Tools and click the Analyze
- Select Change Data Source and then click Change Data Source.
- Now, check and change the problematic formulas, if any.
Method 5: Repair your Excel File
If none of the above methods works, then the Excel file might be corrupted. To repair the corrupted (.xls/.xlsx) file, you can use Excel’s built-in utility – Open and Repair. Here’s how this utility works:
- In the Excel application, go to File and click
- Click Browse to select the problematic workbook.
- In the Open dialog box, select the file in which you’re getting the error.
- Click the arrow next to the Open button and then click Open and Repair.
- Click Repair to recover as much data as possible.
- When the repair process is complete, the application displays a completion message. Click Close.
If the Excel file is severely corrupted, then the Open and Repair utility may fail to fix the issue. In such a case, you can try a professional Excel repair tool. Stellar Repair for Excel is one such tool that is recommended by experts to repair highly corrupted or damaged Excel files. The tool has an intuitive interface that makes it easy to repair the Excel files. It recovers all the data from the corrupted Excel files with complete integrity.
To Conclude
The ‘Excel found a problem with one or more formula references in this worksheet’ error can occur due to different reasons, primarily related to issues with the formulas in the Excel file. You can follow the methods discussed above to fix the error. The error can also occur due to corruption in the Excel file. To repair the file, you can try Stellar Repair for Excel. It can easily repair Excel files and recover all the data, including pivot tables, formulas, etc., without changing the original formatting. You can download the tool’s demo version to scan the file and preview the recoverable data.