How to fix formula errors in Excel 2016 for Mac

Whether it's #VALUE!, #NAME! or a problem with VLOOKUP, the following information can help you correct your error.

VLOOKUP

If you're getting errors or unexpected results with VLOOKUP, download the VLOOKUP troubleshooting tips quick reference.

#VALUE! error

The source of this problem is usually related to having a mix of numeric values and text values. For more information, see Correct a #VALUE! error.

#NAME? error

Make sure you type names correctly, enclose any text in quotation marks, or enclose sheet names in single quotation marks ('). For other causes and solutions, see #NAME? error.

#NUM! error

This can happen if you typed extra characters in a formula. For example, don't type $1,000 in a formula. Instead, enter 1000. For other causes and solutions, see Correct a #NUM! error.

#N/A error

If a formula can't find a referenced value, it returns the #N/A error. For more information, see Correct a #N/A error.

#REF! error

Excel shows this error when a formula contains a cell reference that isn't valid. For more information, see Correct a #REF! error.

#DIV/0! error

This happens when a number is divided by zero (0), or when a formula refers to a cell that has 0 or is blank. For more information, see #DIV/0! error.

Referencing other sheets and workbooks

If you are referencing another worksheet name, type ! after the name, and then type a cell or range. If the sheet name has spaces, enclose the name in single quotation marks. For example: =SUM('Sales Report'!A1:A8).

When referencing another external workbook:

• Enclose the workbook name in square brackets [ ].

• Type the full path to the file.

• Enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point).

  Example: =SUM('/Users/yourname/Desktop/[Q2 Operations.xlsx]Sales'!A1:A8)

Other quick solutions that might help

• Start every formula with an equal sign (=). Example: =SUM(A1:A8)

• Use the * symbol to multiply numbers, not an X. Example: =A1*A8

• Match all opening and closing parentheses so that they are in pairs. This example has 2 pairs: =IF(40>50,SUM(G2:G5),0)

• Enter all required arguments. The Formula Builder can help you with this. Start typing a formula with a function name, and then press CONTROL + A to see the Formula Builder.

• Use quotation marks around text in formulas. Example: =IF(A2>B2,"Over Budget","OK")

• Change a referenced cell's data type. Press + 1, and then select Number.