Error messages detract from a table’s aesthetic.
A common practice of spreadsheet creators is to pre-populate tables with formulas.
You know, so that when data magically appear, the table magically spits out results.
This is all well and good, but when division is involved and blank cells are referenced in the denominator, the result is an ugly #DIV/0! error.
In tables that are several rows deep, this error can really be hard on the eyes.
One place where we will almost certainly encounter this issue is in budgeting workbooks.
For example, we may want to know what proportion of our total monthly income is attributable to each income source.
Given that I wrote this post in June, the July table below doesn’t yet contain any income figures.
The result is a lovely error message in column C. What happened is that the formula was trying to divide income by an empty cell, which is the equivalent of zero*:
*Note that I am using Excel screenshots in this post, but what I am teaching you applies equally to Google Sheets.
Changing or hiding the error text #DIV/0! is easy!
It’s super simple to change or altogether hide the error text – just use this function if you don’t want an ugly error message staring you down:
=IFERROR(value, value if error)
- Replace ‘value’ with the formula or function (do not include =) – in this case, it’s B2/B6; AND
- Replace ‘value if error’ with what you want Excel to show instead of an error message. If you want the cell to appear blank, enter “”; if you want to replace it with ‘Hello! It isn’t July yet!’, enter “Hello! It isn’t July yet!”.
So, enter this in C2 if you want column C to not display anything until income data have been entered:
Or, enter this in C2 this if you want column C to instead display ‘Hello! It isn’t July yet!’:
=IFERROR(B2/B6,”Hello! It isn’t July yet!”)
*Note: if you plan to fill or drag the formula down instead of copying and pasting it, be sure to change the B6 cell reference to B$6. Adding ‘$’ tells Excel or Google Sheets to always reference the column or row that immediately follows.
Here’s the result:
[clickToTweet tweet=”Spreadsheet DIV/o! error messages suck. Here’s how to hide or replace them with playful text.” quote=”Spreadsheet DIV/o! error messages suck. Here’s how to hide or replace them with playful text.” theme=”style1″]
If you’d dig other tips like this, download my free From Spreadsheet Virgin to Goddess in 30 Minutes. Also, here’s another cool post about different (easy!) ways to add drop-down lists to your spreadsheets. Enjoy 🙂
Do you leave #DIV/0! errors alone, or do you use this function to hide or change them? Do you have any favorite clever error replacement messages you like to use? Share below!