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

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?

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, ” “)

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.

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)

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 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.

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,” “))

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:

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,” “))

eXAMPLE 2: VLOOKUP KNIGHTS

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

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

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”.

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)

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”.

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 to upskill and get 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.

 

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