Avoid Legends With Axis Colours

When plotting two variables with different units of measure, you plot one of the series on the secondary axis.  The best way to clearly link each axis to its series plot, is to match the colour of each axis line to the colour of the series plot.

Let me show you how.

This chart compares the Australian ASX200 index with the US S&P 500 index for the past 26+ years.  It's not hard to tell which axis is for with which line is it?

The value of this tip is best demonstrated by comparing this chart with a more standard axis and legend format:

Optional: I've chosen to label each axis, and therefore label each line by implication, based on colour.  You could also label each line, and therefore label each axis by implication, based on colour.

Here's the detail on how to implement this tip:

1. Set-up. First, set up your series on the primary and secondary axes, and scale them appropriately.  Colour each series with strong, dark colours, as you need these colours for the text of axis labels.  Remove the legend (if one appeared by default).
2. Label the primary and secondary axes.
3. Select one of the axis labels, go to the HOME tab, and format the text in the Font group:
• Set the font colour to the same colour as the series, or a stronger tone of that colour.
• Set the font to bold, as this helps improve the readability of the coloured text.
• Increase the font size to something legible, as the default is always too small.
4. Select the other axis label, and format the font to the same style, but obviously with the other colour.
5. Select one of the axes, and format the font for the number labels using the same method as for the axis labels.  Obviously, the font size will need to be a little smaller than the axis labels.
6. While this axis is selected, format the axis line itself by selecting the FORMAT menu in the CHART TOOLS menu group, then selecting "Format Selection" in the (left-most) "Current Selection" group:
• Select the (left-most) "Fill & Line" tab (the pouring paint-pot symbol).
• Expand the LINE option.
• Select the Solid line radio button.
• Set the Color option to the selected colour for this series and axis.
• Adjust the Width option to make the axis line thicker and more visible.  Experiment, but 2 points is a good place to start.  Tip: you need to select something other than the axis to see your changes to the axis properly, and then re-select the axis to change it again.
7. Format the number labels and the axis line of the other axis using consistent fonts and line styles, but obviously with the other colour.

By this stage, you're probably wondering whether it was worth all the effort.  For each case, that depends ultimately on your audience.  But I'd encourage you to take the time to learn and frequently apply these skills.  Soon you'll be able to create higher quality charts - for any audience - within only a few minutes.

Colour-Matching Text Descriptions

The best line charts don't include the default chart legend box.   There are more intuitive ways to communicate what your lines represent.  One of the best is methods is to place colour-matching text descriptions beside each line.

Let me show you what I mean.

Don't do this:

Instead, do this:

I've done all I can to make the legend in the first example work well: I've ordered the series so that they sit in the same order on the chart as they do in the legend, and I moved the legend up into some blank space in the chart area.  Still, you still need to look from the lines to the legend to make sense of the chart.  Can you see how in the second example, the descriptions are right next to to each line, in the same colour as the line?

How to do it?  This is really simple:

1. Select and delete the legend.
2. With the chart selected, go to the INSERT menu, and
3. Select Text Box.
4. Place the text box inside your chart.
5. Enter the description for the line you're annotating.
6. Format the text: select a font and size, and select the font colour.  I recommend bold type for this.
7. Optional: format the fill of the text box to solid white (or your chart's background colour) to prevent the gridlines from coming too close to the text.  Be careful to trim your text box neatly if you do this.
8. Select your text box, press Ctrl, then click and drag to create a copy of the text box.  Use this as the starting point (same font, size, etc) for the next line annotation.

Edit (5/6/2019) - Updated the label for the MTBS line on the second chart, as the previous version had this line also labelled as MTBF.  Thanks to Joey for pointing this out.

Push Volatile Data Into the Background

Some data is so volatile from period to period that there's less meaning in the fluctuations then there is in the long-term trend.  Moving averages clearly show the long-term trend, but they move so slowly each period that they don't provide the talking-points your manager desperately wants.  Let me show you how to show a long-term trend, while keeping the volatile, talking-point data on the page, without causing visual noise.  Push volatile data into the background.

This chart shows the six-month moving average production rates of two plants:

This chart is clean and simple, but says a lot:

• Thick moving average lines. The solid, slow-moving lines are clearly the six-month moving averages for Alpha Plant and Bravo Plant, as stated in the chart heading.
• No Horizontal Axis Explanation Needed.  The data is monthly.  The horizontal axis labeling makes this clear, so it doesn't need to be explicitly stated in the heading, or even with an axis label.
• Simple Vertical Axis Label. The units of production rate are described with an axis label.  This is explicit to avoid confusion, but subtle, because your audience usually knows what you're measuring.
• Volatile Data in Background. In the same colour, behind each moving-average line, is the volatile, monthly data.  I've done a few things to de-emphasise these series:
• Moved behind. Each monthly data line is literally behind the moving average line.
• Thinner line. Fat lines are great for moving averages because they emphasise the imprecise, slow-moving nature of the data.  Conversely, very thin lines look precise, but also less important. 
• Hollow data points.  Using data points emphases the discrete nature of the data - each value stands alone.  I think white-filled round markers are great for this.
• Dashed lines.  The dashed line helps to de-emphases the volatile series.  It also helps to guide the semi-random walk from one data point to the next.  You could remove the line entirely, except I find the dashed line is helpful for the eye to follow along from point to point, and it "joins" the data to its moving average.

How to?  Read on if you need some step-by-step help to achieve these results.

• Move data series behind another.  This is done by changing the order of the series in the "Select Data Source" dialog box in the DESIGN tab of the CHART TOOLS tab group.
• Make data series lines thinner, and dashed. This is done by:
• Selecting the series, and then "Format Selection" in the FORMAT tab of the CHART TOOLS tab group.
• Select the "Fill and Line" tab
• Select and expand the LINE section.
• Adjust the Width option to set the number of points of line width that looks right.
• Select the "Dash type" drop-down to select different dashed line options.
• Create hollow data points.  This is done by:
• Selecting the series, and then "Format Selection" in the FORMAT tab of the CHART TOOLS tab group.
• Select the "Fill and Line" tab
• Select and expand the MARKER section.
• Expand the MARKER OPTIONS section.
• Select the "Built-in" radio button, and choose the Type and Size of the marker you want.
• Expand the FILL section below.
• Select the "Solid fill" radio button, then pick the white colour.
• Expand the BORDER section below.
• Select the "Solid line" radio button, then select the colour for the data series.

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.