In this article, I will show you how to add a secondary axis in Excel charts. Take a look at the following image. Above a regular chart, and below you’re seeing a chart with the secondary axis.
Some Excel charts, almost or completely, are unable to show insights from values. Especially, when you’re using two data series with big differences like this data.
|Month||No. of Sales||Average Sales Price|
Just compare the No. of Sales column and Average Sales Price column.
Min and Max values of No. of Sales column are 112 and 150.
Where Min and Max values of the Average Sales Price column are 106722 and 482498.
To find these Min and Max values, we have used Excel’s MIN and MAX functions. For example, MIN(no_of_sales) = 112. Here, no_of_sales = B3:B14.
So, there is a big difference between these two data series.
I will show you two ways to add a secondary axis to Excel charts.
1) Add secondary axis to Excel charts (the direct way)
You can add the secondary axis to an Excel chart from the beginning when you’re making the chart.
Here is the step-by-step procedure.
1) In this way, at first, select all the data, or select a cell in the data. You see, we have selected a cell within the data that we shall use to make the chart.
2) Now go to Insert tab => click on the Recommended Charts command in the Charts window or click on the little arrow icon on the bottom right corner of the window.
3) This will open the Insert Chart dialog box. In the Insert Chart dialog box, choose the All Charts tab.
Then choose the Combo option from the left menu. On the right side, you will find the data Series Names, 2 drop-down menus under the Chart Type heading, and 2 checkboxes under the Secondary Axis title.
4) I choose Line with Markers chart for the Average Sales Price data series and tick the checkbox (on the right) for showing this data in the secondary axis. You are also seeing the preview of the chart in the middle of the dialog box. If you like the preview, click on the OK button.
5) You get a chart like this.
I will show later (after showing one more technique to add a secondary axis) how you can change the formatting of a chart to make it a professional-looking one.
2) Adding a secondary axis to an existing Excel chart
This is another scenario. You were not aware of the data type and already have built a chart with the data.
Creating the chart
Say, you have created an Excel in one of the following two ways:
- You have selected all the data or a cell in the data => then pressing ALT + F1 key in your keyboard
- Or You have selected all the data or a cell in the data => Insert tab => and then selected a chart from the Charts window to make your preferred chart.
- Or you have created a chart using the Recommended Charts command button.
This is the chart you will create if you have used the ALT + F1 keyboard shortcut or have chosen the 2-D Clustered Column chart or chosen the first recommended chart from the Recommended Charts option.
Adding a secondary axis to this chart
Now you have built a chart and you find that this chart is not showing any insight for you. The reason is the values of No. of Sales data series are very small when compared with the values of the Average Sales Price data series.
Here are the steps of adding a secondary axis to this chart.
1) Select the data series that you want to add to the secondary axis. I choose the Average Sales Price data series as I want to show this data series on the secondary axis. To select a data series, just click on any data point of the data series. The whole data series will be selected.
Note: If you click again, then only that specific data point will be selected.
2) Now right click on the Data Series and choose the Format Data Series option from the menu.
3) Format Data Series task pane appears on the right side of the worksheet.
And we choose the Secondary Axis radio button for this data series.
The keyboard shortcut to open this task pane is: CTRL + 1
4) Though we have placed our data series to a new axis, still our data is not showing any insight.
Let’s change the chart type. Go to Design tab (shows only when the chart is selected) => Type window => and click on the Change Chart Type command
5) Change Chart Type dialog box appears. This dialog box is actually our old Insert Chart dialog box. The Combo option is already selected. I just change the Chart Type from Clustered Column to Line with Markers.
And we see our preferred chart is showing in the preview.
To get the chart created, click on the OK button.
These are my 2 or 3 ways of adding a secondary axis to an Excel chart.
Bonus: Formatting the Excel Chart
This is the bonus section. Let’s see some essential formatting techniques that you can do with your charts. Also, a bit of dynamism with the chart.
What formatting lacks you find with the chart? I find the following:
- There are no axis titles
- of Sales data series axis has started with value 0 while its minimum value is 112. So, we can start from 70 or 80 to show more visual insight. The same situation is also with the Average Sales Price data series axis. The minimum value is 106722 but the axis has started from value 0.
- Average Sales Price data series axis is not showing the values in units like hundreds, thousands, 10000, 100000, millions, etc.
- Removing the grid lines
- We can change the color. Also, we can change the type of marker. (this formatting is optional)
a) Adding Axis Titles
We can add the axis titles using the big plus sign (+) that appears when a chart is selected. In this way, both the axis titles will be created.
To add individual axis titles, go to Design tab (only available when a chart is selected) => Chart Layouts window => click on the Add Chart Element dropdown => hover your mouse over Axis Titles -> 4 options appear => Choose your preferred option
Now select an axis title (remember when selected, it must not be in the Edit mode), press equal sign in the keyboard, and then select the cell B2 where the title of the data series is available.
If you press Enter now on your keyboard, the axis title will show the value of the cell $B$2.
In the same way, you can also format the Axis Title for the secondary axis.
b) Changing minimum values and showing values in units
Let’s change the minimum value of Average Sales Price axis and show its data in units.
Right-click on the data series and choose the Format Axis option from the menu.
Format Axis task pane appears on the right side of the worksheet. Where we change things in two places:
- We change the minimum value to 80,000
- And we change the display units to 100000
Changes reflect on the chart immediately.
c) Remove the grid lines
It is also optional. You can remove the grid lines from the chart or not. It depends on chart data. But it is easy to remove the grid line.
Big plus sign + (when the chart is selected, this plus sign appears) => check or uncheck the checkbox on the left side of the option Gridlines.
d) Changing the marker type and size
Optional change. But let me show you the procedure.
Right click on the data series => Choose the Format Data Series option from the menu => Format Data Series task pane appears => Select Fill & Line tab => Choose Marker => Marker Options => Select Built-in => Using Type and Size drop down, choose preferred Marker and change the Size of the marker.
So, these are the formatting techniques you can use to make your chart a little bit professional.
Download Working File
That’s all from me on how to add a secondary axis in the Excel chart. I also some formatting techniques as a bonus. Hope you like my effort on this topic. Feel free to comment if you come up with feedback.