Using the conditional formatting rule you can easily consistent formatting across a large number of cells/ rows/columns. One such example is a conditional formatting rule when the cells do not contain any value. There are two methods to conditionally format blank cells in Excel, which we are going to discuss here.
Using Built-in Rule
You can easily format blank cells in Excel by choosing a built-in rule type. Select your data range, click on Conditional Formatting Menu and select “New Rule”.
Choose the rule type “Format only cells that contain” and then select “Blanks” from Format Only Cells with options. Select the Format type to preview, like Fill, Font or Border, and apply to take effect, and you will get all blank cells formatted in selected data range as you can see the result in the second picture.
Using this method you can highlight both “blank” and “visually empty” cells in your data range.
For example, If your cell contains any formula that returns in an empty string = IF(A2=1,”OK”,” ”), then such a cell will be “visually empty”, but not “blank” as it returns an empty string with space. Whatever, the cell is blank or empty, you can highlight these cells by applying this method.
Using Custom Formula
You can also format blank cells in selected data range by using a formula to determine which cells to format. There are multiple ways to conditionally highlight the desired data range using this rule type.
If you want to highlight blank cells in selected data range A2:C13 using formula, then apply any of the following formulas in conditional formatting.
The LEN function returns the length of the text as a number. The formula LEN(A1)=0, will return TRUE for “blank” cells only, and not for “visually empty” cells
= ISBLANK (A2)
The ISBLANK function returns the TRUE where cell blank. The formula ISBLANK(A2) will return TRUE for “blank” cells only, and not for “visually empty” cells.
For example, if your cell contains any formula that returns in an empty string = IF(A2=1,”OK”,” ”), then such a cell will be “visually empty”, but not “blank” as it returns an empty string with space. Therefore, both LEN and ISBLANK functions will not see these cells as blank, and will not highlight those cells.
When you use a formula to apply conditional formatting it’s important that conditional formatting formulas be entered relative to the active cell in the selection, which is A2 in this case. This address will be updated when the formula is applied, so conditional formatting will be applied to each cell of the selected range.
If you want to highlight cells of a certain column if any corresponding cell in another column is blank, first you need to select the data range that you want to conditionally highlight and then use following the formula in conditional formatting.
This formula will format selected cells if a corresponding cell in a Column is blank.
In below example, if you want to highlight those invoices in column A, where corresponding Date cell in Column B is blank, then select data range in column A, and apply following formula in conditional formatting. This formula will highlight those cells in column A only where corresponding Date cell in column B is blank as shown below.