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).

The waterfall chart looks like this, after minor modifications.

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.

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.

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

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.

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.

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.

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.

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.

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

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

Possibly Related Posts:

• Secondary Axes that Work - Proportional Scales
• US Employment Slump Chart - How To
• Secondary Axes in Charts
• Stacked Area Chart Challenge
• Simple Waterfall Chart with Up-Down Bars
• Clustered-Stacked-Column Combo Chart With Lines
• Clustered-Stacked Column Charts with Vertical Separators
• Clustered-Stacked Bar Charts