Change Chart Type and Add a Secondary Axis

Change Chart Type and Add a Secondary Axis

Practice changing a chart from one type to another without having to start from scratch and how to add a secondary axis to represent a second data series. 

When to use

Sometimes we might create a chart, a bar chart for example, and then decide we want to switch it to a combo chart at a later date. Knowing how to change the chart type and add a secondary axis is an invaluable skill to improve efficiency when working with Excel charts. 

Instructions

Let's start by creating a basic 2D column chart that shows the date and sales for a company. 

Create a 2D Column Chart

• Select the 'Date' and 'Sales' columns. 

• From the Insert tab, in the Charts group, click the drop-down arrow next to column chart. 
• Choose 2D Column Chart from the gallery. 

Add Axis Titles

• Click the + (plus) icon next to the chart.
• Check the box next to Axis Titles. 
• Double-click the horizontal axis text box and type 'Dates'. 
• Double-click the vertical axis text box and type 'Sales'. 

Add a Chart Title

• Double-click the Chart Title text box. 
• Type 'Sales by Date'. 

Add Another Series

In addition to showing the sales by date, we also need to show the conversion rate on the chart. To do this, we need to add another series. 

• Click on the chart. 
• From the Chart Design tab, in the Data group, click Select Data. 
• In the Legend Entries area, click Add. 
• In the Series name field, click the column header 'Conversion Rate' in the table. 
• In the Series values field, select the column that contains the conversion rates. 
• Click OK. 

The new series has been added but it's really hard to see as the conversion rates are percentages whereas the sales values are whole numbers. 

This type of chart would be more suited to a combo chart with a secondary axis where the sales values are displayed as columns and the conversion rates displayed as a line. 

Change Chart Type

Let's change the chart type to a combo chart. 

• Click on the chart. 
• From the Chart Design tab, in the Type group, click Change Chart Type. 
• From the Recommended Charts tab, click Combo from the list. 
• Set the sales series to show as a Clustered Column and the conversion rate series to show as a Line. 
• Check the Secondary Axis box next to the conversion rate series.
• Click OK. 

A new axis will be added on the right-hand side that shows percentages and the conversion rate values will display as a line.

Add a Legend

• Click the + (plus) next to the chart. 
• Click the arrow next to Legend and choose Top. 

Hints & tips

• Remember you can move text boxes on charts by dragging and dropping or using the arrow keys to nudge.