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.