How to Add a Secondary Axis to an Excel Chart

Then, click "Charts," navigate to the "Column" section, and select "Clustered Column" -- the first option, as shown below. Now it's time to turn the "Percent of Nike Shoes Sold" data -- currently row 3 in the spreadsheet -- into your chart's secondary Y axis. Click this dropdown and select "Series 'Percent of Nike Shoes Sold'" (or whichever series you want as your secondary axis). After you select "Series 'Percent of Nike Shoes Sold,'" click on the "Format Selection" button -- it's right below the dropdown. To give this data a secondary Y axis, click on one of these bars just above the X axis line until they become highlighted. Having highlighted this additional data series on your chart, a menu bar labeled "Format Data Series" should appear on the right of your screen, as shown below. In this menu bar, click the "Secondary Axis" bubble to switch your Percentage of Nike Shoes Sold data from your primary Y axis to its own secondary Y axis. Then, click into "Chart Design" on the menu bar on top of your Excel spreadsheet. Click on "Format." In this menu bar, click the "Secondary Axis" bubble to switch your Percentage of Nike Shoes Sold data from your primary Y axis to its own secondary Y axis.

Author: Rachel Leist / Source: hubspot.com

Have you ever wanted to create a single chart for two different (yet related) pieces of data? Maybe you wanted to see the raw number of leads you’re generating from each channel and what the conversion rate of the channel is.

Having those two sets of data on one graph is extremely helpful to picking out patterns and identifying full-funnel trends.

But there’s a problem. Those two sets of data have two Y axes with two different scales — the number of leads and the conversion rate — making your chart look really wonky.

Luckily, there’s an easy fix. You need something called a secondary axis: it allows you to use the same X axis with two different sets of Y-axis data with two different scales. To help you solve this pesky graphing problem, we’ll show you how to add a secondary axis in Excel on a Mac, PC, or in a Google Doc spreadsheet.

(And for even more Excel tips, check out our post about how to use Excel.)

Note: Although the following Mac and Windows instructions used Microsoft Excel 2016 and 2013, respectively, users can create a secondary axis for their chart in most versions of Excel using variations of these steps. Keep in mind the options shown in each screenshot might be in different locations depending on the version of Excel you’re using.

Free Download_ Facebook Live Guide for Business (2)

How to Add Secondary Axis in Excel

Gather your data into a spreadsheet in Excel.
Create a chart with your data.
Add your second data series.
Switch this data series from your primary Y axis to your secondary Y axis.
Adjust your formatting.

On a Mac Computer (Using Excel 2016)

1. Gather your data into a spreadsheet in Excel.

For the purposes of this process, we’ll create three rows of data on Nike shoe sales in a blank spreadsheet:

Shoe sizes
Number of shoes sold per size
Percentage of that size’s inventory that was sold

Make Row 1 your X axis and Rows 2 and 3 your two Y axes. For this example, Row 3 will be our secondary axis.

2. Create a chart with your data.

Want a detailed guide to creating a chart in Excel? Click here.

Otherwise, you can highlight the data you want to include in your chart and click “Insert” on the top-lefthand corner of your navigation bar. Then, click “Charts,” navigate to the “Column” section, and select “Clustered Column” — the first option, as shown below.

Instructions to insert a clustered column chart in Excel 2016 on a Mac computer — Your chart will then appear below your data set.

3. Add your second data series.

Now it’s time to turn the “Percent of Nike Shoes Sold” data — currently row 3 in the spreadsheet — into your chart’s secondary Y axis. Head over to your top navigation bar and click on “Format.” This should pop up in dark green next to “Chart Design,” as shown to the far right in the screenshot below.

Having selected “Format,” navigate to the dropdown menu on the top-lefthand corner of your menu bar, where it might currently say “Chart Area.” Click this dropdown and select “Series ‘Percent of Nike Shoes Sold'” (or whichever series you want as your secondary axis).

After you select “Series ‘Percent of Nike Shoes Sold,'” click on the “Format Selection” button — it’s right below the dropdown. A pop-up will come out that gives you the option to select a secondary axis. If you’re using a version of Excel that doesn’t provide you with this formatting button, move on to the fourth step below.

4. Switch this data series from your primary Y axis to your secondary Y axis.

You’ll see your new data series added to your chart, but currently, this data is being measured as a low-laying series of columns on your primary Y axis. To give this data a secondary Y axis, click on one of these bars just above the X axis line until they become highlighted.

Having highlighted this additional data series on your chart, a menu bar labeled “Format Data Series” should appear on the right of your screen, as shown below.