Excel Chart Axis Switch

When working with small multiple charts or panel charts as they’re also known, it’s handy to be able to switch the chart axes between the same axis for each chart and their own axis. Using the same axis enables you to compare the data between the charts because they all use the same scale. Whereas using their own axis is useful when there are large variances from one chart to the next which can result in charts with lower values being difficult to read.

Watch the Excel Chart Axis Switch Video

Download Workbook

Excel Chart Axis Switch Set Up

Note: watch the video for detailed instructions.

Firstly, each chart has its own dataset:

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.