Trending October 2023 # How To Get Rid Of #Div/0! Error In Excel? Easy Formulas! # Suggested November 2023 # Top 14 Popular | Dacquyenphaidep.com

Trending October 2023 # How To Get Rid Of #Div/0! Error In Excel? Easy Formulas! # Suggested November 2023 # Top 14 Popular

You are reading the article How To Get Rid Of #Div/0! Error In Excel? Easy Formulas! updated in October 2023 on the website Dacquyenphaidep.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Get Rid Of #Div/0! Error In Excel? Easy Formulas!

If you have been working with formulas in Excel, I am sure you have encountered the #DIV/0! error (the Division error).

In this short tutorial, I will show you how to quickly remove the division error in Excel (using some easy formulas).

I will also show you how you can find all the cells in a worksheet or workbook that contain the DIV errors.

In Excel, you will get a DIV error when you have a formula where there is a division and the divisor is 0.

For example, if you enter =12/0 in a cell in Excel, it will give you the division error (#DIV/0!)

Of course, you wouldn’t use a formula like this in real life, but in most cases, the division error is a result of larger formulas where you use cell references in the formula, and some of the references could have the value 0 or could be blank.

And sometimes, you may get this error as part of the data you download from a database or from the web.

If you’re working with formulas and want to get rid of the #DIV/0! error (which often occurs as the result of the formula), you can use the IFERROR technique.

Below is the syntax of the IFERROR formula:

=IFERROR(value, value_if_error)

here:

value – this is the formula that can give you the division error

value_if_error – this is the value specify you should get when you have any error

Let me explain this using an example.

Suppose you have a dataset as shown below where I have the division formula in column C.

As you can see, I get div errors in all the cases where the divisor is 0 or blank in column B.

To remove the div error, I can use the below formula:

=IFERROR(A2/B2,"")

The above formula returns the resulting value after the division if it’s not an error, and in case it gives an error, it replaces the div error with a blank.

Similarly, if you want to replace the div error with something more meaningful than a blank, you can specify that as the second argument.

Below is the formula that will give you the text “Not Available” instead of the division error:

=IFERROR(A2/B2,"Not Available")

A few important things to know about the IFERROR formula:

IFERROR formula was introduced in Excel 2007 onwards. So if you’re using Excel 2003 or prior versions, you won’t get to use this formula (use the method I cover next in that case)

IFERROR works for all the error values (including N/A, #DIV/0!, #VALUE!, #REF!, #NAME, #NUM, etc.). So in case your formula returns any other error, it would also be treated the same way

While using the IFERROR function is the preferred way to handle the #DIV/0! error in Excel, in case you’re using an old version of Excel that does not have the IFERROR function (or you need backward compatibility in case you have to share the file with someone who is still using an older version of Excel), you can use the IF + ISERROR method.

This one works just like the IFERROR function but has a few more arguments.

Again, suppose I have the below dataset and I want to remove the #DIV/0! error.

I can use the below formula to do this:

=IF(ISERROR(A2/B2),"Not Available",A2/B2)

The above formula first checks whether our formula gives an error or not (using the ISERROR formula), and in case it does, it gives the second argument as the result, else it gives the third argument as the result.

In this section, I will show you how to quickly find and select all the cells that contain the #DIV/0! error.

This could be useful when you get a file from a colleague or download it from the web and it has the div error that you want to remove.

While this method won’t remove the DIV error, it can be useful in finding and highlighting these errors so you can decide how to handle these (such as delete it or check the data and rectify it).

Below are the steps to find and remove all DIV errors in the worksheet/workbook:

Open the file in which you want to remove all the div erorrs

Hold the Control key and press the F key (or Command + F if using Mac). This will open the Find and Replace dialog box

    In the ‘Find what:’ field, enter #DIV/0!

      From the ‘Within’ drop-down, select Sheet or Workbook (select Workbook if you want to find the error cells in the entire file)

        From the ‘Look in’ drop-down, select ‘Values’

          Hold the Control key and press the A key (or Command + A if using Mac). This would select all the cells that contain the division error.

          Once selected, you can remove the error from all these cells by hitting the delete key, or highlight these by giving the cells a background color.

          So these are some of the ways you can use to remove the DIV errors (division error) in Excel. If it’s a result of your own formulas, you can use the IFERROR function, and if you have these in a workbook that you have inherited or downloaded, you can use the Find and Replace dialog box technique I covered.

          I hope you found this tutorial helpful!

          Other Excel tutorials you may also like:

          You're reading How To Get Rid Of #Div/0! Error In Excel? Easy Formulas!

          Update the detailed information about How To Get Rid Of #Div/0! Error In Excel? Easy Formulas! on the Dacquyenphaidep.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!