Why is my formula broken?

When formulas show unintended results or errors, it can be very frustrating, especially if you don’t know what to do next. A number of things can be wrong. For example, there might be mismatched parentheses, typing mistakes, or references to data in ranges that don't exist.

If Excel doesn't accept a formula you're trying to create, you may get an error message like this one:

Error message about broken formulas

To remove the broken formula, do the following:

  1. Click OK to close the error message.

    You'll return to the cell with the broken formula, which is still in edit mode.

  2. Press Backspace to remove the formula, or click the Cancel button in the formula bar.

    Cancel button on the formula bar

The following best practices can help you prevent or resolve common mistakes when you build or need to correct your formulas.

In this article

Start every function with an equal sign (=)

Match all opening and closing parentheses

Enter all required arguments

Use unformatted numbers in formulas

Use the right cell data type

Use the * symbol to multiply numbers

Use quotation marks around text in formulas

Don’t nest more than 64 functions in a formula

Enclose sheet names in single quotation marks

Include the path to external workbooks

Avoid dividing numeric values by zero

Avoid deleting data that’s referenced in formulas

Watch formulas and their results

Evaluate complex formulas one step at a time

Use error checking to detect errors

Start every function with an equal sign (=)

If your entry doesn’t start with an equal sign, it isn’t a formula and won’t be calculated—a common mistake that’s easy to make. When you type something like SUM(A1:A10), Excel shows the text string SUM(A1:A10) instead of a formula result. If you type 11/2, Excel shows a date, such as 2-Nov or 11/02/2009, instead of dividing 11 by 2. To avoid these unexpected results, always start with an equal sign. For example, type: =SUM(A1:A10) and =11/2

Top of Page

Match all opening and closing parentheses

Make sure that all parentheses are part of a matching pair. When you use a function in a formula, each opening parenthesis needs a closing parenthesis for the function to work correctly. For example, the formula =IF(B5<0),"Not valid",B5*1.05) won’t work because there are two closing parentheses but only one opening parenthesis. The correct formula would look like this: =IF(B5<0,"Not valid",B5*1.05).

Top of Page

Enter all required arguments

Most functions have required arguments—values you need to provide for the function to work. Only a few functions (such as PI or TODAY) take no arguments. Check the formula syntax that appears when you start typing an equal sign followed by a function to make sure you enter all required arguments—no more and no less.

For example, the UPPER function accepts only one string of text or cell reference as its argument: =UPPER("hello") or =UPPER(C2)

Also, some functions, such as SUM, require numerical arguments only, while other functions, such as REPLACE, require a text value for at least one of their arguments. If you use the wrong data type, some functions might return unexpected results or show a #VALUE! error.

Top of Page

Use unformatted numbers in formulas

Don't enter numbers formatted with dollar signs ($) or decimal separators (,) in formulas, because dollar signs indicate absolute references and commas are argument separators. Instead of entering $1,000, enter 1000 in the formula.

If you use formatted numbers in arguments, you’ll get unexpected calculation results, but you may also see the #NUM! error. For example, if you enter the formula =ABS(-2,134) to find the absolute value of -2134, Excel shows the #NUM! error, because the ABS function accepts only one argument.

You can format the formula result with decimal separators and currency symbols after you enter the formula using unformatted numbers.

Top of Page

Use the right cell data type

Your formula might not return expected results if the cell’s data type can’t be used in calculations. For example, if you enter a simple formula =2+3 in a cell that’s formatted as text, Excel can’t calculate the data you entered. All you'll see in the cell is =2+3. To fix this, change the cell’s data type from Text to General like this:

  1. Select the cell.

  2. Click Home > arrow next to Number Format, and pick General.

  3. Press F2 to put the cell in to Edit mode, and press Enter to accept the formula.

A date you enter in a cell that’s using the Number data type might be shown as a numeric date value instead of a date. To show this number as a date, pick a Date format in the Number Format gallery.

Top of Page

Use the * symbol to multiply numbers

You may want to use x as the multiplication operator in a formula, but Excel uses the asterisk (*) instead. When you use an x in your formula, Excel shows an error message and can fix the formula for you by replacing the x with the * symbol.

Top of Page

Use quotation marks around text in formulas

If you create a formula that includes text, enclose the text in quotation marks.

For example, the formula ="Today is " & TEXT(TODAY(),"dddd, mmmm dd") combines the text "Today is " with the results of the TEXT and TODAY functions, and returns Today is Monday, May 30 in the cell.

In the formula, "Today is " has a space before the ending quotation mark to provide the blank space you want between the words "Today is" and "Monday, May 30." Without quotation marks around the text, the formula might show the #NAME? error.

Top of Page

Don’t nest more than 64 functions in a formula

You can combine (or nest) up to 64 levels of functions within a formula.

For example, the formula =IF(SQRT(PI())<2,"Less than two!","More than two!") has 3 levels of functions: The PI function is nested inside the SQRT function, which is in turn nested inside the IF function.

Top of Page

Enclose sheet names in single quotation marks

When you type a reference to values or cells in another worksheet, and the name of that sheet has a non-alphabetical character (such as a space), enclose the name in single quotation marks (').

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

You can also click the values or cells in another sheet to refer to them in your formula. Excel then automatically adds the quotation marks around the sheet names.

Top of Page

Include the path to external workbooks

When you type a reference to values or cells in another workbook, include the workbook name enclosed in square brackets ([]) followed by the name of the worksheet that has the values or cells.

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

If the workbook isn’t open in Excel, type the full path to the file.

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).

Top of Page

Avoid dividing numeric values by zero

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

To avoid this error, nest your division operation inside the IFERROR function. For example, enter the following two formulas in cells in a worksheet:

=IFERROR(3/0,0)

=IFERROR(3/7,0)

The first formula returns 0 because the IFERROR function recognizes that dividing by zero results in an error. The second formula returns the value of 3/7—there’s no error because you're not dividing by zero.

Top of Page

Avoid deleting data that’s referenced in formulas

Always check to see if you have any formulas that refer to data in cells, ranges, defined names, worksheets, or workbooks, before you delete anything. You can then replace these formulas with their results before you remove the referenced data.

If you can’t replace the formulas with their results, review this information about the errors and possible solutions:

  • If a formula refers to cells that have been deleted or replaced with other data and returns a #REF! error, select the cell with the #REF! error. In the formula bar, select #REF! and delete it. Then, reenter the range for the formula.

  • If a defined name is missing, and a formula that refers to that name returns a #NAME? error, define a new name that refers to the range you want, or change 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 way to fix this, unfortunately—a worksheet that’s been deleted can't be recovered.

  • If a workbook is missing, a formula that refers to it remains intact until you update the formula.

    For example, if your formula is =[Book1.xlsx]Sheet1'!A1 and you no longer have Book1.xlsx, the values referenced in that workbook stay available. However, if you edit and save a formula that refers to that workbook, Excel shows the Update Values dialog box and prompts you to enter a file name. Click Cancel, and then make sure that this data isn’t lost by replacing the formulas that refer to the missing workbook with the formula results.

Top of Page

Watch formulas and their results

If your worksheet is large, you can use the Watch Window to inspect, audit, or confirm formula calculations and results without having to constantly scroll to different parts of the worksheet.

  1. Select the cells with the formula you want to watch.

  2. Click Formulas > Watch Window.

Formula Auditing group on the Formula tab

  1. In the Watch Window, click Add Watch.

    Add Watch button in the Watch Window

  2. In the Add Watch box, click Add.

    Add Watch dialog box

  3. To dock the Watch Window above, below, or next to your worksheet, drag it to the top, bottom, left, or right side of the worksheet window.

  4. To change the width of a column, drag the boundary on the right side of the column heading.

  5. Double-click sheet and cell entries in the Watch Window to go to their location in the worksheets of workbooks you’ve got open in Excel.

Note    Formulas with external references to other workbooks are shown in the Watch Window only when those workbooks are open in Excel.

Top of Page

Evaluate complex formulas one step at a time

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

  1. Select the formula you want to evaluate.

  2. Click Formulas > Evaluate Formula.

    Formula Auditing group on the Formula tab

  3. Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.

    Evaluate Formula dialog box

  4. If the underlined part of the formula is a reference to another formula, click Step In to show the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.

The Step In button isn’t available the second time the reference appears in the formula, or if the formula refers to a cell in another workbook.

  1. Continue until each part of the formula has been evaluated.

    Notes   

    • Some parts of the IF and CHOOSE functions won’t be evaluated, and the #N/A error might appear in the Evaluation box.

    • Blank references are shown as zero values (0) in the Evaluation box.

    • Functions that are recalculated every time the worksheet changes. Those functions, including the RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, and RANDBETWEEN functions, can cause the Evaluate Formula dialog box to show results that are different from the actual results in the cell on the worksheet.

Top of Page

Use error checking to detect errors

You can detect and fix errors in your formulas by using rules to check for formula errors, a bit like using a spelling checker. The rules don't guarantee error-free worksheets, but they can help find common mistakes.

For more information on using error checking, see Use error checking to detect errors in formulas.

Top of Page

Applies To: Excel 2010, Excel Starter



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language