## 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
; AND**B2**/**B6** - 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:

*=IFERROR( B2/B6,””)*

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:

Simple, no?!

[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!

Brilliant, works perfectly! You’re a life saver 🙂

Yay! Thanks for taking the time to comment, Judson. I’m glad it worked!

I know this is an older entry, but you just saved me a ton of time. Thank you for sharing your knowledge in an easy to understand manner. You rock.

Hi Patrick! I’m so glad this post helped – I know how frustrating error messages can be! Now if only there was an easy solution to VALUE errors, lol. Thanks for taking the time to comment. Best to you.

I am trying to apply this to columns rather than rows. I can’t seem to get it to work. I have a column of orders that needed rewriting. A column of stationary that was reordered to cover those rewrites. In the third column I want it to automatically calculate what percentage of overage (or waste) that was reordered to cover the necessary rewrites.

The current formula looks like this. I want it to show a zero in percent format in the 3rd column if the first 2 columns are zero or contain anything other than a number.

Right now the 3rd column is blank if the first two are zero or contain a number.

=arrayformula(IFERROR((L2:L-K2:K)/L2:L))

Hi Michele! I haven’t had a reason to work with arrays much lately, so maybe I’m overlooking something. But when I see your formula I notice that you are missing the second piece to it – the value if false. The non-array version of the formula that provided the desired result for me is =IFERROR((L2-K2)/L2,0). You’ll then need to select L2:L and format it as a percentage. I hope that helps. If the array thing complicates it, I’d suggest checking out one of the many free Excel support forums as the folks who spend time there are probably more advanced than yours truly. Best of luck to you!

Thanks so much! 🙂

Absolutely! Thanks for taking the time to comment Alexandra ?

Yo, this saved my butt! Thanks for sharing this really helpful and useful Spreadsheet formula tip! Appreciate it.

Hey Ed! I’m so glad it helped. Thanks for taking the time to comment 🙂

A year past the last comment and this information was so helpful that I felt I needed to leave a personal thank you!

Thanks so much for taking the time to let me know, Suzanne! I am glad that it helped and that it continues to help people. Happy Friday!

Thank you. I have ben staring at the #div/0 error for years. When I set up my new 2019 section, I said I want to fix that ugly view. You were the first to show up on the internet search and you were correct. THANK YOU. I have been on excel since it began. Never got that detailed with it.

I’m so glad you found this post, Greg! I know how frustrating it can be seeing that dumb error…it’s why I wrote the post 🙂 Thanks for taking the time to let me know.

I am trying to hide a Div/0 in a pivot table in Google sheets on an Average calculated field. The field contains an “A” so that it is not included in the average calculation but it returns the error Div/0. When in Excel we are able to hide errors in the pivot table options but I can’t find that functionality in Google sheets. I thought I could use conditional formatting but I can’t. Do you have any ideas about this?

Hi Cari. I’m afraid I stopped using Google Sheets a long while back (the mobile app kept crashing with one of my nested if functions and I just gave up). I do not have a solution for you. If you identify one, I’d love to know what it is, though. Best of luck to you!

Hey Cari…I had a similar problem…and through a little (actually a ton!!) of research and looking around, I found this https://www.jonathan.vc/tech/2017/07/return-empty-cell-value-zero-google-sheets. So if you place his calculation as part of Kristi’s, it works…I’ve included an example:

=IFERROR(IF(K10/J10>0,K10/J10,””))

I now have a spreadsheet that has an empty cell if the calculation is 0 or doesn’t yet have any numbers to calculate with.

I hope that helps. Daniel

Hi there, this is not working for me for some reason. I was using a simple =C2/G2 where C2=290 and G2=5 and I should be getting 58 but when I put =IFERROR(C2/G2,””) I get “.040277778”

I don’t know where this is coming from. Please help!

Hi Joni. I am sorry you are having trouble! It’s not a problem with the IFERROR formula, as that would only cause an error message to be displayed. I don’t know why you are getting that odd result, though – I have never seen that before! I recreated your formula and arrive at the correct result (58). If cells are formatted in a non-number format, it’s normal to get what appears to be a non-sensical result. But your result doesn’t jive with that explanation.

I would suggest copying your sheet and doing a paste special => values into a blank sheet. Then, I would recreate your formula. Of course, this may not be a feasible option, depending on your spreadsheet’s contents, but it’s the best solution I can think of. Sorry I can’t be more helpful!

Ms Amdahl:

Everything in your article is correct, AND the same goal can be accomplished even more easily than you describe.

A more accurate representation of the syntax of the IFERROR function is:

=IFERROR(value,[value if error])

The brackets around the second parameter tell us that parameter is optional. When omitted, if the ‘value’ parameter returns an error, then IFERROR returns a blank.

In other words, the following two formulas return identical results.

=IFERROR(B2/B6,””)

=IFERROR(B2/B6)

Thanks for the article, and keep up the good work!

Thanks, Rich! I did not realize this and it’s a game-changer…I will never look at functions the same again 🙂 Thanks for taking the time to share this with us!

Hi Kristi,

I’m combing the IFERROR with the SUM but still getting #ERROR! Could you shed some light please?

=IFERROR((SUM(I13-I3)/I3),“”)

Hi Vilac,

I plugged your formula into an open Excel spreadsheet under the following conditions: 1. A zero value in the denominator; 2. regular numbers; 3. words in the denominator. In the first and third case, I was returned nothing, meaning the formula worked. In the second case, I was returned a number (as expected). So, I don’t know why you are encountering issues with it.

However, I don’t recognize #ERROR! being an Excel error message. Are you using a different program? If so, it may not like your formula. You could try simplifying it like this to see if it resolves your issues: =iferror((I13-I3)/I3),“”). If it doesn’t, I’m not sure what to say. Good luck!

Hi Kristi,

Got it working! Thanks!

Vilac

Awesome! Thanks for the update 🙂