Categorized | excel formula, excel tips

Excel #N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM! Error

Have you ever encountered the #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors in Excel? Learn more about these errors and how to avoid them in simple terms.

Why does Excel show #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors?

#NAME? Error

The #NAME error is generated when a cell tries to reference a non-existent cell call a non-existent function. If you enter a formula that does not exist in Excel, say =getmeburger(), you will get the #NAME? error. Check if the function name has been spelled correctly and that the formula is a valid formula.

#REF! Error

When a cell tries to reference a cell that can’t be located on the spreadsheet either due to deletion or having been overwritten by pasting by a user or a VBA code, it can result in a #REF! error. If you get the #REF Error, try checking if the cell is using a non-existent cell.

#DIV/0! Error

If the denominator in a fraction evaluates to 0, Excel will show the #DIV/0 error. Copy the denominator separately to another cell and check out why is causing to evaluate to 0.

#VALUE! Error

An invalid value or wrong number of values being specified as argument to a function can cause a cell to show the #VALUE! error. For example entering =if(“a”,1,0) will result in a #VALUE! error. Check for arguments to the function and see if they are of the same format (text, number) that the formula expects.

#NUM! Error

Entering a non-numeric argument to a function that expects a numeric value. A function or a value that evaluates to a numeric value beyond the Excel’s capabilities to handle. Result of a function that could not find a valid value in spite multiple iterations.

#N/A Error

A generic error code indicating all other result code not represented by any of the above error codes. Indicates missing data, non-existent function name, providing wrong values as arguments to functions etc. Most frequently indicates that the value that you are trying to find/match does not exist.

How to avoid printing errors In Excel

Select the worksheet that you would like to print. Click Page Setup on the menubar and then click the Sheet tab. Select the Print tab and in the Cell errors box, chose . This will prevent errors from printing.

How to hide display of errors in Excel

Select a cell (Let’s take cell A1 for example). Click on ‘Format’ -> ‘Conditional Formatting’ on the menubar. Choose ‘Formula Is’ and enter =ISERROR(A1) as formula in the next box. Click on format and choose white as the font color. Click ok. This will prevent errors from showing up when users go through your workbook. Apply the same format to all cells that can result in an error.

How to check errors in Excel

Simply enclose the formula that can potentially generate an error in an =ISERROR(condition) formula. Use it along with the IF function like =IF(ISERROR(condition),””,condition). This way, even if an error occurs, the cell will show nothing though it would work well under all other conditions.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. norivil wrote:

    how to solve this =D13/C13 IN EXCEL AND WHAT IS THE ANSWER PLEASE .

  2. Guest wrote:

    Simpler way to hide errors:


  3. Vijay wrote:

    Thank you! Good article.

  4. Hareendran E wrote:

    Fantastic information which I can not find anywhere else. Thank you very much.

  1. How To Fix Excel Max Value Error in Windows wrote:

    […] Excel #N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM! Error … – Have you ever encountered the #N/A, #REF!, #NAME?, #DIV/0!, #NULL!, #VALUE! or #NUM! errors in Excel? Learn more about these errors and how to avoid them in …… […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel