# Excel Conditional Formatting Question **URL:** \ **Category:** Factual Questions\ **Created:** [March 9, 2010, 2:06pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849 "2010-03-09T14:06:01Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![atlantic](https://avatars.discourse-cdn.com/v4/letter/a/46a35a/32.png) [@atlantic](https://boards.straightdope.com/u/atlantic)\ **Post date:** [March 9, 2010, 2:06pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/1 "2010-03-09T14:06:01Z")
I have a spreadsheet that has three columns where a client can type in a Yes or No answer. Based on those answers, I need a cell in a different column to change color. If the answer to all three questions is Yes, the cell is green. If the answer to only two questions is Yes, the cell is yellow. If only one or no cells is Yes, the cell is red. In the past, the client has chosen either 1 for yes or 0 for no, which made formatting with a formula much easier. But we’d like to have the client simply type in Yes or No instead of 1 or 0. I’ve tried to figure this out under conditional formatting, but have had no luck. Any suggestions? ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [March 9, 2010, 2:19pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/2 "2010-03-09T14:19:18Z")
I was able to set this up as Formula Is: =COUNTIF(B2:D2,“Yes”)=1 Red etc. This relies on the lookup cells being adjacent. If they’re not, the formula would be a bit more complex, but it should be doable the same way. ---
**Author:** ![atlantic](https://avatars.discourse-cdn.com/v4/letter/a/46a35a/32.png) [@atlantic](https://boards.straightdope.com/u/atlantic)\ **Post date:** [March 9, 2010, 2:25pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/3 "2010-03-09T14:25:53Z")
That worked perfectly!!! Thanks so much! ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [March 9, 2010, 2:33pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/4 "2010-03-09T14:33:53Z")
if they’re not adjacent - or if you just want to make it needlessly complex, you could: Make the cell Red by default Plug in =IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)=2 and choose format fill yellow under use a formula Plug in =IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)=3 and have that format green changing C5, D5, and E5 to whatever cells you wanted or, if you didn’t mind having a number in the cell with the formatting, you could use the countif function or: =IF(IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)\>2,3,IF(IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)\<2,0,1)) to place a number in the cell and then use the default 3 color color scales conditional formatting, setting the min mid and max to the “number” settign and choosing values of 0,1,2 for red, yellow, green or you could use that formula in a dummy cell and then reference it with a conditional formatting, so many options SCS’s solution is much cleaner, but you can score more points with a longer formula ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 9, 2010, 2:38pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/5 "2010-03-09T14:38:36Z")
> [@SCSimmons](#): > > I was able to set this up as > Formula Is: =COUNTIF(B2:D2,“Yes”)=1 > Red > > etc. This relies on the lookup cells being adjacent. If they’re not, the formula would be a bit more complex, but it should be doable the same way. That doesn’t spit out green or yellow though, does it? ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [March 9, 2010, 2:44pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/6 "2010-03-09T14:44:28Z")
I think you would just use SCS’s formula multiple times, with defining different results as different fills. I would probably just default the cell to red and then use =2 to make it yellow and =3 to make it green ---
**Author:** ![atlantic](https://avatars.discourse-cdn.com/v4/letter/a/46a35a/32.png) [@atlantic](https://boards.straightdope.com/u/atlantic)\ **Post date:** [March 9, 2010, 2:53pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/7 "2010-03-09T14:53:01Z")
> [@Munch](#): > > That doesn’t spit out green or yellow though, does it? I used the formula with conditional formatting and changed the cell color based on the number output (green = 3, yellow = 2, red \< 2) and it worked like a charm! 🙂 ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [March 9, 2010, 3:09pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/8 "2010-03-09T15:09:52Z")
Did you put the formula in the cell, or put the formula in the conditional formatting? i.e. do you have the number of yes’s shown in the cell, or does the cell say something else? ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [March 9, 2010, 3:13pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/9 "2010-03-09T15:13:46Z")
> [@Munch](#): > > That doesn’t spit out green or yellow though, does it? That’s what the ‘etc.’ was for. I presumed that anyone already messing with conditional formatting in Excel could figure out how to write the other conditions based on that sample. 🙂 ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 9, 2010, 4:07pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/10 "2010-03-09T16:07:22Z")
> [@SCSimmons](#): > > That’s what the ‘etc.’ was for. I presumed that anyone already messing with conditional formatting in Excel could figure out how to write the other conditions based on that sample. 🙂 Ah, of course. ---
**Author:** ![atlantic](https://avatars.discourse-cdn.com/v4/letter/a/46a35a/32.png) [@atlantic](https://boards.straightdope.com/u/atlantic)\ **Post date:** [March 9, 2010, 4:21pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/11 "2010-03-09T16:21:09Z")
> [@Darth\_Panda](#): > > Did you put the formula in the cell, or put the formula in the conditional formatting? > > i.e. do you have the number of yes’s shown in the cell, or does the cell say something else? I put the formula in conditional formatting. I have three rules set up, one for each color and the color changes based on the number of yes’s. ---
**Author:** ![Green\_Bean](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/green_bean/32/1760_2.png) [@Green\_Bean](https://boards.straightdope.com/u/Green_Bean)\ **Post date:** [March 9, 2010, 4:47pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/12 "2010-03-09T16:47:23Z")
Hey, thanks. I’d been wondering how to do this. An additional question: If the “If” value is false, is there a way to make it so that it leaves the cell blank instead of showing “FALSE” or a zero? In other words, how do you make a formula return a blank? (Other than doing conditional formatting so that if the cell value is “0” then the font turns white. 😛 ) ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [March 9, 2010, 5:41pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/13 "2010-03-09T17:41:47Z")
> [@Green\_Bean](#): > > Hey, thanks. I’d been wondering how to do this. > > An additional question: > > If the “If” value is false, is there a way to make it so that it leaves the cell blank instead of showing “FALSE” or a zero? > > In other words, how do you make a formula return a blank? Much different question than the OP but the idea is =IF(A1=5,“It’s Five”,“”) \*\*ETA: \*\*Note that is a blank cell in the sense of “the users sees a blank cell.” It will not be considered blank for purposes of COUNTA, for example. ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [March 9, 2010, 5:51pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/14 "2010-03-09T17:51:30Z")
But if you want to count the number of cells in a range that aren’t blank where you have cells that are “blank” from having a conditional statement where the result is “”, then just use counta(range)-countif(range,"") However, if you have legitimately blank cells in there (just plain old empty), you will need to change those cells to ="" otherwise your count will come out too low as they will be ‘double-counted’ as blank ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [March 9, 2010, 6:23pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/15 "2010-03-09T18:23:28Z")
[QUOTE=Green Bean] In other words, how do you make a formula return a blank? [/QUOTE] > [@CookingWithGas](#): > > =IF(A1=5,“It’s Five”,“”) Yup. To better understand it, try reading the equation as the following (the parentheticals are what the text is referring to): “If the cell I’m pointing to (A1) equals 5 (A1=5), then (the comma) say ‘It’s Five’, if not (the next comma), then say nothing (the “” part).” It might be more clear if the equation was: =IF(A1=5,“It’s Five”,“It’s Not Five”) You can leave off the final clause (“It’s Not Five”) and if A1 isn’t 5 then it’ll spit out “FALSE”. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [March 9, 2010, 10:41pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/16 "2010-03-09T22:41:36Z")
> [@Munch](#): > > It might be more clear if the equation was: > > =IF(A1=5,“It’s Five”,“It’s Not Five”) > > You can leave off the final clause (“It’s Not Five”) and if A1 isn’t 5 then it’ll spit out “FALSE”. Actually, this muddies things since **Green Bean** specifically asked how to produce a blank\* instead of\* FALSE. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [March 9, 2010, 11:38pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/17 "2010-03-09T23:38:32Z")
> [@Green\_Bean](#): > > In other words, how do you make a formula return a blank? This is a common question and the answer is that Excel formulas can’t return an empty cell. You can do it with VBA but that’s a different beast. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [March 10, 2010, 3:07am UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/18 "2010-03-10T03:07:53Z")
> [@Ruminator](#): > > This is a common question and the answer is that Excel formulas can’t return an empty cell. You can do it with VBA but that’s a different beast. But the question wasn’t how to make the cell empty, it was how to return a blank. Depends on why you need it blank, but blank and non-empty is perfectly appropriate for many purposes. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [March 10, 2010, 3:22am UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/19 "2010-03-10T03:22:25Z")
I interpreted the question like this… suppose in cell F5 you have a formula like this: ```auto =if(B5 <> A5, "different!", "") ``` …for most purposes, the zero-length string “” is good enough. However, there is no function or special magic constant to replace the double quotes “” to make the cell F5 act as if there was nothing ever put there – empty or NULL (or “blank” in casual parlance) in other words. That’s how I interpreted “blank” in his question. I guess I read the question that way because anybody who ever asks about blank usually already knows about the the zero-length string. The poster inevitably responds with, \*“I already know about the double quote trick but that doesn’t exactly do what I want blah blah \*blah…” ---
**Author:** ![Green\_Bean](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/green_bean/32/1760_2.png) [@Green\_Bean](https://boards.straightdope.com/u/Green_Bean)\ **Post date:** [March 12, 2010, 5:52am UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/20 "2010-03-12T05:52:46Z")
Well, if it makes you feel better, I didn’t know about the double-quote thing. 🙂 Thanks for your help, everyone. [Next page](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849.md?page=2)