Use Stacked Columns to Colour by Category

Have you ever tried to create a bar chart, and changed the colours of a few of the bars, only to be frustrated that some of the colours reset when you made other changes?  There is a much more stable way to introduce a range of colours into a bar chart, and maintain control over which bars are which colours.  The answer: use stacked columns.  Let me show you how.

Let's say we want to create a chart showing the results of the primary vote for the candidates in the seat of Flynn in the recent Australian federal election, and you wanted to colour each bar depending on the colours associated with those parties:

This chart is not a standard column chart, but rather a stacked column chart, with a series for each of the colours you see: six for each named party, and one grey-coloured series for the two independents.

Look how I've sorted the data below:

Let me break it down:

1. In column A, I have a list of the candidates.
2. In row 1, from column B to column H, I've created names for the series that will represent the different colours.  I've used the party name abbreviations, but I could have listed the colour names too, for example.
3. Then, in each row, for each candidate, I placed the result figure into the column that represents the party or colour that I want for that candidate.

Next, to create the chart:

1. Select the entire data table
2. Go to the Insert menu
3. Select the column chart drop-down,
4. In the 2-D Column section, select the stacked column chart (middle option)
5. Now set the colour you want for each data series:
1. Double-click the series (the Format Data Series box should appear on the right)
2. Select the Fill and Line Icon (left-most option)
3. Use the FILL section and the BORDER section to format that series.

This method seems like hard work.  Why is it so much better?  Formatting individual bars in a chart can actually take longer than this method when you have multiple bars.  More than that: the changes can easily reset when you make a change - deliberately or accidentally - to the data series.

When you want multiple colours together in a column chart: think stacked columns.