Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there a way to highlight a cell (possibly with conditional formatting) if one of the cells to the left of it has data, but it is empty?

for instance,

     A     B     C     D     
   ________________________
1 |     |     |     |  x  |
2 |     |  x  |     |     |
3 |  x  |     |     |     |
4 |     |     |     |     |
5 |     |     |     |     |

In the example, cells C2, D2, B3, C3, D3 would be highlighted because they are empty but there is a value in a cell to the left of it

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Select the range you want to apply the conditional formatting rule to and go to Conditional Formatting on the Home ribbon, then New Rule.... From there choose Use a formula to determine which cells to format and enter the formula below.

=AND(COUNTA($A1:A1)=1,A1="")

Here A1 is the top-left cell of the selected range. Adjust the formula to fit your needs.

Next set the desired format/highlighting and click OK.

share|improve this answer

To highlight the cells in col B which appear next to a blank cell in col A:

  • Put the cursor on B1 and select Conditional Formatting -> New rule.
  • Select the last option in the list of rule types, "Use a formula to determine..."
  • In the field that says "Format values where this formula is true:" enter "=IF(ISBLANK(A1),TRUE,FALSE)" (see screenshot)
  • Click the format button to select exactly how you want the highlighted cells to appear
  • Click OK, then OK again to exit the Rules Manager.
  • Right click on B2 and select the Format painter (see screenshot) then drag down the column to apply that rule to the rest of the column as needed.

The rule settings

Select Format Painter

Final result

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.