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

Can someone help me please with a conditional format that would highlight an entire row in Excel 2010 if column B on my spreadsheet has any of these Letters (A,B,C,D,E,F,G,H,Z) and column C has the word "TOTAL" in it?

My Data example:

ColA ColB  ColC    ColD
1    A     TOTAL   334535 (This enire row would be Blue, the letter is a District letter) 
2    B     206448  334535 
3    B     TOTAL   334535 (This entire row would be Blue, the letter is a district letter) 
4    Z     TOTAL   334535 (This entire row would be yellow, this letter is a Zone letter) 
5    C     203837  334535 
share|improve this question
up vote 0 down vote accepted

I hope I have interpreted your description correctly. I am assuming that A-H are District letters and Z is a Zone letter. The conditional format formulas are:


=AND($C1="TOTAL", OR($B1="A",$B1="B",$B1="C",$B1="D",$B1="E",$B1="F",$B1="G",$B1="H"))


=AND($C1="TOTAL", $B1="Z")

I have applied these formulas to the entire sheet, so any more rows you add will automatically apply any colors if necessary.


Here is a download link:

share|improve this answer
YAY!!!! You are fantatic! Thank you so much! – Rebecca Rickey Aug 4 '13 at 22:55

For a case-sensitive match on the zone, district and total codes, you would use the following formulas in your conditional formats:

Blue:    =IF(AND(OR(AND(CODE($B1)>=CODE("A"),CODE($B1)<=CODE("H"))), 

Yellow:  =IF(AND(CODE($B1)=CODE("Z"),EXACT($C1,"TOTAL")),1,0)   
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.