How to add secondary axis in excel? Plot one or more data series on a separate vertical (value) axis when the values in a chart differ significantly among data series or when you have mixed forms of data (price and volume). The secondary vertical axis’ scale displays the values for the corresponding data series. A chart that combines column and line charts benefits from the addition of a supplementary axis. By switching your chart to a combo chart, you may rapidly display a graphic similar to this.
Why Add A Secondary Axis To An Excel Chart?
I’m sure you won’t be able to tell by looking at the chart. If you plot all the data on the same axis, you cannot compare different proportions of the data. These two cannot be drawn on the same axis. Solution – Add auxiliary axes to draw numbers.
Therefore, we added a secondary axis to the mix to make the chart better.
Excel Secondary Axis Addition Instructions
- Create an Excel spreadsheet with your data collected.
- Make a chart using the data you have.
- Add a second set of data.
- Your primary Y axis should now be your secondary Y axis for this data series.
- Change the formatting.
Add Or Remove Secondary Axes From Charts In Office 2016
Collect data into a spreadsheet in Excel.
For the purpose of this process, we’ll create three rows of Nike shoe sales data in a blank spreadsheet:
Shoe size
Number of shoes sold in each size
Percentage of inventory sold for that size
Use row 1 as the X-axis, and row 2 and row 3 as the two y-axes. In this case, line 3 is our secondary axis.
excel-data-two-y-axes-1
Create charts with data.
Want a detailed guide to creating charts in Excel? Click here.
Otherwise, you can highlight the data you want to include in the chart and click Insert in the upper left corner of the navigation bar. Then, click Chart, navigate to the Columns section, and select Aggregate Columns — the first option, as shown below.
Insert a description of the aggregated bar chart in Excel 2016 for your Mac
The chart will then appear below the dataset.
Excel charts MAC
Add a second data series.
Now, it’s time to convert the “percentage of Nike shoes sold” data (currently row 3 in the spreadsheet) to the second Y-axis of the chart. Go to the top navigation bar and click on “Format.” This should pop up in dark green next to “Chart Design,” as shown in the screenshot on the far right below.
Read about: CTO Meaning In Business: Definition – Tech Office Supplies
After selecting Format, navigate to the drop-down menu in the upper-left corner of the menu bar, where the Chart Area may currently be displayed. Click this drop-down menu and select “Series’ Percentage of Nike Shoes Sold ‘” (or whatever series you want as a secondary axis).
Add the series “Nike Shoe Sales Percentage” as an additional dataset in Excel for Mac
After selecting Series of Nike Shoes Sale, click the Format Selection button right below the drop-down list. A pop-up window will appear, giving you the option to select the secondary axis. If the version of Excel you are using does not provide this formatting button, go to Step 4 below.
Switch the data series from primary to secondary Y-axis.
You’ll see new data series added to the chart, but for now, these data are measured as a series of columns on the main Y-axis. To provide a minor Y-axis for this data, click one of these bars right above the X-axis until they are highlighted.
After highlighting this additional data series on the chart, a menu bar labeled “Format Data series” appears on the right side of the screen, as shown below.
Format the data series window in Excel 2016 so that the Mac can add data to the secondary axis
In this menu bar, click the “Secondary Axis” bubble to switch your Nike shoe sales percentage data from the primary Y Axis to its own Secondary Y Axis.
Adjust the format.
Now, you have your own percentage on the Y-axis on the right side of the chart, but we’re still not done. See the overlap between “Nike Shoe sales Percentage” and “shoe Sales quantity” column? Ideally, you want to present secondary data series in a different form than columns. Let’s do that.
Secondary axial overlapping
Click the orange column (as shown in the screenshot above) to highlight the second data series again. Then, click “Chart Design” on the top menu bar of the Excel spreadsheet.
On the far right, select the Change Chart Type icon and hover your cursor over the Line graph option. Select the first line chart option that appears, as shown below.
Change the chart type in Excel and add the orange bar to the secondary Y-axis description
Look! Your chart is ready, showing the number and percentage of shoes sold, based on shoe size.
Add Or Remove Secondary Axes From Charts In Office 2010
When values in a two-dimensional chart vary by data series, or when data types (such as price and volume) are mixed, one or more data series can be drawn on the second vertical (value) axis. The proportion of the sub-vertical axis reflects the value of the relevant data series.
After adding the auxiliary vertical axis to the two-dimensional chart, you can also add the auxiliary horizontal (category) axis, which can be useful in an XY scatter plot or bubble plot.
To help distinguish between data series plotted on a secondary axis, you can change their chart type. For example, in a bar chart, you can change the data series on the secondary axis to a line chart.
Important: To complete the following steps, you must have an existing two-dimensional diagram. Secondary axes are not supported in 3D diagrams.
Conclusion
The above gives a specific answer on how to add a secondary axis in excel. Hope this will help you. Thank you for reading.
