Cleanup error cells or replace with blank – IFERROR

While writing formulas in Google spreadsheets, doing drag and drop of formulas, ever wondered how to get rid or cleanup the cells which have a value as 0 or have errors ? There is a formula provided in Google spreadsheets ” IFERROR“. Basically this formula if encounters an error will replace the cell contents with a balnk or a value specified by you.

A very very good use of this formula is when you would like to drag cell formulas but calculate only if they have values. Sounds complicated 🙂 Let me explain with an example :

Suppose you have two cells and you want to multiply the contents going down :
Drag Google docs formula downwards normally

As you can see, if I drag the formula downwards I see many cells with a 0 in them which gets quite irritating visually. To clean this up I will use IFERROR(1/0). Since I know 1/0 is an error IFERROR will catch this and output a blank.

Hence I will first check whether there is a value for Num1 : IF(isblank(D1)) ; if yes then I will use IFERROR else do the multiplication.

Have a look :
Goog docs IFERROR feature and benefit

You can see for the cells where there is no Num1 value or no value at all, we only see a blank and no more “0”s..

You can also use IFERROR with the ARRAYFORMULA which I will discuss in another section. Keep watching this post!

-Abhi