Mastering data in Google Sheets: ERRORS handling with these formula tricks

Posted on Posted in All articles, Google Sheets, Tutorials
Mastering data in Google Sheets: ERRORS handling with these formula tricks

Errors in Google Sheets… It happens every time! Breaks the charts in Looker Studio and is simply an eyesore while working with data! Read the article about Google Sheets errors handling!

In this article I am going to teach you a bunch of formula tricks that can help you avoid the errors in your Google Sheets! 

Check out the video tutorial here:

Errors!

I have created a table and it was all fine, until I decided to count a conversion rate using a formula. That is how the annoying errors appeared. In my case, they appeared because you can not divide by 0. How can I fix everything?

  1. Iferror formula

This formula is pretty easy:

=IFERROR(D2/G2,0)

It can be explained as if everything is correct in the formula, show the result, but if there is an error, show 0.

Showing zero is a Looker Studio-tolerant way of making formula, but if you decide to show instead of 0, it will be considered as an error.

However, there may be cases where showing 0 may be really confusing. For example, when you do have zeros and you have missing data. 

  1. If formula

=IF(G2=0,0,D2/G2)

This is how the formula looks like, It means that if the value we are going to divide by is 0, show 0. If it’s not, count using the following formula.

  1. If and Iferror

Here is one more formula to use when you have no idea how many rows will the table contain but not showing zeros in all the empty rows, and it’s created using both formulas above mentioned:

=IF(A2=””,””,IFERROR(D2/G2,0))

It can be explained as if the cell from the first column is empty, leave the cell empty, If it’s not, count the value using the formula and if there is going to be an error, show 0. 

  1. Arrayformula

This is a way to make the formula more complex and I use it most of the time. It looks like this:

=ARRAYFORMULA( IF(A2:A=””,””,IFERROR(D2:D/G2:G,0)))

Basically, here I have replaced any cell from the columns with a data range, for example, A2:A, D2:D, G2:G. 

In addition, if you are curious about Looker Studio and you want to get a profound knowledge about this data visualization tool, I invite you to my Looker Studio course! Read more about it.

Hope you liked this article! Share your impressions in the comments below! Check out more artiles of mine in Medium.