Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero

Hide formula on empty rows in Google sheets

Google Sheets, IFERROR, ISBLANK, IF

Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right?

Formual errors referencing empty values Google Sheets

What if I told you that you could hide all those messy errors? What if I told you that you could truly have that pristine nirvana that you have been imagining that your Google Sheet project would truly be?

Drink the Kool-Aid and strap in, my friends, as your very own guru in a goat takes you on a journey of enlightenment into hiding formula error when:

  1.  There is no data to parse
  2. Dividing by zero

“If there is nothing, it is not a mistake. It is simply nothing.” ~ The Ancient Ramblings of Goats

Hiding Formula Errors When There is No Data

Quite often you want to set up a spreadsheet so that users can enter data and that data is magically transformed via a formula. The problem is that quite often when a formula attempts to parse nothing it will display an error like:

#VALUE! or #N/A

These errors warn the user that the value doesn’t exist or there is no searchable index matching that value. This is quite useful in normal circumstances but when you are building a template where there will be blank spaces for your users to enter data at a later date, then these errors are:

  • Unnecessary, because you are simply awaiting data.
  • Messy, because you would have probably dragged down the formulas in anticipation of a lot more data from your users and all you see now is a trailing column of errors.
  • Confusing to your users. You know you are going to get that colleague who is going to annoy you because they see an error and their workplace hobby is calling people out on their mistakes.

So in order to hide these unnecessary errors, we need to pack your formula or function inside another short function that will check if data exists before running your main formula. Let’s look at three ways of doing this:

=IFERROR(Your Formula,””) 

The IFERROR function checks if a cell or formula returns an error. If an error exists, it allows you display an alternative. IFERROR can take two parameters:

=IFERROR(formula or cell value, alternative value[optional])

If you don’t choose an alternative value, then IFERROR will return a blank. This is exactly what we want if our users haven’t returned a value. So we will leave out the second parameter.

Let’s take a look at a couple of examples:

example 1: splitting names

I have a Google Sheet that splits the first name from the last name and displays them in a separate column each. To achieve this we use the SPLIT formula which takes a text cell or string of text and splits it by a delimiter like a comma or, in our case, a space.

=SPLIT(A2, ” “)

Hiding SPLIT error when there is no value Google Sheet

As you can see in the image, when there is no value the formula throws an error of #VALUE!. We don’t need to see that so lets put this in our IFERROR function.

=IFERROR(SPLIT(A2,” “))

Now when we drag that formula down the sheet, only a blank will be displayed in column B.

IFERROR on SPLIT Google Sheets

eXAMPLE 2: VLOOKUP KNIGHTS

In this example, we are using the function, VLOOKUP to compare a list of knights from Game of Thrones against a list of characters killed off in the first season of the TV series (Why? The final season has started!!!). We have the list of killed off characters over in “Sheet2”. Our formula is going to look a little like this:

=VLOOKUP(I2,Sheet2!A:B,2,FALSE)

Hiding VLOOKUP error when there is no value Google Sheet

This time around when we don’t have any data in column “I”, we get a #N/A error telling us that the VLOOKUP did not find any reference to a blank value.

Let’s clear up these empty rows again with an IFERROR function.

IFERROR(VLOOKUP(I2, Sheet2!A:B,2, FALSE)

IFERROR on VLOOKUP Google Sheets

IFERROR is great and all, but it does have a major drawback. You are no longer catching relevant errors. Take a look again at Example 2. Imagine if we also had Ser Sandor Clegane (The Hound) in row five. He was never killed (well as of season 8 episode 3 anyway), so we should expect to see a #N/A error indicating that he is not on the list of killed knights in Sheet2. However, because we have used IFERROR all we get is a blank space in column “J”. Not ideal.

Missuse of IFERROR in Google Sheets

The next two methods for hiding unnecessary error data are a little more convoluted but still provide you with the ability to display errors when data exists.

“Having no means to express something is not an error. Wait patiently and an expression will come to you revealing all.” ~ The Ancient Ramblings of Goats

=IF(ISBLANK(cell ref) = TRUE,””,Your Formula)

This approach uses the IF function to check if a cell is blank, or empty, by using the ISBLANK(cell) which returns either true or false. If the value is TRUE – meaning there is no cell value – we won’t run our formula and just leave a blank string (“”). Alternatively, if there is a value in the cell, we want to run our formula.

Let’s go back to our two examples.

example 1: splitting names

This time when we SPLIT our names we will do the following:

=IF(ISBLANK(A2) = TRUE,””,SPLIT(A2,” “))

IF ISBLANK on SPLIT Google Sheets

While all this extra work is not all that useful for this example, it will come in hand in Example 2.

eXAMPLE 2: VLOOKUP KNIGHTS

Remember in Example 2 we added Ser Sandor Clegane to our List of knights and that our Google Sheet looks up Sheet2 with a list of killed GoT knights? Using this new ISBLANK approach we should now be able to see an error for Ser Sandor Clegane because he is not on the list, but no errors for blank spaces let’s take a look:

IF ISBLANK on VLOOKUP Google Sheets

Great! It works!

=IF(cell ref=””,””,Your Formula)

In this final example, you would reference a cell and if it is blank – indicated by the empty string (“”) – then leave empty (“”). Otherwise, run your formula. This is essentially the same process as the above ISBLANK route.

example 1: splitting names

=IF(A2 – “”,””,SPLIT(A2,” “))

IF empty string on SPLIT Google Sheets

eXAMPLE 2: VLOOKUP KNIGHTS

=IF(I2 – “”,””,VLOOKUP(I2, Sheet2!A:B,2,FALSE))

IF empty string on VLOOKUP Google Sheets

Dividing by Zero

“Infinity awaits those who divide by the great empty” ~ The Ancient Ramblings of Goats

We’ve all done it. Most of the time it is unavoidable. We just have data we want to divide and some of that data is zero or it doesn’t exist. We know we can’t divide by zero and Google Sheets knows it too. If we do attempt to divide by zero, Google Sheets will spit out:

#DIV/0!

It will also do this when there are no values in the referenced cells, cause nothing is…um…zero.

Let’s dive in and take a look at our example:

Example – x/y = z

x divided by y equals z

As you can see in the image above, we have x divided by y in columns “E” and “F” with the resulting value in column “G”. This works out great for our first two formulas. 1/3 is fine and so is 0/1. However in rows 3 and 4 when we, unfortunately, divide by zero we get the #DIV/0! error. Further, when there are no values like from row 6 down it also spits out this error, which is decidedly untidy.

While the divide-by-zero error is useful for identifying errors in your Google Sheet, it can start to get messy when you have no data that is either represented as an empty cell or a zero.

Just like in the above examples we can use IFERROR to help us out.

=IFERROR(Your Formula,””) 

In our example we would deploy IFERROR the following way:

=IFERROR(E2/F2)

This would result in an empty space in column “G”.

IFERROR divide by zero return blank Google Sheets

This is a bit of a pain though if you want to, say, use the values for a graph or maintain consistency with numbers in a report.

A quick an dirty solution to this would be to use the second parameter in IFERROR to add a zero then this would run down the entire column:

=IFERROR(E2/F2,0)

IFERROR divide by zero return zero Google Sheets

This is still a little messy.

“Zero is the great circle. Complete in every way. It is impossible to divide by everything and nothing.” ~ The Ancient Ramblings of Goats

IF to the rescue

First, let’s see how we would duplicate the IFERROR with an IF function:

With a blank space  =IF(F2 <=0,””,E2/F2)

With a zero  =IF(F2 <=0,p,E2/F2)

If the formula now divides by zero, it will return a blank or zero respectively just like we did with IFERROR. The IF function is basically stating that if a value is less than or equal to zero, like a 0 or blank, then return zero or blank depending on which version you use.

That’s cool and all but what if we want to only return zero when we have a zero value in column “F”. Let’s start nesting some IF functions:

=IF(F2 = “”,“”,IF(F2 = 0,0,E2/F2))

In this formula, we first check to see if  “F2” is blank. If this is true, then we return a blank in “G2”. However, if F2 is equal to zero then we return zero in “G2”. Finally, if the value exists and is not divisible by zero then we will run our actual formula returning a number in “G2”.

IFERROR divide by zero return zero IF blank then blank Google Sheets

Conclusion

There you have it, perfection at last. Your Google Sheet is now pristine and free of error, but ready for data to be added and transformed into something amazing.

Did you enjoy the tutorial? Want a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad

Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.

Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

 

 

 

12 thoughts on “Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero”

  1. Yagi, love the conditionals used with if error. Personally, I want to see you get micro donations with your posts. If it weren’t for seeing your work, I wouldn’t have created a communication tracker for teachers within my district. Query functions are the bomb.

    Consider joining Coil.com, or signup for the XRPTIPBOT and point the monetized tips to your tipbot. You won’t make a killing, but it would be a way for me to show my appreciation.

    1. Hey histteacher,

      Thanks for the kind words. I didn’t know about Coil.com and XRPTIPBOT. I really appreciate the advice. I’m still trying to find a good ethical balance to monetize so I will look into it.

  2. 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. You have the closest match 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.

    1. I almost never comment on anything, but this solution is absolutely amazing, both in terms of being easy to apply to any sheet at any point, and that there is still some small visual sign that the cell contains an error and is not actually blank. Truly the most useful google sheets solution I have found so far.

  3. Nice article. However, I’m trying to hide div/0 error in a pivot table. How would you prevent this from this error message and the red in cell flag (error indicator) from showing up? I used the “iserror” formula in conditional formatting, which is helpful, but I can’t figure out how to get rid of that annoying little red error indicator in each cell. Any thoughts?

  4. Good article. I like to remove errors but like to know where they have occurred. Using the same coloured font on the same coloured background and Conditional formatting is a good way of achieving

  5. Sorry to bother, but here is how I am using your IFERROR fix: =iferror(DGET(Responses!$B$1:$D,”Select your score:”,{“Email Address”,”Week of:”;$A2,B$1})). This allows my students to enter the week and score in a google form. It then finds and formats the info for me. However, the IFERROR is making my formulas not update. I haven’t found a workaround. Currently my only fix is to resubmit the cells. Any ideas?

    1. Hi Paul,

      I couldn’t replicate the error unfortunately. Do you have a sample you can share? My only suggestion is to also try the much more protracted =IF(ISERROR = TRUE, “”, DGET(…)), but I think it is going to do the same thing for you.

      ~Yagi

Leave a Reply