How-To

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

Excel

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.
    excel formula menu
  2. Click the Error Checking tool.
    excel error checking
  3. In an error is found, information about the cause of the error will appear in the tool.
    excel references error
  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.
    excel next error
  6. When no more errors are found, you’ll see a pop-up informing you of this fact.
    excel error check complete
  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.
    excel select all
  3. Right-click and select Copy.
  4. Alternatively, use the keyboard shortcut Ctrl+C on Windows or Cmd+C on Mac.
    excel copy
  5. Next, open the File menu.
    excel file menu
  6. Select Blank Workbook.
    excel 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.
    excel paste
  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.
    excel find
  4. Click Find All to find all instances of a cell containing a hashtag.
    excel find all
  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.
    excel found errors
  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.
    excel select data
  3. Confirm that the Chart Data Range is correct and refers to a valid range of cells that contain data.
    excel data range
  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.
    excel data menu
  2. In the Queries & Connections section of the ribbon, click the Edit Links tool.
    excel edit links
  3. You’ll see a list of the external links in your document, with the status listed as Unknown.
    excel current links
  4. Click the Check Status button.
    excel check status
  5. The status of your links will update.
    excel link status
  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.
    excel change source
  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.
    excel break link
  9. You’ll need to confirm your decision by clicking Break Links.
    excel 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.
    excel pivot analyze
  4. In the ribbon, click Change Data Source.
    excel change data source
  5. Check the Table/Range to ensure that it refers to a valid range of cells.
    excel table range
  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.

Click to comment

Leave a Reply

Your email address will not be published.

 

To Top