Chart Axis Switch - Cool Trick for Comparing Multiple Charts

Radio buttons are used for the switch, which are?form controls?that have been around forever:

Note: If you don’t have the developer tab where Form Controls are stored,?click here?to learn how to enable it.

Unfortunately, you can’t format radio buttons unless you use the ActiveX version which require VBA and I like to avoid VBA whenever possible. However, I’ve applied some?conditional formatting?to the cells behind the form controls and used?shapes?to make them look more like buttons. You can see it best when the cells around the radio buttons are selected:

The switch buttons are linked to cell W18 in the worksheet. Excel detects which button is selected (button 1 or button 2) and enters the number in the cell. I can then reference this cell in formulas to choose which axis to display.

The axis to display is handled by a?ghost series?which is an additional hidden series in each chart that plots the minimum and maximum overall values.

When the ‘Same Axis’ button is selected the values in the Ghost Series simply return the minimum and maximum values overall using IF formulas in cells V18 and V19:

Cell V18 - Minimum: =IF(W18=2,0,MIN(G18:G22,K18:K22,O18:O22,S18:S22))

Cell V19 - Maximum: =IF(W18=2,0,MAX(G18:G22,K18:K22,O18:O22,S18:S22))

In English the formulas read, if the switch button selected is number 2 (own axis), then return zero, otherwise find the MIN or MAX values of all charts.

When plotting these values, the vertical axis automatically adjusts to accommodate the largest value in the chart.

Note: In this example I don’t need the minimum value because there are no negative values in the data and therefore all vertical axes should start at zero. However, I’ve kept it in for datasets that contain negative values, or if you’re using line charts which can start above zero.

Have a great day.