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
Last Comment
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
variance % set to secondary Axis
Graph-1-.xlsx