MS Excel: Display Cell Data Conditionally

1. Display Cell Data if the Value is Zero or Below

To achieve this, we’ll use the IF and ABS functions. Let’s start with the basic scenario.

Basic Formula to Display Data if Zero or Below

Assume you have a value in cell A1 and you want to display this value in cell B1 only if it’s zero or below. Use the following formula in cell B1:

=IF(A1<=0, A1, "")

This formula checks if the value in A1 is less than or equal to zero. If true, it displays the value; if false, it leaves B1 blank.

Handling Negative Values: Display as Positive

Often, you may want to display negative values as positive. For this, we incorporate the ABS function, which returns the absolute value of a number:

=IF(A1<=0, ABS(A1), "")

Here, if A1 is zero or negative, B1 will show the positive equivalent of A1. If A1 is positive, B1 remains blank.

Explicitly Handling Zero Values

While the above formula works perfectly, you might want to explicitly handle zero values. To do this, slightly adjust the formula:

=IF(A1<=0, IF(A1=0, 0, ABS(A1)), "")

In this formula:

• If A1 is zero, B1 will explicitly display 0.
• If A1 is negative, B1 will show a positive value.
• If A1 is positive, B1 remains blank.