How-To

Excel Found a Problem With One or More Formula References: How to Fix

By Adam Davidson

Published October 20, 2022

You’re trying to save your spreadsheet when you get an error that Excel found a problem with one or more formula references. Here’s how to fix it.

You’re trying to save the Excel file you’ve worked so hard on, but an error message pops up: Excel found a problem with one or more formula references.

This means that lurking somewhere within your spreadsheet, there’s something wrong with one of your formulas. If your spreadsheet is large, trying to find it may not be a quick task.

Thankfully, there are some fixes you can try to find the root of the problem and ensure that the ‘Excel found a problem with one or more formula references’ error no longer appears.

Use the Error Checking Tool

One of the quickest ways to find errors with your Excel spreadsheet is to use the Error Checking tool. If the tool finds any errors, you can correct them and try saving your file again. The tool may not find every type of error, however.

If the Error Checking tool doesn’t find any errors or fixing them doesn’t solve the problem, then you’ll need to try one of the other fixes below.

To use the Error Checking tool in Excel:

1. Select the Formulas tab.
   
2. Click the Error Checking tool.
   
3. In an error is found, information about the cause of the error will appear in the tool.
   
4. Either fix the error or delete the formula that’s causing the issue if it’s not needed.
5. In the Error Checking tool, click Next to see the next error and repeat the process.
   
6. When no more errors are found, you’ll see a pop-up informing you of this fact.
   
7. Repeat the process for each sheet in your document.
8. Try saving the document again to see if the formula references error has been fixed.

Find the Problematic Worksheet

If the Error Checking tool doesn’t find the cause of your problem, then you’ll need to start hunting for yourself.

For example, if have a document with multiple worksheets, you’ll need to narrow down the problem to the single sheet (or multiple worksheets) causing the issue.

To find the sheet that causing the formula reference error:

1. Open the first sheet in your spreadsheet.
2. Click Ctrl+A on Windows or Cmd+A on Mac to select the entire sheet. Alternatively, you can click the button at the very top left of your spreadsheet.
   
3. Right-click and select Copy.
4. Alternatively, use the keyboard shortcut Ctrl+C on Windows or Cmd+C on Mac.
   
5. Next, open the File menu.
   
6. Select Blank Workbook.
   
7. Right-click in cell A1 and select the Paste icon.
8. Alternatively, use the keyboard shortcut Ctrl+V on Windows or Cmd+V on Mac.
   
9. Try to save your new file. If you can save the file with no errors, then that specific sheet does not contain the source of your error.
10. Repeat with each sheet in your document until you find the sheet or sheets that are causing the problems.

Search For Errors

Now that you know which sheet or sheets contain the cause of your issues, you can search them to track down any errors that they may contain.

The simplest way to do so is to search for any formulas that contain a # symbol. That’s because this is the symbol used at the start of formula error messages.

To search for errors in your Excel sheet:

1. Open the sheet that you have found contains potential errors.
2. Click Ctrl+F on Windows or Cmd+F on Mac to open the search tool.
3. Type # in the Find What field and ensure that Look In is set to Values.
   
4. Click Find All to find all instances of a cell containing a hashtag.
   
5. If any instances are found, they will appear in the search tool. You can click on each one to go to that cell and make any necessary changes, or simply delete the formulas if you don’t need them.
   
6. Repeat the process for any other sheets that you believe may contain errors.
7. Try saving your document again to see if this has resolved the problem.

Check Your Charts

The ‘Excel found a problem with one or more formula references’ error can also often be caused by invalid references for your charts. Fixing these references may solve the problem.

To check the source data for your Excel charts:

1. Open the sheet containing your chart.
2. Right-click on the chart and click Select Data.
   
3. Confirm that the Chart Data Range is correct and refers to a valid range of cells that contain data.
   
4. If it doesn’t, edit the range to refer to the correct cells, or delete the chart if you no longer need it.
5. Try saving your Excel document again to see if the error still occurs.

Check External Links

If your Excel worksheet references an external spreadsheet that you’ve since removed or renamed, you might see this error appear.

Thankfully, there is a tool in Excel that can help you to check the status of any external links.

To check the validity of external links in Excel:

1. Click the Data menu.
   
2. In the Queries & Connections section of the ribbon, click the Edit Links tool.
   
3. You’ll see a list of the external links in your document, with the status listed as Unknown.
   
4. Click the Check Status button.
   
5. The status of your links will update.
   
6. If one of your links is showing an error, the source file cannot be found. You can update the location of the source file by clicking Change Source and selecting the file you want to reference.
   
7. If you don’t need to refer to the other document any longer, you can delete the cell containing the external link.
8. If a lot of cells refer to a document that is no longer accessible, you can remove the link from the document entirely by clicking on that link and selecting click Break Link. This should be done with caution, as you will lose all of your external references to this link.
   
9. You’ll need to confirm your decision by clicking Break Links.
   
10. Once you fix or delete any rogue links, try saving your document again.

Check Your Pivot Tables

Another common source of reference errors in Excel is pivot tables. These types of tables are incredibly powerful, but they can also be the cause of errors if the references are no longer valid.

To check your pivot tables for errors in Excel:

1. Open the sheet containing your pivot table.
2. Select the pivot table.
3. On the ribbon bar, click the PivotTable Analyze menu that appears.
   
4. In the ribbon, click Change Data Source.
   
5. Check the Table/Range to ensure that it refers to a valid range of cells.
   
6. If it doesn’t, edit the table/range values, or if you don’t need it, delete the pivot table.
7. Try saving your document again.

How to Fix Common Excel Problems

If you see the ‘Excel found a problem with one or more formula references’ error message pop up, one of the fixes above will hopefully solve the problem. If you find any other ways to stop the error from occurring, then please let us know in the comments below.

Learning how to fix some of the most common Excel errors can ensure you spend more time working and less time pulling your hair out. It’s useful to know how to find a circular reference in Excel, for example.

If Excel keeps crashing, there are some useful fixes that you can try. If you want to ignore any problems in your worksheet, you can choose to ignore all errors in Excel instead.