Ways to avoid broken formulas

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for iPad, Excel for Android tablets, Excel Starter 2010.

This post will teach you about avoiding broken formulas and provide examples of error messages you will get in such cases. For example, if Excel is unable to repair a formula you’re attempting to create, you might receive an error message like this one:

Image of Excel's "Problem with this formula" dialogue for avoiding broken formulas.

Unfortunately, this means that Excel has no understanding of what you’re planning to do. So, you may simply want to exit and start afresh for avoiding broken formulas.

Begin by selecting OK or press ESC to dismiss the error message.

You’ll return to the cell with the broken formula, which will be in edit mode, and Excel will illuminate the spot where it’s experiencing an issue. If you’re still unsure of what to do from there and want to restart, you can press ESC again, or press the Cancel button in the formula bar, which will take you out of edit mode.

Image of the Formula Bar Cancel button

If you want to progress, then the following checklist offers troubleshooting steps to assist you in working out what may have gone wrong.

Are you seeing a pound (#) error?

Excel chucks several pound (#) errors like #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, and #NULL!, to alert you to an aspect of formula that is not operating properly. For instance, the #VALUE! error is attributable to incorrect formatting or unsupported data types in arguments. Or, you will notice the #REF! error if a formula refers to deleted or obsolete cells (that have been overwritten with other data). Troubleshooting guidance will vary for each error.

Note: #### is not a formula-related error. It merely means that the column is too narrow to present the cell contents. Simply drag the column to extend it, or select Home > Format > AutoFit Column Width.

Image of Home > Format > AutoFit Column Width

Refer to any of the following topics corresponding to the pound error you see:

There are broken links in the formula

Every time you access a spreadsheet that has formulas referring to values in other spreadsheets, you will be asked to update the references or leave them as-is.Broken references dialog box in Excel

Excel emerges the above dialogue box to ensure that the formulas in the current spreadsheet always direct to the most updated value in case the reference value has altered. You can decide to update the references, or skip if you want to leave them as they are. Even if you confirm to not update the references, you can always manually update the links in the spreadsheet anytime you prefer.

You can even disable the dialogue box from launching at start-up. To do that, visit File > Options > Advanced > General, and uncheck Ask to update automatic links.  This helps you with avoiding broken formulas.

Image of the Ask to update automatic link option

Important: If this is the first time you are working with broken links in formulas, need a refresher on resolving broken links, or you don’t know whether to update the references, see Control when external references (links) are updated.

The formula displays the syntax, and not the value

If the formula doesn’t display the value, follow these steps for avoiding broken formulas:

  • Make sure that the Excel will show formulas in your spreadsheet, by default. To do this, press the Formulas tab, and in the Formula Auditing group, pick Show Formulas.

Tip: You can even use the keyboard shortcut Ctrl + ` (the key above the Tab key). Once you do this, your columns will automatically widen to display your formulas, but don’t worry, when you switch back to normal view, your columns will resize.

  • If the above step still doesn’t fix the issue, it’s possible the cell is formatted as text. You can right-click on the cell and click Format Cells > General (or Ctrl + 1). Then, press F2 > Enter to modify the format.
  • If you have a broad set of cells in a column that are formatted as text, then you can pick the range, set the number format of your preference and go to Data > Text to Column > Finish. This will import the format to all of the selected cells.
Image of Data > Text to Columns dialog

The formula is not calculating

After a formula does not calculate, you must inspect if automatic calculation is enabled in Excel. Formulas will omit calculations if manual calculation is enabled. Follow these steps to check for Automatic calculation and avoiding broken formulas:

  1. Select the File tab, pick Options, and then choose the Formulas category.
  2. In the Calculation options section, below Workbook Calculation, ensure the Automatic option is chosen.Image of Automatic & Manual Calculation options

For further information on calculations in the context of avoiding broken formulas, see Change formula recalculation, iteration, or precision.

There are one or more circular references in the formula

A circular reference takes place when a formula refers to the cell that it is found in. The solution is to either relocate the formula to another cell or adjust the formula syntax, one that bypasses circular references. However, in some situations, you might require circular references because they cause your functions to iterate—recur until a distinct numeric condition is met. In such instances, you will have to enable Remove or allow a circular reference

For more details on circular references in the case of avoiding broken formulas, see Remove or allow a circular reference

Does your function start with an equal sign (=)?

If your entry starts with anything other than an equal sign, it isn’t a formula, and won’t be calculated—a frequent mistake.

When you input something like SUM(A1:A10), Excel displays the text string SUM(A1:A10) rather than a formula result. Alternatively, if you type 11/2, Excel presents a date, such as 2-Nov or 11/02/2009, as opposed to dividing 11 by 2.

To avoid these unwanted results, always begin the function with an equal sign for avoiding broken formulas. For example, type: =SUM(A1:A10) and =11/2.

Have you matched the opening and closing parentheses?

After you use a function in a formula, each opening parenthesis requires a closing parenthesis for the function to operate correctly, so check that all parentheses are linked to a matching pair. For example, the formula =IF(B5<0),”Not valid”,B5*1.05) will malfunction since there are two closing parentheses but only one opening parenthesis. The correct formula would take this form: =IF(B5<0,”Not valid”,B5*1.05).

Are all required arguments present in the syntax?

Excel functions have arguments—values you must supply for proper function. Just some functions (such as PI or TODAY) accept no arguments. Review the formula syntax that displays as you begin writing in the function, to guarantee that the function has the necessary arguments.

For instance, the UPPER function allows only one string of text or cell reference as its argument: =UPPER(“hello”) or =UPPER(C2)

Note: You will notice the function’s arguments listed in a floating function reference toolbar under the formula as you are entering it.

Screen shot of the Function Reference toolbar

Moreover, some functions, like SUM, permit numerical arguments only, while other functions, such as REPLACE, mandate a text value for a minimum of one of their arguments. If you apply the incorrect data type, functions may return unforeseen results or display a #VALUE! error.

If you must quickly look up the syntax of a particular function, see the list of Excel functions (by category).

Are there any unformatted numbers in formulas?

Don’t type numbers formatted with dollar signs ($) or decimal separators (,) in formulas, since dollar signs symbolise Absolute References and commas are argument separators. Rather than typing $1,000, input 1,000 in the formula.

If you use formatted numbers in arguments, you’ll receive inaccurate calculation results, but you might even notice the #NUM! error. For example, if you input the formula =ABS(-2,134) to capture the absolute value of -2134, Excel presents the #NUM! error, because the ABS function only takes one argument, and it registers the -2, and 134 as independent arguments.

Note: You can format the formula result with decimal separators and currency symbols following you typing the formula using unformatted numbers (constants). It’s usually poor practice to insert constants in formulas, because they can be difficult to uncover if you must amend them later, and they are more susceptible to being entered incorrectly. It’s far better to add your constants in cells, where they are widely available and easily referenced.

Do the referred cells belong to the correct data type?

Your formula may return different results than what you anticipated if the cell’s data type is incompatible with calculations. For example, if you type a simple formula =2+3 in a cell that’s formatted as text, Excel can’t calculate the data you stated. All you’ll observe in the cell is =2+3. To repair this, update the cell’s data type from Text to General like this:

  1. Pick the cell.
  2. Choose Home > arrow beside Number Format (or press Ctrl + 1), and select General.
  3. Press F2 to assign the cell in the edit mode. Then, press Enter to approve the formula.

A date you state in a cell that’s using the Number data type may be presented as a numeric date value rather than a date. To illustrate a number as a date, select a Date format in the Number Format gallery.

Are you trying to multiply without using the * symbol?

It’s mostly typical to use x as the multiplication operator in a formula, but Excel can only adopt the asterisk (*) for multiplication. If you use constants in your formula, Excel presents an error message and can repair the formula for you by substituting the x with the asterisk (*).

Message box asking to replace x with * for multiplication

However, if you use cell references, Excel will yield a #NAME? error.

#NAME? error when using x with cell references instead of * for multiplication

Are quotation marks missing around text in formulas?

If you produce a formula that contains text, surround the text in quotation marks.

For instance, the formula =”Today is ” & TEXT(TODAY(),”dddd, mmmm dd”) merges the text “Today is ” with the results of the TEXT and TODAY functions, and returns say something like Today is Monday, May 30.

In the formula, “Today is ” includes a space before the ending quotation mark to offer the blank space you want between the words “Today is” and “Monday, May 30.” Without quotation marks around the text, the formula could display the #NAME? error.

Are there more than 64 functions in a formula?

You can integrate (or nest) up to 64 levels of functions inside a formula.

For example, the formula =IF(SQRT(PI())<2,”Less than two!”,”More than two!”) contains 3 levels of functions: The PI function is nested within the SQRT function, which is then nested inside the IF function.

Are sheet names enclosed in single quotation marks?

Once you input a reference to values or cells in another worksheet, and the name of that sheet includes a non-alphabetical character (such as a space), embody the name in single quotation marks (‘).

For example, to return the value from cell D3 in a worksheet named Quarterly Data in your workbook, write: =’Quarterly Data’!D3. Excluding the quotation marks around the sheet name, the formula displays the #NAME? error.

Alternatively, you can select the values or cells in another sheet to refer to them in your formula. Excel subsequently adds the quotation marks around the sheet names.

If the formula references to an external workbook, is the path to the workbook referenced correctly?

Once you enter a reference to values or cells in another workbook, add the workbook name enclosed in square brackets ([]) followed by the worksheet name with the values or cells.

For example, to refer to cells A1 to A8 on the Sales sheet in the Q2 Operations workbook that’s open in Excel, enter: =[Q2 Operations.xlsx]Sales!A1:A8. Without the square brackets, the formula presents the #REF! error.

State the full path to the file for any closed workbooks in Excel.

For example, =ROWS(‘C:\My Documents\[Q2 Operations.xlsx]Sales’!A1:A8).

Note: If the full path has space characters, enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point).

Tip: The easiest way to get the path to the other workbook is to open the other workbook, then from your original workbook, type =, then use Alt+Tab to shift to the other workbook and select any cell on the sheet you want. Then close the source workbook. Your formula will automatically update to display the full file path and sheet name along with the required syntax. You can even copy and paste the path and use wherever you need it.

Are you dividing numeric values by zero?

Dividing a cell by another cell that has zero (0) or no value leads to a #DIV/0! error.

To overcome this error, you can resolve it in the affected cell and troubleshoot for the existence of the denominator.

=IF(B1,A1/B1,0)

Which says IF(B1 exists, then divide A1 by B1, otherwise return a 0).

Is the formula referencing to deleted data?

Always make sure to see if you have any formulas that refer to data in cells, ranges, defined names, worksheets, or workbooks, prior to you deleting anything. You can then replace these formulas with their results before you erase the referenced data thus avoiding broken formulas.

If you’re unable to replace the formulas with their results, check this information about the errors and potential solutions:

  • If a formula refers to cells that have been erased or replaced with other data and returns a #REF! error, pick the cell with the #REF! error. In the formula bar, click #REF! and delete it. Then, retype the range for the formula.
  • If a defined name is missing, and a formula that refers to that name returns a #NAME? error, apply a new name that refers to your desired range, or alter the formula to refer directly to the range of cells (for example, A2:D8).
  • If a worksheet is missing, and a formula that refers to it returns a #REF! error, there’s no solution to repair this, unfortunately—an erased worksheet is irretrievable.
  • If a workbook is missing, a formula that refers to it remains saved until you update the formula. For example, if your formula is =[Book1.xlsx]Sheet1′!A1 and Book1.xlsx has been replaced, the values referenced in that workbook stay accessible. However, if you amend and save a formula that refers to that workbook, Excel displays the Update Values dialogue box and requests you to type a file name. Press Cancel, and then check that this data isn’t misplaced by replacing the formulas that refer to the missing workbook with the formula results.

Have you copied and pasted cells associated to a formula in the spreadsheet?

Often, after you copy a cell’s contents, you want to paste only the value rather than the supplementary formula that is indicated in the formula bar.

For instance, you may want to copy the resulting value of a formula to a cell on another worksheet. Or you might want to remove the values that you used in a formula after you copied the resulting value to another cell on the worksheet. Both of these actions trigger an invalid cell reference error (#REF!) in the destination cell, because the cells that have the values that you used in the formula are no longer valid to be referenced.

You can evade this error by pasting the resulting values of formulas excluding the formula in destination cells.

  1. On a worksheet, click the cells with the resulting values of a formula that you want to copy.
  2. On the Home tab, in the Clipboard group, select Copy Button image .
Excel Ribbon Image

Keyboard shortcut: Press CTRL+C.

  1. Click the upper-left cell of the paste area.

Tip: To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.

  1. On the Home tab, in the Clipboard group, choose Paste Button image , and then pick Paste Values. Or alternatively, press Alt > E > S > V > Enter for Windows, or Option > Command > V > V > Enter on a Mac.

If you have a nested formula, evaluate the formula one step at a time

To acknowledge how a complex or nested formula calculates the final result, you can assess that formula.

  1. Pick the formula you want to evaluate.
  2. Choose Formulas > Evaluate Formula.
Formula Auditing group on the Formula tab
  1. Press Evaluate to inspect the value of the underlined reference. Italicised results indicate the result of the evaluation.
Evaluate Formula dialog box
  1. If the underlined part of the formula is a reference to another formula, select Step In to present the other formula in the Evaluation box. Press Step Out to return to the previous cell and formula. The Step In button is unavailable the second time the reference displays in the formula, or if the formula refers to a cell in another workbook.
  2. Continue until every aspect of the formula has been evaluated. The Evaluate Formula tool can aid you with where to look without strictly telling you the cause of your formula to break. This can be a very useful tool in broader formulas where it may otherwise be tough to source the problem.

Notes:

  • Some sections of the IF and CHOOSE functions will be skipped from evaluation, and the #N/A error could display in the Evaluation box.
  • Blank references are denoted as zero values (0) in the Evaluation box.
  • Functions that are recalculated constantly when the worksheet changes. Those functions, including the RANDAREASINDEXOFFSETCELLINDIRECTROWSCOLUMNSNOWTODAY, and RANDBETWEEN functions, can result in the Evaluate Formula dialogue box to present inconsistent results from the actual results in the cell on the worksheet.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Summary of formulas in Excel

Detect errors in formulas

Excel functions (alphabetical)

Excel functions (by category)

Leave a Reply

%d bloggers like this: