Column Chart with Primary and Secondary Axes

Column Chart with Primary and Secondary Axes:
Use a Panel Chart Instead

It’s hard to make column charts with primary and secondary axes; special steps must be taken to prevent secondary columns from obscuring primary columns. Such charts are even harder to read than most two-axis charts, probably because the primary and secondary columns are interspersed. You can avoid these problems if you use a panel chart instead.

What People (Think They) Want:
Column Chart with Two Axes

Let’s use some simple data to illustrate the situation. Here the Secondary data is more than an order of magnitude greater than the Primary data.

Columns on Two Axes Data

When plotted together on a single axis, we can’t tell much about the Primary data, because it is overshadowed by the Secondary data.

Columns on One Axis

Aha! We’ll just plot the Secondary data on the Secondary axis, despite Jon’s earlier warnings about Secondary Axes in Charts.

Overlapping Columns on Two Axis

Now the Secondary data really obscures the Primary data. This is where people usually get stuck with their chart.

Of course, it’s possible to add dummy series to the chart to provide adequate spacing.

Fixed Columns on Two Axes

I’ve found that plotting data on two different axes makes it hard for the reader to determine and recall which axis goes with which data. It’s even more confusing with bars that alternate between axes. No matter how you try to clarify the chart, using axis labels that coordinate with the data (shown below), using arrows (not shown), people still mix them up.

Gussied Up Columns on Two Axes

This is added to the usual confusion with two axes. I’ve had people (smart people, mind you) look at the chart, and their takeaway was that Secondary started lower then Primary, but finished higher.

Ah, the perils of a chart with primary and secondary axes.

What People Really Should Use:
Panel Chart

It’s okay to use primary and secondary axes in the same chart, but to avoid confusion, it’s best to separate them into separate panels of the chart. This is the protocol for creating such a panel chart.

We’ll use the same data.

Columns on Two Axes Data

Start by making a plain old column chart.

Columns on Two Panels - Step 1

Format the Secondary series so it is plotted on the Secondary Axis.

Columns on Two Panels - Step 2

The Primary axis is scaled from 0 to 10, and the Secondary axis from 0 to 200. We need to adjust these scales so the Primary panel is in the bottom half of the chart, and the secondary panel in the top half. If the primary panel has to be 0 to 10 in the bottom half of the chart, we need another 10 units on top, so the total primary scale should be 0 to 20. If the secondary panel has to be 0 to 200 in the top half of the chart, we need another 200 units below this, so the secondary scale should be -200 to 200.

Format the primary and secondary vertical axes according to these computations. If you use a major unit of 2 for the primary axis and of 40 for the secondary axis, both sets of labels line up with the primary horizontal gridlines.

Columns on Two Panels - Step 3

We don’t want to display primary axis labels in the secondary panel or secondary labels in the primary panel. We can use custom number formats to display only the desired axis labels.

In general, number formats have four elements, separated by semicolons. The first element shows the format to be used for a particular numerical situation, the second for another particular numerical situation, the third for all other numerical values, and the fourth for alphanumeric labels:

[first condition]format;[second condition]format;format;alphanumeric

If conditions are not specified, the first condition is positive numbers and the second is negative number, leaving the third for zero values.

This makes the custom number format for the secondary axis very easy:

0;;0;@

Positive numbers (first element) and zero values (third element) will be displayed as numbers with no decimal digits (i.e., “0”), negative numbers (missing second element) will not be displayed, and alphanumerics (fourth element) will be displayed as themselves (“@”). You could leave off the “@”, and alphanumeric values will also not be displayed.

The custom number format for the primary axis is not much more difficult, now that you know the system:

[<=10]0;;;@

Values less than or equal to 10 (first element) will be displayed as numbers with no decimal digits (i.e., “0”), any other values (missing second and third elements) will not be displayed, and alphanumerics (fourth element) will be displayed as themselves (“@”), or omitted if desired (omit the “@”).

Format the primary and secondary axes in turn, and assign the applicable custom number format to each.

Columns on Two Panels - Step 4

We’ll want to visually separate the two panels. Excel only gave us the secondary vertical axis, but we’ll add the secondary horizontal axis, and position that between the panels (at Y=0 on the secondary vertical axis).

First, format the gridlines to use a lighter shade of gray, and the primary horizontal axis to use a darker shade of gray (but not too dark, no need to use harsh black lines).

Columns on Two Panels - Step 5

Using the plus icon (Excel 2013) or the Chart Tools > Layout tab > Axes control (Excel 2007/2010), add the secondary horizontal axis. Excel puts it at the top of the chart by default.

Columns on Two Panels - Step 6

Format the secondary horizontal axis so it uses the same gray line color as the primary horizontal axis. Also format it so it has no labels and no tickmarks.

Columns on Two Panels - Step 7

Format the secondary vertical axis (right side of chart) so the horizontal axis crosses at the automatic position (zero).

Columns on Two Panels - Step 8

Nobody says you need to keep the original aspect ratio of your chart. You can make it narrower (more so for fewer bars) and taller. You can also adjust the size of the plot area to minimize the white margins around the plotted data.

Columns on Two Panels - Step 9

You probably want to label your vertical axes. Using the plus icon (Excel 2013) or the Chart Tools > Layout tab > Axis Titles control (Excel 2007/2010), add axis titles to the two vertical axes.

Columns on Two Panels - Step 10

Excel centers these axis titles along the sides of the chart. You can drag them so they are centered on their respective panels. You’ll probably also have to readjust the plot area.

Columns on Two Panels - Step 11

Adding Data

Making the panel chart seemed like a lot of steps, but it’s really not more steps than making any chart with primary and secondary axes, and it’s fewer steps than the gymnastics needed to put columns on two axes with appropriate spacing.

Adding data is also easier for the panel chart. If I add a series to the column chart with two axes, it throws off the spacing provided by the dummy series, and I’ll need to adjust the number and arrangement of blank series in the chart.

Columns on Two Axes with Added Series

But I’m not going to bother. Instead I’ll add the data to my panel chart.

So copy the new data, select the chart, click on the Paste button of the Home tab (the Copy button in 2007!), and add the data as a new series.

The data is added to the secondary axis.

Columns on Two Panels with Added Series

If the data belongs on the primary axis, simply format the new series and assign it to the primary axis.

Columns on Two Panels with Added Series

If the new series belongs on the secondary axis, you’re already done.

Columns on Two Panels with Added Series

If you add multiple series, and they belong on both axes, well, just move the appropriate ones to the primary axis.

Columns on Two Panels with Added Series

If you need primary and secondary axes, it’s easy to use panel charts to produce charts that avoid the confusion that overlapping axis scales can cause.

 

Peltier Tech Charts for Excel

Add Percentages on the Secondary Axis

I received an email from a user of my Waterfall Chart Utility, who wanted to add a secondary axis showing percentages corresponding to the values on the primary axis. This is really just a matter of applying straightforward algebra to compute the secondary axis scale parameters from the primary axis scale parameters and the two values one wants to line up. I described a very similar problem in Align X Axis to Y=0 on Two Y Axes, which solved the special case where zero on the primary axis was to coincide with zero on the secondary axis.

The user’s data looks like this (I’ve changed the labels and values).

Waterfall Chart Data

The waterfall chart looks like this, after minor modifications.

Waterfall Chart

The user wanted the secondary axis to scale from 0% at $0, to 100% at the top of the revenue bar, or $840,000. The simplest way to do this is to set up a table like the one below, which contains the primary axis maximum and minimum, the primary and secondary axis values which have to line up, and spaces for the secondary axis maximum and minimum.

Secondary Percentage Axis - Data

I changed the view to show formulas in the cells, by holding Ctrl and clicking the button with the tilde (~). The formulas in C12 and C14 are really straightforward once you’ve set up a table like this.

Secondary Percentage Axis - Formulas

I’ve switched back to normal view, by again holding Ctrl and clicking the ~ button. The calculated secondary axis scale parameters are now shown.

Secondary Percentage Axis - Calculated Scale

In order to have a secondary axis in an Excel chart, you need a series to put onto the secondary axis, in addition to any series that belong on the primary axis. The waterfall chart has a bunch of series on the primary axis, but we’ll add a new series for the secondary axis. Right click the chart, choose Source Data, and click on the Series tab. Click the Add button, enter a name (“Dummy”), and for values, enter ={0}, which is an array consisting of the single element zero. This results in a series which should remain hidden.

Add Dummy Series Dialog

This new series is added to the primary axis. Select the new series, by using the Chart Menu’s Select Chart element dropdown, or simply by selecting any chart series (actually, any chart element) and cycling through all chart elements until the Dummy series is selected.

Select series with chart toolbar

Once the Dummy series is selected, press Ctrl+1 (numeral one) to open the Format Series dialog. On the Axis tab, click Secondary. The chart now has a secondary axis.

Waterfall Chart in progress

Format the secondary axis: right click on the axis and choose Format Axis. On the Scale tab, enter the numbers calculated above. If necessary, adjust the number format of the axis labels on the Number tab.

Format Axis Scale Dialog

You should also lock in the primary axis scale parameters, because any change Excel makes under Auto mode will break the calculations made above. Right click the axis, choose Format Axis, and on the scale tab, uncheck the Auto boxes in front of Minimum, Maximum, and Major Unit.

Format Axis Scale Dialog

The result is a chart with percentages that line up as desired.

Waterfall Chart with Percentage Secondary Axis

An alternative might be to scale the secondary axis from 0% to 100%, and the primary axis from $0 to $840,000.

Alternative Waterfall Chart with Percentage Secondary Axis

 

 

Peltier Tech Charts for Excel

Overlapped Bar Chart – Thinner Bars in Front

In Overlapped Bar Chart – Longer Bars in Back I showed a fairly easy way to make a chart Robert Kosara developed to examine popular vote and Electoral College vote in US presidential elections. Stag Lee commented that Robert’s chart type was not intuitive, and suggested a slight modification, as shown here:

Column Chart Thinner in Front

This example uses the same data from the previous post.

Sample Data

Start with a simple clustered column chart.

Column Chart Thinner in Front

Select one of the series (in this case “One”), and move it to the secondary axis.

Column Chart Thinner in Front

Remove the secondary Y axis, the axis along the top edge of the chart.

Column Chart Thinner in Front

Change the gap width of the series in front to 200, and of the series behind to 50.

Column Chart Thinner in Front

This is also a relatively clear way to show the two series, but it’s not as striking when the thin series represents a larger value than the wide series. Robert’s chart, reproduced below, more clearly shows the series with the smaller value, because the smaller series bar is visible all the way to the left axis of the chart.

Column Chart Larger in Back

 

Peltier Tech Charts for Excel

Secondary Axes that Work – Proportional Scales

In my last post, Secondary Axes in Charts, I described an essay by Stephen Few in which he concluded that secondary axes provide no benefit to good infographics. I have come to the same conclusion myself: secondary axes are more likely to confuse and obscure the data, than to clarify relationships in the data.

In the first comment, Lee countered with a description of charts he frequently uses, which have dual axes. His axes are not independent of each other, however, but instead are tied together, showing different measures of cost of fuel per energy content. The units of measure are different for oil (gallons) than for coal (pounds), for example, and industry has developed standard measures of each. With a few constants, however, Lee is able to relate primary and secondary scales on a chart.

Lee’s charts do not have truly different scales. He uses a single scale, with two sets of numbers to describe them. That’s like reporting your weight in lb and kg, or your car’s speed in mph or km/hr. The numbers are different, but the scales are proportional, not independent. I thought, what a good opportunity to show how to construct a chart with this kind of proportional scales, which are the only kind of intentionally visible secondary axes you should employ in your charts.

Standard Secondary Axis Construction

In this example, I’ll plot fictional daily temperatures in Fahrenheit and Celsius. The data is shown in this simple table. The Fahrenheit values were derived from the made-up Celsius values using the well known F = 9/5 C + 32 relationship.

A B C
1 Date Temp (°C) Temp (°F)
2 3/13/2008 5 41
3 3/14/2008 -1 30.2
4 3/15/2008 -2 28.4
5 3/16/2008 3 37.4
6 3/17/2008 4 39.2
7 3/18/2008 13 55.4
8 3/19/2008 10 50
9 3/20/2008 12 53.6
10 3/21/2008 4 39.2
11 3/22/2008 5 41
12 3/23/2008 7 44.6
13 3/24/2008 8 46.4
14 3/25/2008 5 41

The first step in creating a chart with primary and secondary value axes is to plot the data. In Excel you need to have at least two series in a chart if you want secondary axes, because each axis group requires at least one series. The data will be plotted with the Dates as the X variable and the Temperatures as the Y variable(s). At first the chart has only a primary Y axis.

So far so good. The curves actually relate to the same temperatures, but they are measured on different scales. Let’s move the Fahrenheit data to the secondary axis. Classic Excel (97 and probably earlier to 2003): double click the series, click on the Axis tab, choose the Secondary Axis option. New Excel (2007, and 2010 if they don’t change it again): select the series, press Ctrl+1 (numeral one) to open the formatting dialog, and on the main screen, choose the Secondary Axis option.

The scales still do not coincide. Excel’s built-in axis scaling algorithm has assigned a Y axis minimum of zero to the secondary axis, even though the actual minimum is almost 30 on a scale of 60. No matter: like everything else, if you want something done right, you have to do it yourself. I decided to clean up the Celsius axis by locking in the minimum and maximum at -5 and 15, with a step (major unit) of 5. With the 5/9 relationship between the two scales, the works out to a minimum and maximum of 23 and 59 on the Fahrenheit scale, with a step of 9.

The scales coincide, as evidenced by the alignment of the Celsius and Fahrenheit data. I’ve hidden the Fahrenheit lines os you can see that the Celsius lines connect the Fahrenheit points perfectly. The only problem is that the Fahrenheit scale is a bit unfamiliar; people would probably prefer a scale that started and incremented in multiples of five. in addition, having to plot two sets of data is a bit redundant, because the points coincide exactly.

Custom Axis Scale

Let’s rebuild the chart using just the Celsius data points, and we’ll fake the Fahrenheit scale.

We need a range of values for our fake Fahrenheit axis. From our first experiment we know that -5 to 15 Celsius encompasses 25 to 55 Fahrenheit, os this is the table we will use. The Celsuis values this time were calculated from the Fahrenheit labels. We will add an XY series to the cahrt, using the dates in column E as the X values, the Celsius values in column F as the Y values, and the Fahrenheit values in column G as the “axis” labels.

E F G
1 Temp (°C) Temp (°F)
2 3/27/2008 -3.88889 25
3 3/27/2008 -1.11111 30
4 3/27/2008 1.666667 35
5 3/27/2008 4.444444 40
6 3/27/2008 7.222222 45
7 3/27/2008 10 50
8 3/27/2008 12.77778 55

To add the points, copy the range E1:F8, select the chart, and use Paste Special (Classic Excel: Edit menu, New Excel: Home tab of the ribbon, far left) to add the data as a new series, X values in the first column, series name in the first row. As desired, the points line up along the right edge of the chart.

Next we need labels on the data points. There are two very good chart labeling utilities, both free, that integrate nicely with Classic Excel. I haven’t tested either in New Excel: I suspect they work but may not interface as nicely with the ribbon. The utilities are Rob Bovey’s Chart Labeler from AppsPro.com, and John Walkenbach’s Chart Tools from J-Walk.com. Download, install, and use one of these utilities to add chart labels from the range G2:G8 to the data points, in the position to the right of the points.

The last step is to make the XY series look like an axis. First, format the series so it has no markers and no lines. Add positive X error bars to this series, with a nominal value (in this case, 0.15 seems good). Format the error bars so there sre no end caps on the bars, and the line color matches the gray of the chart frame.

Note: I have just tried this in Excel 2007, and it doesn’t work. In Classic Excel, there is a small border around the visible plot area of a line or XY chart that has markers in any of its series, which allows a marker to be located on the edge of the chart and not be truncated by the plot area’s border. This allows the error bars to extend away from the chart. New Excel does not have this little border region around the plot area, or maybe the error bars do not appear within the border. I’ve had other issues with Excel 2007 error bars, and maybe one day I’ll rant post about them.

Here is the finished chart in Classic Excel. In New Excel you could change all your ticks in the chart’s axes to Cross instead of Outside, then use the plus-sign markers for the dummy scale axis. Or you could use the small dash marker, but that produces a tick which is too thick.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0