Show other result for 0 and empty cell with a formula in an excel report

Many extractions I did contain some empty cells or cells with 0. One of the options is to delete them but in some situation, I need to keep them. What I do most, it is to put another value, for example, all cells with zero, I will put “none” and for empty ones, “not related”.

formula excel


When I use the formula ?

When I need to display another result if the cell is 0 or empty.


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 are the formulas ?



This formula is telling to display an empty cell if the value is 0.


In fact, you can ask it to put anything you want, if you want to display “nothing” for all 0 values, the formula will be:


Of course, if you prefer to tell it to display the empty cell or “nothing” for another value, just change the “=0” to for instance “=5”.

This formula works the same way if the cell value is empty:


Instead to put the cell reference A2, you can put the name:


The point is that if you have 2 or more “chocolate”, the formula will only take the first one that it will find. Putting the name is better only if the name is unique and not duplicate.

Interesting Management