Excel finds a problem with one or more formula references as you attempt to save the Excel file you’ve worked so hard on. This indicates a flaw in one of your spreadsheet’s formulas. Finding anything in a huge spreadsheet could take some time. Fortunately, there are certain fixes you may attempt to find the cause of the problem and ensure that the Excel found a problem with one or more formula references error no longer occurs.
How To Easily Fix Excel Found A Problem With One Or More Formula References?
Use Error Checking Tool
Using the Error Checking tool is one of the easiest methods to find errors in your Excel spreadsheet. If the tool detects any errors, you may correct them and attempt to save your file again. However, the tool may not find all errors. If the Error Checking tool does not find any errors or if fixing them does not solve the problem, you will need to attempt one of the additional fixes listed below. To use Excel’s Error Checking tool.
1 – Choose the Formulas tab.
2 – Click the Error Checking tool.
3 – In an error is found, information regarding the cause of the error will appear in the tool.
4 – Either fix the error and/or delete the formula causing the problem if it’s unnecessary.
5 – In the Error Checking tool, click Next to view the next error and repeat the process.
6 – A pop-up will advise you when no further errors are discovered.
7 – On each sheet in your document, repeat the process.
8 – If the formula references error has been fixed, save the document again.
Find Problematic Worksheet
If the Error Checking tool fails to find the source of your problem, you will need to begin your investigation. For instance, if you have a document with numerous workbooks, you must isolate the problem to the single sheet (or multiple workbooks) causing the issue. To find the formula reference error-causing sheet.
1 – Sheet 1 of your spreadsheet should be open.
2 – Select the entire sheet from Ctrl+A on Windows and/or Cmd+A on Mac. Instead, click the button in your spreadsheet’s upper-left corner.
3 – Right-click and choose Copy.
4 – Use the keyboard shortcut Ctrl+C on Windows and/or Cmd+C on a Mac instead.
5 – Open the File menu next.
6 – Choose Blank Workbook.
7 – Right-click on cell A1 and choose the Paste icon.
8 – Use the keyboard shortcut Ctrl+V on Windows and/or Cmd+V on a Mac instead.
9 – Attempt to save your new file. If the file can be saved without errors, then that particular sheet does not contain the error.
10 – Once you find the sheet or sheets causing the problems, repeat each sheet in your document.
Search For Errors
Now you know which sheet or sheets contain the issue; you may search for any errors they may have. A great way to do so is to search for formulas containing a # symbol. This is because formula error messages begin with this symbol. To search for errors in an Excel sheet.
1 – Open the sheet that you discovered that includes probable errors.
2 – To open the search tool, use Ctrl+F on Windows or Cmd+F on a Mac.
3 – Enter # in the Find What field, and ensure that the Look In the drop-down menu is selected to Values.
4 – Click Find All to find all instances of a cell with a hashtag.
5 – The search tool will display any instances that are discovered. If you don’t need the formulas, you can delete them or click each to get to that cell and make any required adjustments.
6 – Repeat the process for any more sheets you suspect have errors.
7 – Retry saving your document to check whether the problem has been resolved.
Check Charts
Invalid references for your charts are another common source of Excel found a problem with one or more formula references error. Fixing these references may fix the problem. To verify the data sources for your Excel charts.
1 – Open the chart sheet.
2 – Right-click the chart and then click Select Data.
3 – Verify that the Chart Data Range is correct and relates to a valid range of cells that contain data.
4 – If it doesn’t, edit the range to relate to the correct cells or delete the chart if you no longer want it.
5 – If the error persists, try saving your Excel document again.
Check External Links
This error may happen if your Excel spreadsheet references an external spreadsheet you have since deleted or renamed. Fortunately, Excel has a tool that can check the status of any external links. To validate external links in Excel.
1 – Click the Data menu.
2 – Click the Edit Links tool in the Queries & Connections area of the Ribbon.
3 – In your document, you’ll notice a list of external links with the status labeled Unknown.
4 – Click the Check Status button.
5 – Your links’ status will update.
6 – The source file cannot be located if one of your links displays an error. By choosing Change Source and selecting the file you want to reference, you may update the source file’s location.
7 – If you no longer need to refer to the other document, you may delete the cell holding the external link.
8 – If several cells refer to a document that is no longer available, you can completely delete the link from the document by selecting that link and then choosing Break Link. This should be done cautiously since you will lose any external references to this link.
9 – You must confirm your choice by clicking Break Links.
10 – After fixing or deleting any broken links, try saving your document again.
Check Pivot Tables
Pivot tables are another major cause of reference errors in Excel. These tables are tremendously powerful but can also generate errors if the references are no longer valid. So check the pivot tables for errors in Excel.
1 – Open the pivot table sheet.
2 – Choose the pivot table.
3 – Click the PivotTable Analyze option that appears on the ribbon bar.
4 – Click Change Data Source in the Ribbon.
5 – Check the Table/Range to ensure it refers to an appropriate range of cells.
6 – If it doesn’t, edit the table/range values or delete the pivot table if you don’t need it.
7 – Try saving the document again.
Conclusion:
One of the fixes listed above should hopefully solve the problem if you see the error notice “Excel found a problem with one or more formula references.” Let us know in the comments below if you find any further techniques to prevent the error from occurring.
