I have a simple bar graph that displays the number of points a set group of people have attained in different competitions, eg, John 10, Lisa, 12, Paul 16, etc; with each person having their bar coloured specific to them, so John's bar is red, Lisa's always blue etc,. I realised once done that I didn't like the haphazard look of the bars, thinking it would be much better to sort the data so the bars were descending in order from left to right.

I've now done this and it does indeed look much better (and the order of the bars also reflects the 1st, 2nd, 3rd place score) but the problem is I cannot figure out how to ensure the colour of each bar remains specific to the individual. The bars all display in descending order but the bar colour doesn't stay with the relevant person, so for example, when John is 1st his bar colour is red, but when he's 3rd it is blue.

So I'm either stuck with having specific colours but the data spread without order across the chart, or displaying them in descending order but without specific bar colours. Is there any way I can fix this? Preferably using formulas as my VBA experience is near zero, but any help would be much appreciated.

I'm using Excel 2010

Thanks