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

**Note:** the same solution applies when it comes to hiding other errors.

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

Spreadsheet DIV/o! error messages suck. Here's how to hide or replace them with playful text.Click To TweetIf 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!

In google drive the order is inverted fist is the result if error then the calculation

=IFERROR(“”;B3/D3 )

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 🙂

OMG – life-saver!! I just spent waaaay too long trying to figure this out!! thank you

Hi LeighAnn! I’m so glad it helped. Errors are always so frustrating 🙂

Hi, I had a large sheet that wasn’t easy to retrofit with prefixed functions to suppress error codes in cells. I was using conditional formatting in the sheet and searched for a way to achieve blanking out errors with conditional formatting. I didn’t find anything on the web. Your page is one of the closest matches to my search so I want to share the solution I ultimately discovered based on a formula I used in another spreadsheet. I set a conditional format for a range with the Custom Formula rule “=ERROR.TYPE(indirect(address(row(),Column())))<9" and I set the style to use white text on white background. The red triangle affordance still appears at the top right of a cell that has an error, which still communicates the error but in a less severe way.

Hi Jack. Thanks for sharing this alternative way to deal with errors! I have used conditional formatting to hide cell contents depending on other cells’ contents, but I had not thought to use it to hide errors. Now I’m going to have to try it! Thanks again!

actualy no. in google drive you have to use “;” not “,” at least for me

Hi,

I am using Google Sheets and want the value to be 0%. How do I change the formula to result 0?

Hi Felicia. In the example provided in the post: =IFERROR(B2/B6,”Hello! It isn’t July yet!”), you would just replace the ”Hello! It isn’t July yet!” with “0%”. I hope this helps!

Hey Kristi! Thanks for the tip. It helped me a lot with a report I had to deliver. Awesome job!

You bet, Edgard! I’m glad it helped. Thanks for letting me know 🙂

Hi! I followed you directions to get rid of the #DIV/0, but not my averages won’t compute correctly. :-/

I’m creating a gradebook. I want to average cells in a row (i.e. C4 – E4 and have the average display in F4.) Because no individual grades were in the C4-E4 cells yet, it was giving me the #DIV/0 error message. I then used this: =IFERROR(AVERAGE(C5:E5,0)) which did input a 0 when no grades were entered yet. But when I enter grades, the average won’t computer correctly. A 100 and a 50 is averaging to 50 instead of 75. If I just enter one score of 100, it says the average is 75 right off the bat. :-/

I hope this is making sense..I’m just a first grade teacher trying to create a simple gradebook for my team. 🙂 I appreciate any help you can give me!

Hi Ashley! I’m sorry – that is frustrating! I’ve run into this same issue, so I know what you’re talking about. I’m not in front of the computer right now and so haven’t test-driven potential solutions. But, your formula is including the zeroes in its averaging.

There may be an obvious solution that I’m missing (if there is, I’m blaming the vino and time), but there is a fairly simple way to bypass the problem. In F4, type =sum(C4:E4)/G4 (or any other cell that isn’t in use). Then, in G4 enter =count(C4:E4). Copy the formulas in F4 and G4 and paste for each student. Note that you will need to adjust the ranges if you insert new columns. This should give you an average of only those cells that have grades entered. If a student receives a zero for an assignment, you may have to manually adjust the count.

Forget the iferror part – you won’t need it for this.

Let me know if you run into issues. I can troubleshoot during the day tomorrow if you do 🙂

Thank you! I’m sorry I didn’t see this until just now. I will most definitely give that a try and let you know!

YAY!!! It worked! So I just have to have an extra column to add up how many grades it is I’m entering. Thank you so much! 🙂 🙂

Whoops. Wait. It still shows the #DIV/0 message in the “average” cells until I enter the first grade. I guess what I’m really trying to figure out is… is there a way to just hide that for the time being. It’s more of an asthetics thing I guess. I’d prefer it to keep the formula, but just be a blank cell until grades start being entered. I just feel it will look very cluttered and crowded with #DIV/0 on 22 different lines of 10 different standards that are being graded. I hope that makes sense.

Hi again! This is actually a super easy fix (thankfully!). In F4, type =iferror(sum(C4:E4)/G4,””)…based on one of your comments, you may need to adjust the column letters or row numbers. Let me know if you run into any issues!