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 COMMAND + 1, and then select Number.

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×