“Who has come across #DIV/0! in their spreadsheets?” is a question that I often ask the attendees when I deliver formulas and functions in Excel training. There are usually a few Yes’s entered into the online chat along with a few raised virtual hands.

“And how do you deal with them?” is my follow-up question.

Most people either leave them as they are, but to me that looks messy, or they manually overtype them which, depending on how many they’ve got, can take time and there’s always the risk that some get missed. Note: you can’t use Find and Replace because #DIV/0! isn’t recognised in the Replace dialog box.

So what is a #DIV/0! Error? Why does it occur and how do you fix it?

In Excel #DIV/0! is displayed in a cell as the result of a formula that includes a division where the cell “being divided by” contains a zero or is blank.

As an example, in a new file, type 100 into A1. In A2 type 0 (or leave it blank) and in B1 type the following formula: **=A1/A2**.

Because A2 is the cell being divided by and it contains a 0 or is blank, Excel can’t generate an answer. Dividing by 0 is a mathematical impossibility so the result of the formula is #DIV/0!

So that’s what #DIV/0! Is. But how do you fix it? There are several ways but my preferred method is to use IFERROR. Watch this video to find out more.

You can download a copy of the file I used in the video by clicking through to YouTube. The link for the file is in the YouTube description.