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

Comments

  1. Jon,

    Assuming an entirely different data set, another issue might be . . . how do you account for the percent changes when there are positive and negative values? Or, for example, favorable and unfavorable variances to plan or greater than or less than changes to prior period? In my experience rarely are the components of change all positive or all negative. Perhaps rather than showing the percentages on an axis perhap each percent change could be individually tied to the differences between two bars.

    As an example, an increase from 40 to 50 is 25%, but a decrease from 50 to 40 is 20%.

    John Mansfield

  2. Hi John –

    That’s a good point. In many cases this technique would be much more difficult to implement. In this specific case, the person wanted to see the changes with respect to the initial value, the gross revenues. Any case that referenced a single value, say, the initial value or the final value, would work simply like this.

    If you wanted to see the percentage change corresponding to each value change, I think you would have to calculate the percentages in the worksheet and apply them to each bar in the chart as a data label.

  3. Hi Jon,
    in http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html you show how to link the axis scales to cell values. This works fine for primary axis?
    How can I use this code for secondary axis? – I’ a VBA dummy ;-)
    Thanks for your reply

  4. Hi Michael –

    When you specify an axis, you use a syntax like this:

    ActiveChart.Axes(xlValue, xlPrimary)

    or

    ActiveSheet.ChartObjects(“Chart 1″).Chart.Axes(xlCategory)

    There are two arguments to the Axes() property. The first is xlCategory for the X axis and xlValue for the Y axis. The second is xlPrimary or xlSecondary, and the meaning is obvious. The second argument is optional, and assumed to be xlPrimary if missing. Simply insert the xlSecondary into the slot for thee second argument:

    ActiveSheet.ChartObjects(“Chart 1″).Chart.Axes(xlCategory, xlSecondary)

  5. I am trying to make the advanced Gantt chart —
    http://peltiertech.com/Excel/pix5/AdvGantt08ax.gif
    responsive to spin buttons to change the MinimumScale and MaximumScale values for the time axes. The chart has its “Primary X Time Scale Axis” along the bottom and “Secondary Y Value Axis” along the top edge of the chart.

    The following VBA code in a worksheet module has no effect:
    Case “$C$33″
    ActiveSheet.ChartObjects(“Chart 1″).Chart.Axes(xlCategory).MinimumScale = _
    Target.Value + 39810
    ActiveSheet.ChartObjects(“Chart 1″).Chart.Axes(xlValue, xlSecondary). _
    MinimumScale = Target.Value + 39810

    The spin button changes cell C33 (and other dependent cell values), but the chart does not change. I, too, am a VBA dummy…

  6. This works well, but not if the data you are viewing changes, this means you have to go back into the axis scales and type number again.

    The trick around this is to add another data series with values that match the right hand edge of the chart, then use data labels to show ‘100%’, ‘80%’ and so on. The actual value of each data point is the same as the maximum value on the primary axis

  7. Al –

    That is a great trick, and I use it in many cases. But here it is not guaranteed to work: if the secondary tick spacing (major unit) isn’t the same proportion of the maximum as for the primary axis, you may not get the axes to match automatically.

  8. I have been trying for 3 hours and I couldnt figure this out. Your article is the best!

Subscribe without commenting

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.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites