Errors in Excel

CA Suraj Lakhotia , Last updated: 18 June 2013  
  Share


#N/A…#DIV/0!.. #NAME?.. #VALUE!.. #REF!.. #NUM!

 

Sounds familiar?

These are the errors excel returns when the formula contains an error (#NAME?), the variables don’t meet the criterion of the formula (#VALUE!), when the reference is not valid reference (#REF!), Division not possible (…#DIV/0!) , when you enter a non numeric value where the formula requires a numeric reference (#NUM!) or the residual error  (#N/A).

VLOOKUP is probably our favourite formula and “#N/A” the most disliked result. Every time we execute this formula across thousands of cells and when many of these return “#N/A”, removing these texts containing error message becomes a daunting task.

We can use few techniques to make the removal process easier:

 Copy the entire data fields > Use Paste Special Function and Paste the values > Use the find and replace option (Ctrl+H) > Type “#N/A” in “Find what” field and leave the “Replace With” field blank> Select Replace All. Excel removes all the field containing “#N/A”

 

This is easy to execute but the formula no longer exists in the sheet. Any update in the base sheet will not be updated.

 

Fig.1 Using the Find and Replace Option

  

 Use the formula “IFERROR”:  The syntax for this formula is - IFERROR (Value, Value_if_error). This is simple to use. The formula would look like:

=Iferror(Vlookup(A1,_______,__,0),”

If we don’t give any text and just use “”, then the excel will return a blank cell instead of a “#N/A”.

 Another option to delete the contents of a cell which contains an error is to use Go To option. Press “F5” and select “Special”. In the “Go To Special” window, “select formulas” and check only the “Errors” option. When you click “OK”, Excel will highlight all the Cells Containing Formulas which have returned Errors. You can delete these cells with one stroke on keyboard.

Fig.2- Using the Go To Option

Join CCI Pro

Published by

CA Suraj Lakhotia
(IndigoLearn)
Category Students   Report

  10817 Views

Comments

14 July 2013 CA Kumar Mukesh

One more thing instead of using replace function one may avoid to come errors like #NA, #VALUE ect. by using iferror formula, for example we want to use iferror function with VLOOKUP the way is =iferror(vlookup(fun)," "/0/etc


19 June 2013 Pulkit Sharma

you are right but when we do vlookup or anything else the source data will contain atleast some value if not than its nil or 0. However to use 0 or "" depends on the required results of tested querry.


18 June 2013 CA Suraj Lakhotia

@ Pulkit, If you use a 0 instead of "", no doubt the cell will retain its format as number. However if the result of the formula in some cases itself turns out to be 0, it would not be appropriate to use 0 as a replacement of error.


18 June 2013 Pulkit Sharma

instead of putting "", one should put 0 so that cell does not change to text.


Guest
18 June 2013 Anonymous

Thanx 4 Sharing......for me i have learn new thing abt dis error....useful 1........


Your are not logged in . Please login to post comments.

Click here to Login / Register