# 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:** 12\ **Page:** 2
**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 12, 2010, 1:58pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/21 "2010-03-12T13:58:23Z")
> [@CookingWithGas](#): > > Actually, this muddies things since **Green Bean** specifically asked how to produce a blank\* instead of\* FALSE. Uh, no - the formula I gave resulted in “Not Five” - not “FALSE”. I specifically said “If you leave off the “Not Five” part, it results in “FALSE””. ---
**Author:** ![RealityChuck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/realitychuck/32/195_2.png) [@RealityChuck](https://boards.straightdope.com/u/RealityChuck)\ **Post date:** [March 12, 2010, 2:31pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/22 "2010-03-12T14:31:28Z")
Let me add a question of my own. Is is possible to set up conditional formatting to check a different cell and format appropriately? For instance, format cell D5 with a green background if cell X100 contains “yes” or another designated string. ---
**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 12, 2010, 2:42pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/23 "2010-03-12T14:42:01Z")
Yes Type yes into cell x100 click on cell D5 click conditional formatting -\> New Rule click “Use a formula to determine which cells to format” in the formula box that pops up type: =X100=“yes” Click the format button Click the fill tab and select green, then click OK on the format box and click OK again at the new formatting rule box done Occasionally, Excel will screw up the necessary quotes in the rule when you do things like this. If it doesn’t work, highlight the cell and click on manage rules, select your rule and play with the way the quotes are shown in the rule - this just takes some tinkering and getting used to. In this scenario, however, it should not be necessary. It usually only pops up with more complicated formulas. ---
**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 12, 2010, 4:03pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/24 "2010-03-12T16:03:17Z")
> [@Munch](#): > > Uh, no - the formula I gave resulted in “Not Five” - not “FALSE”. I specifically said “If you leave off the “Not Five” part, it results in “FALSE””. Neither of which answered the original question. ---
**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 12, 2010, 4:08pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/25 "2010-03-12T16:08:23Z")
> [@CookingWithGas](#): > > Neither of which answered the original question. No, it didn’t. But as explained in the post, it was another way to look at the formula to try to understand it, rather than just C&P it from your post. Why is offering GB an explanation of the ins and outs of the formula provided such a threat to you? Jeez, lighten up. ---
**Author:** ![RealityChuck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/realitychuck/32/195_2.png) [@RealityChuck](https://boards.straightdope.com/u/RealityChuck)\ **Post date:** [March 12, 2010, 6:11pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/26 "2010-03-12T18:11:05Z")
> [@Darth\_Panda](#): > > Yes Thank you ---
**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 12, 2010, 6:44pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/27 "2010-03-12T18:44:21Z")
> [@Ruminator](#): > > 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. Here is a new thread on another board where this issue is discussed. > **[Is it possible to insert a TRUE blank into a cell?](https://www.excelforum.com/excel-charting-and-pivots/721547-is-it-possible-to-insert-a-true-blank-into-a-cell.html)** > > Hello, > > After searching I couldn't find a solution to this problem. > > "" or " " is not treated as a true blank by excel. It is in terms of formulas like Average(), but not when graphing. "" or " " is interpreted as a 0 data point on a graph. > ... > [@RealityChuck](#): > > Is is possible to set up conditional formatting to check a different cell and format appropriately? For instance, format cell D5 with a green background if cell X100 contains “yes” or another designated string. Yes–instead of using a value, you select “Formula.” Then your formula for formatting D5 is something like =X100=“yes” The user interface for conditional formatting changed a bit going from 2003 to 2007 so to provide more detail I would need to know which version you’re using. ---
**Author:** ![atlantic](https://avatars.discourse-cdn.com/v4/letter/a/46a35a/32.png) [@atlantic](https://boards.straightdope.com/u/atlantic)\ **Post date:** [March 17, 2010, 4:06pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/28 "2010-03-17T16:06:16Z")
Okay, new issue with this spreadsheet (and my boss’s crazy demands!!!) … instead of selecting Yes or No, my boss would like for the client to select either a green, yellow, or red light from a drop down box. He saw these options in the Icon Sets under conditional formatting. Am I able to do this in Excel? I’ve googled everything I can about icon sets and don’t think that they can be based on text. I’d like to avoid VB if at all possible. Thanks again for any input! ---
**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 17, 2010, 5:54pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/29 "2010-03-17T17:54:14Z")
Holy christ… 🙂 I don’t think you can. But you can have them select from the words “red” “yellow” and “green”, and have conditional formatting change that to “red background/red text” which will result in a completely red cell. Same for yellow and green, of course. ---
**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 17, 2010, 6:29pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/30 "2010-03-17T18:29:00Z")
> [@atlantic](#): > > Okay, new issue with this spreadsheet (and my boss’s crazy demands!!!) … instead of selecting Yes or No, my boss would like for the client to select either a green, yellow, or red light from a drop down box. He saw these options in the Icon Sets under conditional formatting. > > Am I able to do this in Excel? > > I’ve googled everything I can about icon sets and don’t think that they can be based on text. I’d like to avoid VB if at all possible. > > Thanks again for any input! You cannot have your client choose from these buttons - they are there for you to use when you do conditional formatting. It is possible to have your client use “Radio Buttons” which, in theory, could be colored - but you are deep into Macro land at this point. ---
**Author:** ![atlantic](https://avatars.discourse-cdn.com/v4/letter/a/46a35a/32.png) [@atlantic](https://boards.straightdope.com/u/atlantic)\ **Post date:** [March 17, 2010, 6:31pm UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/31 "2010-03-17T18:31:16Z")
> [@Munch](#): > > Holy christ… 🙂 I know, right?!!! I think I’ve resolved the issue, though. I told my boss that I don’t think it’s possible to use icon sets with text and he seems satisfied with that answer … for now … 😉 Thanks for your response! > [@Darth\_Panda](#): > > You cannot have your client choose from these buttons - they are there for you to use when you do conditional formatting. > > It is possible to have your client use “Radio Buttons” which, in theory, could be colored - but you are deep into Macro land at this point. And that’s what I wanted to avoid, if at all possible. He seems content with the finished product now. He saw those shiny icon sets, though, and couldn’t resist! ---
**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 18, 2010, 3:01am UTC](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849/32 "2010-03-18T03:01:00Z")
> [@atlantic](#): > > …He saw those shiny icon sets, though, and couldn’t resist! Does your boss have pointy hair? 😃 [Previous page](https://boards.straightdope.com/t/excel-conditional-formatting-question/531849.md?page=1)