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

Related Posts

How to Sum the Smallest N Values in Excel
How to Sum the Smallest N Values in Excel 15

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

How to Count Unique Numeric Values with Criteria in a Range in Excel
count unique numeric values criteria9

We have talked that how to count unique numeric values in a data set in Excel in the previous post. And this post will guide you how to count unique numeric values with criteria in the range in Excel 2013/2016 ...

How to Count Number of Cells that Contain Errors in Excel
count number cells that contain errors6

This post will guide you how to count the number of cells that contain errors within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through ...

How to Sum by Group in Excel

Sometimes we may have the requirement that subtotal data by group in a table. To just subtotal data in a column, we can use SUM function directly; for data in a column, amount data in another column, we can use ...

How to Compare Two Columns and Remove the Duplicate Values by Formula in Excel
How to Compare Two Columns and Remove the Duplicate Values by Formula 9

To compare two columns and remove the duplicate values, we have already posted a tutorial about how to solve this problem by ‘Conditional Formatting’ feature ‘Remove Duplicate’ rule. Actually, there are some other ways to solve it as well. If ...

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel
How to Create Dynamical Drop-Down List 14

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...

How to Find the Smallest Value and Smallest Positive Value in Excel
Find the Smallest Positive Value 9

Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate ...

How to Get the First, 2nd or Nth Match Using Vlookup/Index/Match
Find the First Match Value Using VLOOKUP4

This post will guide you how get the nth matching values with VLOOKUP function in Excel. How do I find the nth match value with Index/Match formula in Excel. How to find the first, second, third or nth matching value ...

Find the Earliest and Latest Date in a Range of Dates in Excel
find earliest date4

This post will guide you how to find the earliest date in a range of dates in Excel. How do I get the earliest and latest date in a range with a formula in Excel. How to find the highest ...

Find Closest Value or Nearest Value in a Range in Excel
find closest value3

This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. How do I find the closest match in a range to a given lookup value ...

Sidebar