I am new to conditional formatting and having a hard time. I have 6 columns with 100 rows. What I would like to have happen is to highlight the row in one color if there is no data in it at all. If there is data in one cell within the row, however, I would like for the highlighting to be removed from the row completely. Currently I have it set up to highlight the entire row if there is no data in it and if there is data in one cell, only that cell has no highlighting....I can't seem to make the entire row's highlighting disappear.

I have used the formula to determine which cells to format:

=I16:N16="" formatting color is yellow.

I know I have to add a second conditional format but I have tried numerous different formulas and cant seem to get it to work.

  • Ms Excel, I presume? – Dennis Jun 6 '12 at 18:59

Assuming data starts at row 16 then select the whole range (I16:N115?) and use this formula in conditional formatting


The $ signs are required as shown

Note: this formula should also work


....but that's an example of an "array formula" which Excel sometimes mismanages in conditional formatting (particularly Excel 2007 I believe) so I recommend the above version with COUNTIF


For only six columns, you can check each cell individually. Assuming your data starts at I16, this should do the trick:

=NOT(AND($I16="", $J16="", $K16="", $L16="", $M16="", $N16=""))

This will return true for those rows where all six cells (columns A-F) are empty.

See the documentation on the AND() and NOT() functions for more information.

  • @Jennifer No problem. Please accept the answer that helped you most (I'd recommend barry houdini's answer below, it's a more elegant solution) by clicking on the checkbox outline next to it. – Indrek Jun 7 '12 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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