Conditional Formatting for Graphs and Charts in Excel
HTML-код
- Опубликовано: 21 авг 2024
- You cannot natively add conditional formatting to chart objects in Excel. Maybe someone can bug them about that. Until then, this is one of a number of hacks you can do to highlight and change the colour of charts based on the value.
Hoping you can help me. I want to highlight cells a1-d1 if cell e1 equals “apple” OR “pear”. I’m having trouble getting the OR function to work on specific words. I’ve successfully used CF with single words. For example if cell E1 = grey, highlight cells A1-D1 a grey colour. I run into trouble with OR. My formula syntax is…. $E1=OR(“apple”,”pear”). Am I missing something in the syntax or is it a misapplication of the OR function?
OR() needs two or more statements that evaluate as true or false.
You need the conditional formatting formula to read something like =OR(E1="apple", E1="pear"), as those statements should evaluate as TRUE or FALSE. You will need to check the use of $, too.
Any other issues with AND() and OR() can usually be resolved by using * and + instead, which treats TRUE as 1 and FALSE as 0.
Thank you Chris. You might want to do a video on this, since i was unable to find anyone talking about my scenario. I can’t be the first to have encountered this. Using the OR function keeps my CF simplified and uncluttered…. Trying it out now.
You might find it useful to try:
=NOT(ISERROR(MATCH($E$1,$F$1:$F$2,0)))
Where F1:F2 contains your list, "apple", "pear". Here, it looks for it in a list, and if it isn't found, it returns an error. So the NOT() ISERROR() combination will return TRUE if it's found.
That way, you can extend your choices to >2 entries more easily. Especially if you replace $F$1:$F$1 with a dynamic array that automatically counts and expands to the right number of entries, which can be done with OFFSET(). So if you add "banana" to F3, it would expand to include that. Obviously, stash that somewhere innocuous, but easy to access.