I think I now understand the difference between an Excel tip and an Excel annoyance. It’s an annoyance if the recipient of your spreadsheet doesn’t know the tip and you spend more time defining the problem than it takes to fix it. Next time, I’ll take the five minutes to fix my Excel formula so it doesn’t display the **#DIV/0!** error message.

## What Does #DIV/0! mean in Excel?

It’s a logical error that happens when:

A. a number is divided by 0

or

B. your Excel formula refers to a cell with a zero value or is blank.

Some look at the alert and see the help text “**The formula or function used is dividing by zero or empty cells**” as shown below. Others might question the data integrity. Personally, I think it’s an aesthetic issue.

The reason I got this Excel error was that I tried to divide my **Cost** value in **C7** by my **Catalog Count** in **D7**.

I ran a test ad that cost $77.45 and generated 0 catalog requests. A similar error would occur if the Catalog Count cell was blank. In other words, I was asking Excel divide $77.45 by zero. Oops.

## Add Logic to Your Excel Formula

There are several ways to fix this error. One method is to use the **IF function**.

This is a logic function where you can direct Excel to do one action **IF** a condition is **TRUE** and another action if the condition is **FALSE**.

In this case, I want Excel to take a different action **IF** I have a Catalog Count of “**0**”. Otherwise, Excel can continue as normal.

## How to Show a Blank Value instead of #DIV/0!

- Create a column for your formula. (e.g. Column E Conv Cost)
- Click the next cell down in that column. (e.g. E2)
- Click
**Insert Function**on the Excel ribbon. - In the
**Insert Function dialog**, select**IF** - Click
**OK**. - In the
**Function Arguments**dialog, click in the**Logical_test**field. - Click the top cell in the column which you’re dividing by. (e.g. D2)
- In the same text field after the cell reference type
**=0**. (The field should show something like D2=0) - Leave the
**Value_if_true**field**blank**. - In the
**Value_if_false**field, enter your formula such as C2/D2 - Click
**OK**. - Copy the Excel formula down to each cell in the column.

You can see in the example below, in each case where I had a “0” or a blank cell in Column D (Catalog Count), Excel left the Conv Cost value in Column E blank. The blank value in the Excel formula is represented as the double quotes.

In short order, you’ll start entering this function directly in the Excel formula bar rather than using the **Insert Function** dialog.

## Other Text Substitutions for #DIV/0!

Just as some people don’t like the divide by zero error messages, others might object to blank cell values or the zero. They might prefer something like a text value or even the contents of another cell.

**You can substitute text or a cell reference instead of having the cell be blank**. For example, I could’ve used “NA”.

**=IF(D2=0,”na”,C2/D2)**

or

**=IF(D2=0,C2,C2/D2)**

In this last example, Excel would insert the Cost value in the Conv Cost cell instead. Depending on your situation this may be more accurate. In my example of catalog conversion costs, using the Cost value might be better than “0” or a blank.

Excel allows you to fix this error in several ways. Another popular solution from readers mentioned that the **ERROR.TYPE** method works well. Microsoft has assigned an error value of “2” for this #DIV/0 example. You can find more details on ERROR.TYPE on the Microsoft site.

As you can see there are several ways you can remove the divide by zero error in Excel. Chances are whichever method you choose will be less annoying for your users than seeing #DIV/0!