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.

`=LEN(A2)=0`

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.

`=$A1=""`

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.

`=$B2=""`

I need to input dates in the dates column and name in the other I would like the color to highlight rows for quarters per the 2018 color chart, can this be done with conditional formatting?

Hi! I am trying to do a conditional formatting where if one column doesn’t follow the in another column to the value in the third column it will highlight it

I am having an issue trying to figure out some conditional formatting for a spreadsheet. It is more than just one function. It’s either an ifs string or and/if string I think. But you are the experts. Can you help??

Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?

Is there an actual function that changes cell colour rather than Conditional Formatting? It wouldn’t work in this situation. I basically have an IF statement and I want the TRUE result to be the colour of the cell changing. Ive tried setting it to 1, then setting a rule where all 1 become that colour, but nothing happens. I’ve tried it with and without the quotations.

This was helpful! I’m looking for a formula that I can set up for conditional formatting to highlight an entire row of cells based on the condition of two specific values. I need this rule to have 8 variant outcomes as well, differentiated by color.

Hello. I’m looking for a conditional formatting formula to highlight a range of cells based on value of another cell range. I’m using Google Sheets.

Hi,

I have a 6 month calendar which goes across in 1 row (September- February). I am trying to highlight certain blank cells underneath a certain date. Is this possible?

Hey Emma, thanks for you question! Myself and other Excel experts who write these blogs are also online and available to help. Ask me your question here: http://www.excelchat.co and I’ll be happy to help out!