Why Excel shows #DIV/0! and how to hide it

You copy a formula down a column and suddenly a column of #DIV/0! errors appears. It looks alarming, but it is one of the easiest problems to fix once you know why it happens.

What the error means

Excel shows #DIV/0! when it tries to divide a number by zero. It often appears when you copy a formula down a column and one of the references moves to an empty cell. That happens because Excel uses relative cell addressing by default: the cell references in a formula move when you copy it.

Fix the cause: lock the reference

If one part of your formula must always point at the same cell, add dollar signs to it. Click next to the reference, such as C9, and press F4. It becomes $C$9, and it stays put wherever you copy the formula.

Hide the errors with IFERROR

Sometimes the data simply has not arrived yet, so there is nothing to divide. Wrap your formula in IFERROR and tell Excel to show a blank instead:

=IFERROR(C5/$C$9,"")

The cells stay empty until there is data to calculate, and the results appear as soon as you add it.

We work through this exact example on the Intermediate Excel course, alongside COUNTIFS, SUMIFS and PivotTables. If you would like to learn more, get in touch.