3 ways to handle unwanted Errors in Excel

image_pdf

It is important to have error free worksheet, which can be presented in better way. But if it is not possible, we must try that we can choose to hide Errors, so it does not affect the appearance of the worksheet.

Many times it is require to know, if the cell is containing error or not. We may want to display the specific message if the cells is with error. To count the total number of error in range of cell, countif or sumif function is used.

1. Use ISERROR function to know if the active cell is with Error or not.

Combine IFERROR and IF function to give specific message for the cells with Error.

ISERROR: The ISERROR function returns True/False depending on error.

Syntax:

                         =ISERROR(value)

Example:

counterror

IF: The IF function checks the logical function and returns the value if found true /false based on arguments. Here ISERROR Function returns TRUE/FALSE.Based on that we can provide message of "error" or "noerror".

Syntax:

                    =IF(logical_function,[value-if-true],[value-if-false])

Example:

counterror1

Learn more: Use nested IF function to evaluate multiple condition

2. Use COUNT OR SUM Function to calculate how many errors are there in sheet.

You can use combination of COUNT+IF+ISERROR function to calculate errors in the given range. It will help to analyze the worksheet properly and take action carefully. If Errors are more then you can Hide cells with Errors using ISERROR or IFERROR fuction.

COUNT: The COUNT/SUM function counts the number of errors present in the given range.

Syntax:

                      = COUNT(IF(ISERROR(value),1))

                                                OR

                      =SUM(IF(ISERROR(value),1))

Example:

counterror3

3. If your datasheet is with multiple type of error like, #NAME? #REF or #VALUE! etc. You can count particular type of error. This will help you to identify type of mistakes in the worksheet.

COUNTIF: The COUNTIF function is used to count the particular type of error.

 Syntax:

                                  =COUNTIF(range,criteria)

Example:

counterror4

Explore More, It's Amazing…

10 Best Tips to Format your data quickly

How to remove first,last or certain characters from text using LEN function?

 

 

6,407 total views, 9 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>