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 Chart Utility

Comments

  1. I really like this cool stuff, hope it can be used someday to impress may big boss. ha…

  2. Thanks for the cool guide. Love the little hacks.

    Although I can’t really think of a use-case right now where I’d use this over just creating 2 different charts.

  3. Tom –
    Much of the time, two separate charts is just as good. Especially if it’s just you examining the data.
    For presentations, having all the data in one chart links it together, and the data shares a common category axis, so you don’t need to continually tweak alignment of two charts.

  4. I just used this to doctor a daily report and it looks MUCH better. I have four values to display that should logically be separated into two pairs, yet the pairs are related to one another and are better shown on one chart. The value of one is also much higher than the other three. Just excellent, thank you.

  5. Hi Jon,

    Thanks for this great post.

    I am still not able to figure out how to make the second chart:
    https://dl.dropboxusercontent.com/u/15685326/Total%20sales%20vs%20operating%20margin.png

    Is it possible or do I need to use the linechart on the first axis?

    Thanks

  6. Kalle –
    The two series must go on different axes, since they display much different data ranges.
    When I get a lull, I will post another half dozen or more tutorials, including your example.

  7. Thanks for sharing panel chart technique
    Number formatting axes is new learning for me
    Only thing I can’t get gridlines accurately as you in secondary chart.

  8. Prashant –
    If I understand your problem, you need to adjust the major tick spacing and the min and max of the two axes, so that there are the same number of gridlines in each half of the chart.

  9. Hi Jon,
    I’m having trouble creating a custom number format for my primary axis that runs 0 to 30,000 in increments of 5,000. I tried using [<=10]0;;;@ and modifying it to [<=30,000]0;;;@ but when I try that the axis disappears leaving only a zero at the bottom. Do you know what I'm doing wrong? I would greatly appreciate your help with this.

    Thanks for this great post,
    Eric S

  10. Eric –
    The number format looks okay. What are the axis scale parameters?

  11. Hi Jon –

    I am looking to create a clustered and floating stacked-bar chart with a primary and secondary axis as well. Do you have any guidance on that?

    Thank you.

  12. Clarice –
    A floating bar chart is a stacked bar chart with a transparent bar in the lowest position, on which the other bars “float”.
    A clustered-stacked bar chart has a particular arrangement of bars across the different categories.
    To get a floating clustered-stacked chart, you just need to insert a transparent series that the clustered-stacked bars will float on.

  13. Just the solution I needed! Thanks for the guide. I’m currently preparing a report for forest fire survey with lots of data, and these tips have helped greatly.

  14. Everytime your site comes up in a google search on how to fix something in Excel, your advice always seems to be to make a panel chart. I think I’ll stick with a dummy column for this problem.

  15. Hi John, great post as always. how do you take a 2 series vertical column chart and make it horizontal and use a secondary axis. But not have the series overlapped. thanks

Subscribe without commenting

Trackbacks

  1. […] Column Chart with Primary and Secondary Axes | Peltier Tech Blog […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.