It’s often a good idea to highlight empty cells in a data set to help easily identify the outliers. Here, you can find out how to highlight empty cells dynamically using Conditional Formatting.


  1. Begin by selecting the data range B3:H10
  2. Open the Conditional Formatting window from HOME > Conditional Formatting > Add New Rule
  3. Select Format only cells that contain
  4. Select Blanks in Format only cells with dropdown
  5. Click the Format button to edit formatting settings
  6. Click OK to continue and apply your settings


The conditional formatting feature applies selected formatting options to a cell, when a given condition is met. Excel provides several pre-defined settings you can choose from, and also allows defining your own rules.

How to highlight empty cells - 2

One of the settings is format only cells that contains specific values. You can set a specific format to a cell whether it contains a  specific value or has errors. In this example, using the Blanks setting makes Excel change the cells formats that don't contain any values. To highlight a cell, set a Fill color that is to be your highlight color. For other types of cell highlighting, also see How to highlight