Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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

share|improve this answer
THANK YOU THANK YOU THANK YOU!!! – Jennifer Jun 7 '12 at 11:02

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.

share|improve this answer
Thank you very much for your help! – Jennifer Jun 7 '12 at 11:07
@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 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.