There are times when you need to plot data on both a primary axis and a secondary axis. Creating charts for correlation analysis is one example. Although Excel allows you to easily create these types of charts, unfortunately it does not offer an option to create proportionate axes. To do so you must perform the calculations yourself and then enter the scale values individually.

For selected primary and secondary axis presentations, it is important to make sure that each axis is proportionate to the other. If the axes are not proportionate, you run the risk of misrepresenting the data. The proportionate axes insures that the data is presented in the same plane.

Most of the time the calculations to create proportionate axes are fairly easy to do. This is especially true when the minimum and maximum values for both the primary and secondary axis are positive. However, the calculations get complex if negative values fall into the mix. Fortunately, I've seemed to have found a simple calculation that seems to work for all values positive and negative.

As an example and given the data set below, the task is to create a chart with a primary and secondary axis. Both axes must be proportional to each other and must have tick marks that line up with each other.

Before showing how to create the chart, below is the end result:

How do you know that the axes are proportional to each other? To test the results, divide the absolute value of the maximum axis value by the absolute value of the minimum axis value. Using the example above, the primary axis ratio is calculated as:

abs(-122.99268) / abs(59.47431) = 2.067997

The secondary axis ratio is calculated as:

abs(489,195) / abs(-236,555) = 2.067997

Since both ratios equal each other, the axes are proportionate.

Calculating the Proportionate Axes Values

To calculate the values for the proportional axes, three sets calculations are performed. The first two calculations provide data for the final calculation. The final calculation is then used as the source for the axes values. Based on the data above, the worksheet with all three calculations looks like this:

Axes Calculation 1

For Axes Calculation 1, one possible option for the primary axes is the maximum of the range C3:C12. Another possible option for the primary axes the minimum of the range C3:C12. The midpoint is the middle value between the maximum and the minimum as calculated with this formula:

=MAX($C$3:$C$12)-((MAX($C$3:$C$12)-MIN($C$3:$C$12))/2)

A possible option the secondary axes (cell D17) is calculated with this formula:

=C16/C17*D17

Another possible option for the secondary axes (cell D19) is calculated with this formula:

=C18/C17*D17

The midpoint for the secondary axes is calculated the same as shown above.

Axes Calculation 2

For Axes Calculation 2, a possible option for the secondary axes is the maximum of the range D3:D12. Another possible option for the secondary axes the minimum of the range D3:D12. The midpoint is the middle value between the maximum and the minimum as calculated with this formula:

=MAX($D$3:$D$12)-((MAX($D$3:$D$12)-MIN($D$3:$D$12))/2)

A possible option for the primary axes (cell E17) is calculated with this formula:

=F16/F17*E17

Another possible option for the primary axes (cell E19) is calculated with this formula:

=F18/F17*E17

The midpoint for the primary axes is calculated the same as shown above.

Final Axes Calculation

Once the first two calculations are complete, the maximum and minimum values for the proportional axes can be obtained. To get the maximum value for the primary axis, take the maximum of calculations 1 and 2. The formula in cell D24 is:

=MAX(C17,E17,C19,E19)

To get the minimum value for the primary axis, take the minimum of calculations 1 and 2. The formula in cell D25 is:

=MIN(C17,E17,C19,E19)

To get the maximum value for the secondary axis, take the maximum of calculations 1 and 2. The formula in cell E24 is:

=MAX(D17,F17,D19,F19)

To get the minimum value for the secondary axis, take the minimum of calculations 1 and 2. The formula in cell E25 is:

=MIN(D17,F17,D19,F19)

Enter the values obtained above as the maximum and minimum primary and secondary axis values. To align the tick marks, divide the difference between the maximum and minimum values for each axis by the same number. This example divides the differences by six to get the major units. For example, to get the primary major unit values, subtract the minimum value (-122.99268) from the maximum value (59.47431). The difference is 182.46699. Divide that difference by six and enter the result as the major unit. Do the same for the secondary axis and your axes are complete.

Please note that these calculations do not take into account the possibility that the midpoint of one, the other, or both axes falls at zero. This issue will be discussed at a later date.

You do not define proportional or proportionate axes. To me, the most important consideration of proportionality would be that the ratios of the min to the max be the same on each, and that the zeros would correspond (a situation which you have put off to a later date).

Your two defining formulas should show min/max, and should not use ABS:

-122.99268 / 9.47431 = -2.067997
-236,555 / 489,195 = 0.483559 *

*In your example, you calculated min/max of the primary axis and max/min of the secondary axis.

Unless you reverse the order of the secondary axis, they do not show the same ratio.

If your only concern is that the tick marks line up, don’t concern yourself with the extraneous definition of “proportional axes”, and simply use major unit scale parameters that make it work.

However, to make the scales truly proportionate, you need to line up their zeros, and extend both to the max and min that encompass both series.

What we need are the min, max, and range of each series.
Series 1: Max = 53, Min = -91, Range = 144
Series 2: Max = 489195, Min = -236555, Range = 725750

Now, what are the max and min expressed as fraction of the range:
Series 1: Max/Range = 0.36806, Min/Range = -0.63194
Series 2: Max/Range = 0.67405, Min/Range = -0.32595

What are the extremes, that is:
MAX(Max/Range) = 0.67405
MIN(Min/Range) = -0.63194

Now multiply the ranges by the extremes:
Series 1: New Max = 144 * 0.67405 = 97.06384
Series 1: New Min = 144 * -0.63194 = -91 (unchanged)
Series 2: New Max = 725750 * 0.67405 = 489195 (unchanged)
Series 2: New Min = 725750 * -0.63194 = -458633.7

These are the new primary and secondary Y axis min and max scale parameters. To get the major unit (tick spacing) so that there is a tick at zero, divide the (absolute value of the) minimum of each axis by an integer (Excel starts ticks at the minimum, so zero will be an integer number of ticks above the minimum); I selected 4.

Series 1: Major Unit = 91/4 = 22.75
Series 2: Major Unit = 458633.7/4 = 114658.4

Now the axes are proportionate, the zeros coincide, and the range is from the lowest proportional minimum of the series to the highest proportional maximum of the series.

I’ve reposted John’s workbook with my analysis here: PropAxesCalcsJP.zip.

I think you’ve stated best by saying “the most important consideration of proportionality would be that the ratios of the min to the max be the same on each”.  This is the real point of the post.  In a nutshell, with a single set of calculations I was hoping to illustrate how to calculate the scales no matter what the source data was i.e. all positive numbers, all negative numbers, or any combination of positive and negative numbers.

The use of the ABS function in the ratio is to basically say ->  disregarding the signs, proving proportionality should be as easy as taking the largest value on the scale and dividing it by the smallest value. 

Regarding “in your example, you calculated min/max of the primary axis and max/min of the secondary axis.  Unless you reverse the order of the secondary axis, they do not show the same ratio.”  That is correct, and why I needed to go to the third calculation to get the final results.  On second thought, the use of the descriptions “max” and “min” for the first and second calculations is incorrect because those descriptions can vary depending on the sign.  As a result, I’ve changed the example to say “Option 1” and “Option 2” as opposed to “max” and “min”.

Finally, as you’ve noted lining up the zeros if you have data that crosses zero is the final step.  Thanks for sharing the file with the revised calculations.  I was leaving this for a later post - would you care if I incorporated your example into that future post?

Thanks Jon!

Hi John -

Feel free to use or modify the example workbook and technique I posted; just include a reference to me and my web site.

I think you’ll find that my calculations work no matter what the breakdown of numbers (positive, negative, mixed). I was not sure what the basis for your approach was, because I couldn’t figure out what “proportional” referred to. So I spent a few minutes thinking of an alternative approach, and it had the added benefit of aligning the zeros. This was unplanned at the time, but a natural consequence of the mathematics.