LinkBack URL
About LinkBacks
Using Excel 2007. Can someone point me to a tutorial explaining how to add a second primary axis and plot data to it?
Using Excel 2007. Can someone point me to a tutorial explaining how to add a second primary axis and plot data to it?
Perhaps this? http://office.microsoft.com/en-us/ex...149.aspx?CTT=1
If that is not it, perhaps you can explain what a "second primary axis" is.
Originally Posted by shg
An Excel chart has a primary axis on the left side, with a secondary axis on the right side. Everything I've found discusses adding another secondary axis - on the right side. I want to add another Y axis on the left side of the chart.
example contains variations of axes and the position of their labels and lines.
Any of these fit the bill?
Hi Andy. Thanks for help with this model. "Large on Secondary Y axis
both axis on left by adding secondary X axis" is what I'm looking for.
Last edited by Phil Hageman; 09-19-2013 at 08:21 AM.
Okay, do you need any more info on how to do that?
Yes - do you have instructions on how to do this? BTW, I already have a plot going on on the secondary (right side) axis.
On another topic, I am using your method of labeling end points based on a dummy series. It inserts the label when new values are posted, but the previous label remains. Is there a way to remove/replace labels?
Finally - is there a way to color format a line series based on ranges? Example: 300-250 red, 249-200 yellow, 199-150 green.
select the chart and use ribbon Chart Tools > Layout > Axes > Secondary Horizontal Axes. This will add horizontal axis to top of plotarea.
Format secondary horizontal axis. Set Vertical Axis crosses at > Automatic.
The label last point should automatic remove data labels when the data point is NA().
You would have to use additional series to colour sections of the line. Problem comes when the line crosses a boundary.
This example illustrates the technique.
http://www.andypope.info/charts/conditionalline.htm
Thanks. Will get busy and report back later.
Secondary horizontal axis in place (top of plot area). Right click to format, but there is no "Automatic" option under "Vertical axis crosses." I want to move an existing line to the new vertical axis on the left side of the chart, but selecting it does not offer an option I can identify to link it to the new horizontal axis.
see picture of formatting dialog from xl2007
On the line graph labeling, typical formula is: =IF($C19="",E18,NA()). What I see in the cell is #N/A. Changed the formula to =IF($C19="",E18,"NA()"), got NA(), but the previous labels still remain. Is there a setting somewhere I'm overlooking?
Don't put quotes around it, that will cause it to be treated as text and text will be plotted as zero
=IF($C19="",E18,NA())
cell should display #N/A, this will cause the chart not to plot a data marker or create a line.
Formatting axis: Three options appear - Between dates; At date...; and At maximum date. No Crosses at/automatic option.
Labeling: Removed the "" and tried it again. Previous labels still appearing - so Excel does not recognize the #N/A for some reason.....
You did not mention your horizontal axis was dates.
set to between dates.
can you post an example of your file then because my examples do not appear to be the same as what you are using and it is causing confusion.
Andy, Per attached model:
Re secondary axis: Two series plotting okay on the left axis, need the BMI series to plot on a right side axis with an maximum of 40, and minimum of 20.
Re labeling: Previous label does not go away when new weight posted.
The series was not set to use the secondary axis.
Once moverd along with the associated Target series I set the min/max of the secondary scale.
The labelling does not work as you need to include another line series that only plots the relevant data point. I have added a new series Label WT.
I also had to change the formula so that #N/A was put out for future data points rather than a zero value.
Andy,
I'm new to Excel 2007 so may have something set up wrong, which could be causing my issues.
1. Labeling still does not remove the prior label on the BMI series when a new weight is posted. It works okay on the weight series - should I change the formulas like you did on the weight series?
2. BMR does not plot at all - is it not possible to plot three series, two on one side as primary/secondary, and one on the other side? I need to show all three series on the chart with axes sufficient to amplify the series for easier analysis.
Regards,
Phil
You can only have 2 Y axes. You appear to want 3. Only way to do that is to factor one of the set of values against one of the other scales.
You need to add dummy series for all those you want to label the last point for. You will also need to update the formula otherwise zeros will be displayed (if the scale permits it)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks