Monday, April 23, 2012

Highlight First Blank Cell With Conditional Formatting

Hey, just wanted to share a quick trick about conditional formatting.

If you have a data entry sheet with input in adjacent cells within the same row or column, and you want the next blank input cell to be highlighted with a color, you can apply conditional formatting based on a formula.


The following formulas assume that your input is starting from cell A1 and all the input is in adjacent cells:
  • Vertical data entry:   =ROW(A1)=COUNTA(A:A)+1
  • Horizontal data entry:   =COLUMN(A1)=COUNTA(1:1)+1
If there are empty cells before the actual input range, you need to modify the formula. Increase the "+1" with every empty cell above (or to the left of) the actual input range. This type of conditional formatting can be applied on the entire column, entire row, or custom range, and there would only be one highlighted cell at a time.

Excel Geek insiders will get the sample file from Eric.

3 comments:

Let us relax said...

I had to adjust your formula to =COLUMN(A1)=COUNTA(1:1) to get the desired results.

I actually prefer to use =AND(A$1<>"",B$1="") which gives the desired results as well as highlighting empty cells not at the end of the range in yellow.

Gadi said...

The goal of the formula was to highlight the next empty cell within a consecutive input of adjacent cells. For other specific scenarios the formulas can be adjusted to fit the need.

Your alternative A&B formula is not highlighting empty cells anywhere, it's always highlighting non-blank cells.

Planilhas Excel said...

That's a very creative tip, well done!