When I am extracting data, a lot of times, I got the number 0 or an empty cell and when 1 of my colleagues give me her report, she can put something like n/a. Of course, the easy way is to delete them, one of the quick ways will be to use the replace tool but sometimes, I need a formula to show a different result because I am not working with the original sheet but with another sheet.

When I use the formula ?

Each time that amongst the data has 0, I need to show something different.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=IF()

=ISBLANK()

Put this formula:

=IF(C2="","nothing",C2) or =IF(C2=0,"nothing",C2) or =IF(C2="n/a","nothing",C2)

So I am telling that if the cell C2 is empty (first one), the value is 0 (second one) or n/a (third one), put nothing, if not, just put its value.

=IF(SUM(A1:A3)="","",SUM(A1:A3)) or =IF(SUM(A1:A3)=0,"",SUM(A1:A3)) or =IF(SUM(A1:A3)="n/a","",SUM(A1:A3))

So I am telling that if the sum of A1+A2+A3 is empty (first one), equal to 0 (second one) or n/a (third one), put an empty cell, if not, just put its value.

As you can see, I am using the “if” function. Those 2 examples, it is just to show you that you can put everything you want, it can be an empty cell, a sentence like “why we have a 0 here”, etc. Also you can use it with another formula.

For the empty cell, sometimes it is best to use the “isblank” function:

=IF(ISBLANK(C2),"nothing",C2)