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.
, 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 report that contains charts, and especially if you are creating an Excel dashboard where space is at a premium, then displaying two unique sets of information is extremely useful. One of the key problems with trying to do this is when your values have very different ranges.
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 chart with two Y axis ends up looking like: 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 work.
Keep Excelling.
If you're familiar with Excel, I can simply ask: How do you create a secondary axis in a scatter plot? More descriptively, when one works in a scatter plot, you're comparing one set of numbers to another. Let's say you have two sets of numbers (to be put on the Y-axis) that you want to compare to another set of numbers (on the x-axis), but the two sets of numbers are of greatly different scale. You'd want to put on set of data on the primary axis (usually located on the left side of the chart), and put the other set of data on the secondary axis (usually located on the right side of the chart).
Can this be done in Numbers? Create the scatter plot with your x-values and first set of y-values. Select the second group of y-values in your table, and drag the selection to the chart.
![]()
Drag the same selection to the chart a second time, and some new data points will appear (don't worry if they're not in the right location at this point). Click on one of the data points from the second group. Now go to the Chart Inspector, choose the 'Series' tab and edit the X-Data entry to reflect the same x-values range as for your first series of data. There's no automatic way to put a secondary axis on a Scatter Chart, but that shouldn't stop you.
Add a Plain Table to your Sheet and adjust its size to one column, and with a number of rows equal to the number of horizontal grid lines you are using. Set the borders and fill of this new Table to None, type in your labels and position the Table to the right side of your Chart. Adjust the Font and Row Height, tweak the position of the Table. Create a new column for the data that you want to chart on your Secondary Axis and scale that data based on the ratio of the Secondary Axis to the Primary Axis. Assuming that you are sharing X-values as Brie suggested, you can then drag your scaled data to the Chart. That's a neat way to get around this problem.
I would recommend for the left-side Y axis that you enter the min and max in the Table Inspector and not leave them as 'auto'. You don't want them adjusting by themselves later in the future. This got me thinking. You can color-coordinate the text on the two Y axes so they are the same color as their respective lines. That makes it instantly recognizable on screen and on a color printout which axis goes with which data. With color coordination and a multi-column right side Y-axis table, you could add additional sets of Y axis labels for additional data series.
Apple Footer. This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |