Ignore zero values in conditional formatting in Excel

Problem

We want conditional formatting in Excel to ignore cells containing zero values. In the example below, after applying colour scales in conditional formatting, we can see that Excel applies red formatting to cells with zero values.

Solution

To make conditional formatting ignore zero values, we follow these steps:

1. Create a new conditional formatting rule.

2. Apply no format to cells with value equal to 0.

3. Make sure that this rule is the first to be evaluated, and select the Stop If True check box.

Results

Related posts

Formato condicional

References

Use conditional formatting to highlight information

Conditional formatting to hide cell content in Excel

Problem

We would like to hide the contents of a cell based on the value of another cell.

Solution

In A2 we apply a conditional formatting rule that hides the content of that cell if it is equal to B2.

In Conditional Formatting we create the following rule: =$A$2=$B$2, then press Format...

1. On the Format menu, click Cells, and then click the Number tab.
2. Under Category, click Custom.
3. In the Type box, type ;;; (that is, three semicolons in a row), and then click OK

Notes

Why do we need to add three semicolons as a custom Excel format? The reason behind is that custom number formats consist of four sections separated by three semicolons, so if we just type three semicolons and nothing else, the cell will appear empty.

References

• stackoverflow