Excel Request - Different Colour Bars in Chart

[wndsr]

Looking for a bit of help from an Excel pro before I pull my hair out. I'm using the latest version of Excel for Mac.

I have a list of approximately 45 names and each name has a number next to it.

The names are grouped into three categories - 1, 2 and 3. I want all the bars for 'Group 1' to be one colour, all of Group 2 to be another and so on.

Is there a way to do this without manually changing the colour of each and every bar on the chart?

Cheers!

 

[wndsr]

I don't think I made myself very clear, my bad. I'm not sure how conditional formatting would achieve what I am after.

The photo shows a snippet of the data I am working with and is formatted exactly as it is in my data sheet. I basically want to be able to automatically make the columns of each group the same colour. At the moment it just makes them all blue. I've tried adding a new series for each group, but for whatever reason that doesn't work.
Take a look here: