How can I ignore blank cells in conditional formatting in Excel?

By /

Conditional formatting in Excel allows users to apply different formatting styles to cells based on specific conditions. However, when working with large datasets, it is common to come across blank cells which can disrupt the conditional formatting. To avoid this, users can use the “ignore blank cells” option in the conditional formatting settings. This will ensure that any blank cells in the selected range will not affect the conditional formatting and only cells with data will be considered. This can help maintain consistency in the formatting and prevent errors in data analysis.

Excel: Ignore Blank Cells in Conditional Formatting

To ignore blank cells when applying conditional formatting in Excel, you can use the Manage Rules option under the Conditional Formatting dropdown menu within the Home tab.

The following example shows how to use this option in practice.

Example: How to Ignore Blank Cells in Conditional Formatting in Excel

Suppose we set up a conditional formatting rule in Excel to highlight all values in the range B2:B11 that have a value less than 20:

Notice that the value in cell B10 is also highlighted in green even though it contains a blank cell.

To ignore this blank cell when using conditional formatting, we need to add a new rule that applies no conditional formatting to blank cells.

To do so, highlight the range B2:B11, then click the Conditional Formatting dropdown menu on the Home tab, then click Manage Rules:

In the new window that appears, click the New Rule button:

Then click Format only cells that contain, then choose Blanks, and leave the formatting blank:

Once you click OK, the Conditional Formatting Rules Manager will appear where you can see all of the conditional formatting rules.

Check the box next to Stop If True for the first rule:

Excel conditional formatting ignore blanks

You’ll notice that the blank value is no longer highlighted in cell B10:

By creating this new rule, we are able to apply no conditional formatting to blank cells and ignore all other conditional formatting rules since we checked the box next to Stop if True.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: How do you ignore blank cells in conditional formatting?
  2. How to Apply Conditional Formatting to Blank Cells in Excel?
  3. How to Ignore Blank Cells When Using Formulas in Excel
  4. How do I make Google Sheets ignore blank cells with formulas
  5. How can I use conditional formatting in Google Sheets to highlight cells that are greater than or equal to a certain value?
  6. How do I apply conditional formatting to cells using VBA?
  7. How to apply conditional formatting if two cells are not equal
  8. Excel: Apply Conditional Formatting to Second Highest Value
  9. How to Apply Conditional Formatting to Dates within 6 Months in Excel
  10. How to Use Conditional Formatting in Excel