Link to home
Start Free TrialLog in
Avatar of JCJG
JCJG

asked on

Excel 2007: How to add variance % on graph

Hi, I have a graph comparing actual vs. budget and I would like to add the variance % on the actual line as a data labels (file attached).  Can I do that?
Graph.xlsx
Avatar of wchh
wchh

Please refer to the attachment...
variance % set to secondary Axis
Graph-1-.xlsx
Avatar of JCJG

ASKER

Hi, thanks for your input.  I have 2 questions.  First how do I add the secondary axis?  Second, I don't want to see the variance % line which I can set to no line in the format option.  But I also want to variance data labels to be positioned right next to the actual line.  In other words, I want the data label of the actual line to show the variance % as the data label.  Is it possible?
Steps:
1. Select Data, Add, (name and range)
2. Right-click variance line and select Format Data Series:
a. Series Option: Select Secondary Axis
b. Line color: None (get rid of line)
3. Right-click variance line and select Format Data Label: Check-on Value for Label Option ('Label Contains' section)



Graph-1-.xlsx
Avatar of JCJG

ASKER

Got the first part.  Thanks.

But I want the red data labels to be positioned next the the green line.  If this is not possible I would like them to line up at the same level.  Is it doable?
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JCJG

ASKER

Thanks!  It works beautifully.  How do I modify the codes if I want the variance % to be positioned on top of the bars?
After line 11:

ser.Datalabels.Position = xlLabelPositionAbove
Avatar of JCJG

ASKER

Sorry if I didn't make myself clear.  I meant position above the blue bars, not the green line.  Is it possible?  Thanks.
Yes change this line:
Set ser = cht.SeriesCollection(2)

to this:
Set ser = cht.SeriesCollection(1)