how to add two y axis in excel
Adding a second Y axis to an Excel chart can help you display more information on a single chart and empower your users to make better business decisions. To learn how to create a chart with two Y axis in Excel read on….
Two Y axis on an Excel chart
If you are creating an Excel
For example, if you want to display the number of units sold and the income made from those sales on one chart you might find that the two data series (units sold and income) end up looking something like this on your Excel chart:
The flaw with this chart is that units sold are in multiples of ten whereas the income is in multiples of thousands. As a result of this mismatch in data the Excel chart fails to fit the data correctly and it makes the number of units sold impossible to read, the line just looks flat at the bottom of the chart if you can see it!
The solution
The solution is to create a second Y axis (Y is the vertical axis for any newcomers reading). By creating two Y axis on the Excel chart you will be able to have two different scales on the same chart therefore making it easier for any end-users, and yourself, to interpret the data correctly and see trends or correlations.
To create a second Y axis the first step is to determine which of the two data series, (in this example units sold or Income) should be on the second Y axis. There is no rule to this but for this example I set the income to be on the second Y axis.
Once you have decided which data series will be on the second Y axis right click the mouse button while the mouse pointer is anywhere on that line, you will see the data-points of that line highlight and a new option box will appear in Excel:
In the new option box select the last option called "Format Data Series", select it using the left mouse button, a new options window will open up:
This new window allows you to format the highlighted data series in a number of ways by selecting the various categories on the left hand side. However, the window defaults to the option needed here and that is "Series Options". The default selection in this case is to plot the data series on the primary axis, the key here is to change it to the secondary axis:
When you have changed the option button to "Secondary Axis" click on close (using the left mouse button) and you will automatically see your chart update to having two Y axis like below:
Now you can see the secondary Axis, income, is plotted against the right hand side Y axis and we can now clearly view the Units sold line because that has reverted to being the only data series plotted against the primary Axis on the left side.
While that has now completed the task I always find it good practice to tidy up the chart and place some labels on the two Y axis, that way it helps avoid any confusion to what the end-user is looking at. A more complete Excel
Summary
Using two Y axis in an Excel chart allows you to display two different data series in a user-friendly manner, especially when their values derive from very different ranges, i.e. tens for units sold or thousands for income.
To add a second Y axis to an Excel chart create the chart as normal then right-click on the data series you want to plot against the second Y axis and select "Format Data Series" from the option box that appears.
In the "Format Data Series" window select "Secondary Axis" and the chart will update with your selected data series now plotted against a second Y axis.
For enhanced user-friendly reporting or dashboards make sure you add some labels to the chart so the end-user can clearly understand what values they are analysing.
That's all for this post but I hope you find this as useful as I do in your Excel
Keep Excelling,
how to add two y axis in excel
Source: https://dedicatedexcel.com/how-to-create-a-chart-with-two-y-axis-in-excel-2010/
Posted by: marshallzekere.blogspot.com
0 Response to "how to add two y axis in excel"
Post a Comment