Understanding, Detecting and Correcting Excel errors – #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!

One way to gauge how successful you are is to know how much you have learnt from your mistakes. But learning cannot happen unless you not only identify the mistake but also admit it as only then you can find a way to fix it. And this second part is often the difficult one i.e. finding and fixing.

You are not an avid Excel user unless you hit a point where even Excel goes hash tags on you!

What I mean to say is that if you are a hardcore excel-ler then you better know what excel errors are. Good thing is that excel has a unique way to tell if you hit a tipping point. In this article we will discuss different types of errors that you might run into the reason why they occur and how to solve them and definitely with bonus tips.

We can broadly categorise errors in two:

Formatting errors:

This category has only one type of error code named “######” error. Yup seriously! Its all hashes.

Formula errors:

This category has the following types of errors:

1. #DIV/0!
2. #N/A
3. #NAME?
4. #NULL!
5. #NUM!
6. #REF!
7. #VALUE!

Remember!

In total we have 8 different types of errors. Hold on a second. You can make different types of errors but there are only 8 of them where excel will jump in to save you by notifying in cell. Simply put if you make an error like 2-2 instead of 2+2 then Excel won’t know that you have mistakenly used a wrong operator.

###### error

Reason 1: This usually happens when Excel is unable to display the number with specified format as columns are not wide enough.

Solution:

1. Double click on the right margin of the respective column to make the column wide enough so that number can be displayed properly.
2. Change the number format For example use either number, general or scientific

Reason 2: A negative number (serial number) is formatted as Date or Time. If you are using 1900 Date system then underlying serial number needs to be positive. This can also happen if two time periods are deducted in a way that result a negative value.

Example 1:

If cell A1 has 7/7/1983 and cell B1 has 8/6/1986 then following formula will result in a negative value:

=A1-B1

The value is -1126 and if you try to format this figure in date format this will result in ##### error.

Example 2:

If cell A2 has 6:00 and B2 has 20:00 then using =A2-B2 will result in a negative figure i.e. -0.58333 and thus ###### error if formatted in any of the time related format.

Solution:

1. Alter the formula if possible. For example using a formula =B1-A1 instead of the one mentioned above will result in 1126. Similarly in example 2 if =B2-A2 is used then you get a result 0.58333 and applying a time format will give 14:00 that means 14 hours difference to be exact.
2. Problem is negative sign to be exact. If you are not sure about time data in the range then you can take help of relevant functions. If you wrap =A1-B1 in ABS() function as =ABS(A1-B1) then it will churn out positive figure all the time and thus can be formatted in time correctly. But remember ABS() function is not a solution in all cases.

Reason 3: If you try to format a number that is outside time/date range of Excel then again ##### error will occur. For example if you try to format 77677667677766 in date then it will give ####### error.

Solution: What I found myself is that you cannot format a number greater than 2958465.99999 in Date/Time format. Any number greater than this number will result in ###### error.

#DIV/0! error

Reason 1: In cell a number is divided by zero. For example =21/0 will result in #DIV/0! error.

Reason 2: A formula is constructed in a way that denominator ends up being zer0. For example cell A1 has 100 and B1 has 0 then =A1/B1 will result in #DIV/0! error.

Remember!

Excel considers empty or blank cell as zero and thus dividing a number with blank cell will result in #DIV/0! error. For example if A3 has 200 and B3 is empty then =A3/B3 will result in error.

Solution:

1. Avoid dividing a number by zero. Avoid having a fraction with a denominator that solves to zero.
2. Take due care in using blank cells in formula.
3. If it cannot be avoided then modify your formula in such a way that in case of error excel displays an appropriate number, an empty cell or text message.
   Suppose A3 has 200 and B3 is empty:
   =IF(E20=0,””,F12/E20) this formula checks if cell E20 is blank or has zero then display nothing. Otherwise divide the value in cell F12 over the value in cell E20.
   =IF(ISERROR(F12/E20),””,F12/E20) using this formula will give an empty cell if cell E20 is blank or has 0 in it.
   =IF(ISERROR(F12/E20),”Check”,F12/E20) will display a text message Check if cell cell E20 is blank or has 0.
   =IFERROR(F12/E20,”Check”) this formula divides F12’s value over E20’s value but if there is an error then it displays a message Check.

#N/A errors

Simply put its “Not Available”. Meaning that something is missing.

Reason 1: This is a common sight if LOOKUP functions like HLOOKUP or VLOOKUP can’t find a match because:

1. the matching item is missing or not in the range specified
2. a blank cell is referenced as lookup value argument.

Reason 2: a cell containing #N/A error is referenced in formula. For example cell A1 has 30 and B1 has #N/A then =SUM(A1,B1) will result in #N/A error.

Solution:

In the beginning when worksheet is developed user or developer get the formula in place in advance even when the underlying data or required arguments are not inplace and this error is rectified moment the required argument is available. Also it is observed that such error is intentionally forced by the user using =NA() function. Check out this tutorial where we can see this error is intentionally made.

#NAME? errors

Excel has a powerful feature that let you define names for specific ranges of cells. In turn this makes writing formulas much more convenient. Check out our tutorials that involve named ranges.

Reason 1: #NAME? error occurs if user tries to use a name that is not already defined or is missing from Excel name “repository” or the name used is spelled wrong.

Reason 2: This can also result if user misspelled a function for example =HOOKUP( instead of =HLOOKUP(

Reason 3: There are many add-ons for excel that may come with additional formulas. If you have used such add-in dependent formula and later opened the same workbook in another excel program that does not have such add-in installed then again #NAME? error will result.

Solution:

1. Take extra care in changing or deleting names in name manager.
2. Use on the fly help. In excel as you start writing functions excel suggests functions which you can insert using TAB key instead of writing it completely yourself. This saves time and helps in writing formulas without spelling mistakes.
3. Make sure required add-ins are installed

#NULL! errors

Reason: This error occurs if user specifies a range using intersection operator that does not intersect. This error is rare as intersection operator is not used that often. Intersection operator is specified using space between ranges.

Solution:

1. Make sure if intersection operator is used then the ranges mentioned does intersect.
2. Use a different operator for example instead of intersection operator =SUM(A1:A10 B1:B10) use union operator =SUM(A1:A10,B1:B10)

#NUM! errors

As the name suggests it is a number error.

Reason 1: The output of value of a formula is too large or too small. There is a limit to which Excel can report numbers.

Solution: Excel support any number between -1 x 10^307 and 1 x 10^307. This is humongous range already. Still, if you hit bottleneck then consider using figures in thousands or millions in data to overcome this “limitation”.

Reason 2: If a function require a number as argument and a input is not such then again result will be #NUM! error.

Solution: Double check if the arguments specified are as per requirement for the function to work. Often this occurs if $ or % are used with numbers or comma separator is used within function.

Reason 3: Functions that calculate results with iterations were unable to find result and thus a #NUM! error.

Solution: You can change the number of iterations Excel can do by going to Excel options.

#REF! errors

This error notifies if cell reference is messed up and thus function cannot get the desired result.

Reason 1: The cell referenced in the formula is deleted. This often happens if the relevant column, row or worksheet is deleted by the user

Solution: Just undo the changes made.

Reason 2: If you have referenced the cells of first column and then copied or dragged the content to left then #REF! error will occur. For example you enter A2/A1 in cell A7 and dragging the cell to A6 will cause the error.

Solution: Use static/absolute reference instead of relative references.

#VALUE! errors

One of the most common errors in Excel. Mostly it is the case when function input is not as per requirements.

Reason 1: The input argument is wrong. For example =SUM(A1,”A”) will give #VALUE! error

Solution: Pay attention to the input requirements of function and what can be used as function arguments. For example SUM function can only handle numerical data and not text strings.

Reason 2: Function requires a single value or cell reference holding a single value but a range is given. For example in VLOOKUP the lookup value argument requires just one figure. If range is mentioned it will return #VALUE! error

Solution: Double check that you have mentioned the arguments correctly. Often user forgets to punch in comma to jump to next argument or take it as a wrong argument.

Reason 3: Formula is an array formula

Solution: Array formula needs to be executed using CTRL+SHIFT+ENTER key combo.

Bonus Tip: Printing errors

Worksheet containing any of the above errors will be printed with the errors. However, Excel’s print options let users control how the errors are printed. User has four options to chose from:

1. as displayed: will print the errors as they are displayed in the worksheet. This is the default value
2. as <blank>: cell containing errors will be blanked out and will show up as an empty cell.
3. show — : print “–” in place of errors in the worksheet.
4. show #N/A: This will replace all types of errors in the worksheet and will report #N/A instead

These options can be accessed via File > Print > Page setup > Sheet tab. Following animation helps you understand how to access such options:

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1