0

I am trying to define conditional formatting on a bar chart in excel based on the value of the data point or his legend. I want to make it dynamic. Let's suppose that I have the following chart

3 Alex 4 John 6 David

I want to color the bar ABOVE "John" in red.

Any ideas ?

Thanks

Avidan Cohen
  • 1
  • 1
  • for reference https://stackoverflow.com/a/13868707/2727437 – Marcucciboy2 Aug 28 '18 at 14:33
  • @Marcucciboy2 VBA is unnecessarily complicated for this task, especially if you want this to be dynamic – Dan Aug 28 '18 at 14:36
  • @Dan meant to link the question to include both answers, my b – Marcucciboy2 Aug 28 '18 at 14:40
  • thanks but i dont want to arrange the data i need this to be dynamic but i dont find a vba script – Avidan Cohen Aug 28 '18 at 14:41
  • @Avidan, you can leave the data in place and just duplicate it but in the right arrangement using formulae. If you want this to be dynamic, this is the only reasonable option. If you just want to change the colour for John, well that's trivial to do interactively... – Dan Aug 28 '18 at 14:43

1 Answers1

0

Jon Peltier has a great article showing how you can do this: https://peltiertech.com/conditional-formatting-of-excel-charts/

In essence, just use two different series with data like so:

  | A     | B | C |
-------------------
1 | Alex  | 3 |   |
2 | John  |   | 4 |
3 | David | 6 |   |

So you plot a bar chart with 2 series, the first using column B and the second (red) using column C. If you have some rule that decides who should be red, then just use =IF() instead of hard coding the numbers

Dan
  • 45,079
  • 17
  • 88
  • 157