Conditional Fill Colors for Bar Charts

Question

Conditional Fill Colors for Bar Charts

Numbers, version 13.2 (7038.0.87)

Im interested in using conditional highlighting or similar method to dynamically change the fill color of bars within a bar chart.

For example red fill if bar value is under 20%, orange if between 20% - 60% and green if over 60%.

I have seen some creative workarounds posted here from 2015 with conditionally highlighted cells overlayed with background color matched bar graphs to utilize negative space but it's a lot of hacking about.

I am curious if there's any sophisticated solutions that might look like this:

Posted on Feb 1, 2024 8:32 PM

Reply

Answer 1

Top-ranking reply

Ralf-F

Level 4

2,741 points

Feb 3, 2024 9:05 AM in response to escapevelocity_

Hi escapevelocity,

you could try this option, but you would need 3 support columns for your 3 groups.

C2=IF($B2<20%,$B2,"-")

D2=IF(AND($B2≥20%,B2≤60%),$B2,"-")

E2=IF($B2>60%,$B2,"-")

Based on the value from column B the data will be displayed in the different columns.

Now you create a 2D Stacked Bar Chart for column C, D and E.

Set the right colors to your chart and format the rest based on your preferences.

It will only show one bar, because the other cells are always empty (have no value).

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Regards Ralf

Reply

Answer 2

Feb 9, 2024 5:29 PM in response to Ralf-F

Hi Ralf-F, that is very clever, thank you!

Reply

Conditional Fill Colors for Bar Charts

Similar questions