How to add or remove a secondary axis in an Excel chart

0
316

[ad_1]

You can add a secondary axis in Excel by making your chart a combo chart, enabling the “Secondary Axis” option for a series, and plotting the series in a different style than the primary axis. If you decide to delete the second axis later, just select it and press Delete.

If you have a chart where you are plotting different types of data or the values ​​vary, consider adding a second axis for easier visualization. We’ll show you how to seamlessly create a two-axis chart in Excel.

When to use a secondary axis

You can have a combination of data series with currencies, percentages, decimals, or whole numbers. Or perhaps the values ​​you’re displaying vary by greater amounts than the graph can adequately display. In these cases, adding a second vertical axis to the chart can represent the data more effectively.

For example, we have our data set that includes expenses and income for our locations. We also have the number of employees for each location to show how those numbers affect expenses and revenue. This graph has two problems.

Data chart and chart without secondary axis

First, the values ​​for employees are well below the smaller amounts for expenses and income. Second, we have a combination of currency and number formats. The data is not only nearly impossible to see, but it is meaningless without the numerical values.

graph without secondary axis

As you can see, plotting this data without a secondary axis does not provide a satisfactory or useful picture.

Add a secondary axis in Excel

If you haven’t created your chart yet, you can add the secondary axis immediately by creating a combo chart from scratch. But if you already have your chart and you just want to add the second axis to it, it will turn your chart, in a sense, into a combo chart.

RELATED: How to create a combo chart in Excel

To begin the conversion to a two-axis chart, select one of the measures for the axis you want to make a secondary axis. Then right-click and choose “Change Series Chart Type.”

Change the serial chart type in the context menu

You will see the Change Chart Type window open with Combo selected on the left. On the right, you have your current chart type with the data below it.

Current chart with combo chart settings

Use the Chart Type dropdown box next to the data series you want to change. Usually it works fine to use a line or a line with markers for the second axis. However, you can choose another option like area or scatter with lines for a unique look if you want.

Chart types for a series

Then check the box to the right of the same series for “Secondary Axis”.

You will then see a preview of the updated chart. As you can see, the second axis stands on its own and also includes the values ​​along the right hand side. Both make the data much easier to understand.

Secondary axis added using a line chart

Note: Combination charts typically work with the column chart type. So you may need to adjust this if you are using a bubble chart or other type of chart.

When you’re done, click “OK” to apply the change to the chart on your sheet. You can then add axis titles or set data labels for clarity.

Updated chart with a secondary axis in Excel

RELATED: 6 Tips for Making Microsoft Excel Charts Stand Out

Delete a secondary axis in Excel

If you decide later that you no longer want a secondary axis on your chart, how you remove it depends on how you want to display the remaining data.

Delete the y-axis data from the chart

You can remove the y-axis corresponding data from your two-axis chart quickly and easily. Select the secondary axis on the chart and press the Delete key.

You will then see that both are removed from the chart.

Before and after deleting the second axis of a chart

Convert axis to a different type

You can keep the data in the chart and change its type as in the previous example to make it a combo chart.

Right-click the data series and choose “Change Series Chart Type.”

Change the serial chart type in the context menu

Then choose the type from the dropdown menu. Be sure to uncheck the box for Secondary Axis. Click “OK” to apply the change.

The secondary axis type of the chart series has been changed.

Convert the secondary axis to a primary axis

Another option is to convert the secondary axis to a primary axis. Depending on the type of chart you use for the secondary axis, this might be the ideal option for you.

Right-click on the data series and choose “Format Data Series”.

Data series format in context menu

When the sidebar opens, select the Main Axis option in the Series Options section. Note that you must be directed to this exact location from the context menu.

Primary Axis selected in the Format Data Series sidebar

Again, depending on the chart type, you may need to change the style of the axis after converting. You can do this by right-clicking, selecting “Change Series Chart Type” and choosing the style as described above.

By adding a secondary axis in Excel, you can improve the readability of your chart to make it a more useful picture. For more information, see How to choose a chart that fits your data in Excel.

[ad_2]