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
Graph.xlsx
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
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ser.Datalabels.Position = xlLabelPositionAbove
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)
Set ser = cht.SeriesCollection(2)
to this:
Set ser = cht.SeriesCollection(1)
variance % set to secondary Axis
Graph-1-.xlsx