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

I have 2 columns "N" and "O" . Both columns can contain "x". If column N has an x and column O is null then highlight the line. This should apply to the entire worksheet. Thanks so much in advance.

share|improve this question
What version of Excel do you have? – David Dec 5 '12 at 21:43
So you logic is: IF( N contains "x" AND column N+1 = null) Then highlight the entire line? – David Dec 5 '12 at 21:45
@Jikag - looks like Excel 2007 ;-) – pnuts Dec 5 '12 at 22:43

2 Answers 2

The easiest way I know of to do this would be by using a filter to hide the rows you don't want to highlight.

  1. Begin by highlighting your desired data, including the header row.

  2. Press the Sort & Filter button and select Filter. This will put little dropdown arrows on all the row headers.

  3. Click the dropdown for column N and alter the checkboxes so only X is checked. This will filter all the rows so it hides anything without an X in column N.

  4. Next, click the dropdown for column N and uncheck everything except the (Blanks) option. This will hide any rows that are not blank in column N.

  5. The resulting visible rows are the ones you want highlighted. Simply select them all and apply the your desired highlighting.

  6. To show all lines again, click the Sort & Filter button and uncheck the Filter option.

share|improve this answer

To have the highlighting change along with any data changes, you could use conditional formatting with a rule such as

=AND($N1="x",ISBLANK($O1)) applied to =$1:$17

(17 here is the assumed total number of relevant rows).

SU515355 example

share|improve this answer

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.