Highlight Cell if Cell in Other Columns are Blank

In this article, I will explain how you can format a cell if cell/s are blank in other columns. To understand this conditional formatting in excel let's have an example.
Example: Highlight Average Cell if Test Column is Blank

Here, I have data on test scores. I conducted three tests. In column E, I am calculating the average scores of students. In some cases, students have missed the test and I want to highlight the average score if the student has missed any test. 

In Excel words, highlight the cell in column E if the adjacent cell of column B, C, or D is blank.

  • Select the average column (E2:E7).
  • Go to Home ? Conditional Formatting ? New Rule
  • Here select the "Use formula to determine which cell to highlight" option.
  • In the formula box, write this formula.
  • =COUNTBLANK($B2:$D2)

  • Choose the formatting of the cell if the condition falls true. I have chosen Yellow fill.
  • Hit the OK button.

And it is done. You can see that the average scores are highlighted of those students who have missed any test.

How does it work?

Here we have used the COUNTBLANK function that counts the number of blank cells in a range. 

In conditional formatting, any number greater than 0 is considered True.

Here the formula is COUNTBLANK($B2:$D2). We have locked columns and left rows relative. It makes column absolute and allows rows to change. Now, since B2:D2 contains a blank cell, the function returns 1. Which translates to TRUE. Hence we get a yellow filled average cell. In B3:D3, there's no blank cells and the function returns FALSE and the cell doesn't turn yellow.

ISBLANK Method

The same thing can be done with another formula. 

=ISBLANK($B2)+ISBLANK($C2)+ISBLANK($D2)

The ISBLANK function returns TRUE if a cell is blank else it returns FALLS. If we add TRUE-FALSE values it acts like an OR function. So, if any function cell is blank the formula above will return TRUE. In return, the cell will be filled with yellow colour.

The same thing can be done with the formula below.

=OR(ISBLANK($B2),ISBLANK($C2),ISBLANK($D2))

Another equivalent formula is

=OR($B2="",$C2="",$D2="")

In the above two methods, we are checking individual cells if they are blank or not. We have encapsulated these checks with the OR function. As per the OR function property, it returns true if any of the tests return true.

So yeah, this is how you can do conditional formatting of the cells if a cell in other columns is blank. I hope this article was explanatory enough to explain the procedure of column conditional formatting in excel. You can click on the function names in the formula to read about that function. If you have any specific problem in excel, mention it in the comments section below.

Related Articles:

Conditional formatting based on another cell value

IF and Conditional formatting

Conditional Formatting with formula 2016

Highlight cells that contain specific text

Popular Articles:

50 Excel Shortcut’s to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube