How to avoid broken formulas

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. The following best practices can help you prevent or resolve common mistakes when you build or need to correct your formulas.

Use this best practice:

To avoid this common mistake:

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.

For example, when you type something like SUM(A1:A10), Excel Online shows the text string SUM(A1:A10) instead of a formula result. If you type 11/2, you’ll see 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

Match all opening and closing parentheses

Make sure that all parentheses are part of a matching pair. In a formula that uses a function, 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).

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.

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. Formatted numbers aren’t used in the formula calculation. 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 Online 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 by applying a number format.

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 Online 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 > Number Format arrow, and pick General.

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

A date you enter in a cell that has 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.

Use the * symbol to multiply numbers

You may want to use x as the multiplication operator in a formula, but you need to use the asterisk (*) instead.

When you use an x in your formula, Excel Online can’t calculate the result and shows the formula in the cell instead, placing a dotted red border around the cell to let you know there’s a formula error.

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.

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.

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:



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.

To make it easier to create your formulas, you may also want to learn about these formula tips and tricks.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!