How to Add a Secondary Axis to an Excel Chart

How to Add a Secondary Axis to an Excel Chart (Easy Tutorial)

Have you ever wanted to combine two data series with very different scales into a single chart? For example, maybe you want to graph both total sales revenue and number of units sold each month. The revenue numbers might range from $10,000 to $100,000 while the units sold could be in the single or double digits.

Trying to plot both on the same scale would result in a very skewed, hard to read chart. The units sold line would be squished down into the bottom, making it impossible to see any meaningful trends.

Luckily, there‘s an easy fix for this – adding a secondary axis! With a secondary axis, you can display a second scale on the right side of your chart for the smaller value data series. This lets you clearly visualize data that has very different magnitudes in a single chart.

In this article, you‘ll learn everything you need to know to start using secondary axes in your own Excel charts. I‘ll walk you through the process click-by-click (with screenshots) for Excel on Windows, Mac, and even Google Sheets. You‘ll also see tips and best practices for formatting your dual axis charts to perfection.

Windows Instructions (Excel 2024)

  1. Enter your data into a worksheet, with the independent variable (like dates or categories) in the first column followed by the two data series in the next two columns. For this example, we‘ll use:

    Month Total Sales Avg Units per Order
    Jan 2024 $87,500 12
    Feb 2024 $94,200 17
    Mar 2024 $114,900 21
  2. Select your data range and click Insert > Charts > Line. This will create a basic line chart on your worksheet.

  3. Right-click on the data series you want to plot on the secondary axis (Average Units per Order in this case) and choose "Format Data Series" from the menu.

  4. In the Format Data Series pane on the right, expand the "Series Options" heading. Under "Plot Series On", click the "Secondary Axis" radio button.

  5. You should now see the Average Units per Order data displayed on a new vertical axis on the right side of your chart. However, the default formatting probably has the secondary axis labels overlapping the primary on the left.

  6. To fix this, right-click the secondary vertical axis and select "Format Axis". In the pane on the right, expand the "Labels" options and check the "Left to right" box under "Label Position."

  7. While you‘re in the Format Axis pane, you can also customize things like the axis minimum/maximum values, unit size, number format, and more to perfect your secondary axis.

  8. To further distinguish the two data series, consider changing the marker type, line style or color used for the secondary data. Just select the data series and use the formatting options in the Chart Tools Design tab.

  9. Adjust other elements of your chart like the title, axis labels, legend, and gridlines as desired using the formatting panes. For a professional look, remove chart elements that aren‘t needed to understand the data.

  10. If the default positioning of the secondary axis labels doesn‘t quite work for your data, you can manually drag them to an open area on the chart. Just be sure not to obscure any important data points.

macOS Instructions (Excel for Mac 2024)

The process for adding a secondary axis is very similar in Excel 2024 for Mac. Here are the key steps:

  1. With your data selected, go to Insert > Charts and choose a 2-D Line chart.

  2. In the Chart Design tab, click the "Format" button next to the chart.

  3. In the Format pane on the right, select "Series (Your Secondary Data Series Name)" from the dropdown at the top.

  4. Expand the "Series Options" heading and click "Secondary Axis" under Plot Series On.

  5. Customize the formatting of your secondary axis labels, scale, line style, and more using the Format Axis pane, just like in the Windows instructions above.

Google Sheets Instructions

Adding a secondary axis works a bit differently in Google Sheets compared to Excel. Here‘s how to do it:

  1. Enter your data into the sheet and select it.

  2. Click Insert > Chart to open the Chart Editor pane on the right.

  3. In the Setup tab, scroll down to the "Series" section. Find the data series you want to plot on the secondary axis.

  4. Next to "Axis", click into the field and change it to "Right axis."

  5. Customize the chart title, colors, fonts, and other options in the Customize tab of the Chart Editor.

  6. When finished, click the X to close the Chart Editor. The completed chart will now display in your sheet.

Across all platforms, the key steps are selecting the data series to plot on the secondary axis, assigning it to the secondary axis, and then fine-tuning the formatting. The exact location of the options varies but the core process is the same.

Tips for Making Great Secondary Axis Charts

Just because you can add a second axis to your chart doesn‘t always mean you should. Ask if combining the two scales actually makes the data easier to interpret or just more cluttered.

Generally, a secondary axis works best when you have two data series that are related but have very different scales. If the series are only slightly different in scale, consider using a single axis and labeling the data points directly.

Whenever you do use a secondary axis, be sure to clearly label it to avoid confusion. Use colors, line styles, and dual legends to distinguish which data belongs to which axis.

Resist the urge to add more than two axes to a single chart. While technically possible in some cases, having more than a primary and secondary y-axis quickly becomes hard for the viewer to keep track of. If you have a third data series to plot, try a combo chart instead.

The secondary axis should relate in some way to the primary axis – like revenue vs. units, price vs. demand, etc. Avoid plotting two completely unrelated variables just for the sake of cramming them into one chart.

By keeping these guidelines in mind and following the steps in this tutorial, you‘ll be able to create informative, professional-looking charts that harness the power of the secondary axis. Go ahead and practice adding secondary axes to some of your own data visualizations!

To learn even more Excel charting techniques, check out our tutorials on making Gantt charts, Pivot charts, and interactive dashboard charts. Happy charting!

Tags: