Ignoring Blank or Zero Cells with Conditional formatting

This post will guide you how to make conditional formatting ignore blank cells or zero cell in Excel. How do I force blank cells or zero cells to be ignored in conditional formatting in Excel. How to ignore blank cells or blank cells when applying conditional formatting in Excel.

 Ignoring Blank with Conditional Formatting


When you create conditional formatting rules for a list of selected data, and you want to ignore all Blank cells, How to achieve it. Just do the following steps:

#1 select the source data that you want to apply the conditional Formatting.

ignore blank zero cells in 1

#2 go to HOME tab, click Conditional Formatting command under Styles group. And select New Rule from the popup menu list. And the New Formatting Rule dialog will open.

#3 select Use a formula to determine which cells to format in the Select a Rule Type section.

#4 type the following formula into the Format values where this formula is true text box. Click Ok button.

=ISBLANK(B1)=TRUE

ignore blank zero cells in 2

Note: the B1 is the first cell of the selected range of cells.

Ignoring zero with Conditional Formatting


If you want to ignore zero values with conditional Formatting, you can do the following steps:

#1 select the range of cells C2:C5

ignore blank zero cells in 3

#2 go to HOME tab, click Conditional Formatting command under Styles group, and select New Rule, and the New Formatting Rule dialog will open.

ignore blank zero cells in 4

#3 select Use a formula to determine which cells to format in the Select a Rule Type section.

ignore blank zero cells in 5

#4 type the following formula into the Format values where this formula is true text box.

=AND(C2<>0,C2<=SMALL(IF(C$2:C$5<>0,$C$2:$C$5),2))

#5 click Format button, and switch to Fill tab in Format Cells dialog, select one color as the background color. Click Ok button.

ignore blank zero cells in 6

#6 click Ok button. You will see that all zero values are ignored by conditional formatting.

ignore blank zero cells in 7

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar