Excel 2007: How to add variance % on graph

JCJG
JCJG used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Please refer to the attachment...
variance % set to secondary Axis
Graph-1-.xlsx

Author

Commented:
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?

Commented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Author

Commented:
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?
Most Valuable Expert 2011
Top Expert 2011
Commented:
You could run code like this:
Sub AddDataLabels()
    Dim cht As Chart
    Dim ser As Series
    Dim n As Long
    Dim dl As DataLabel
    Dim rngLabels As Range
    
    Set rngLabels = Range("D3:D22")
    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set ser = cht.SeriesCollection(2)
    ser.HasDataLabels = True
    For n = 1 To ser.Points.Count
        ser.Points(n).DataLabel.Text = "='" & ActiveSheet.Name & "'!" & rngLabels(n).Address
    Next n
End Sub

Open in new window


You may want to alter the position of the datalabels.

Author

Commented:
Thanks!  It works beautifully.  How do I modify the codes if I want the variance % to be positioned on top of the bars?
Most Valuable Expert 2011
Top Expert 2011

Commented:
After line 11:

ser.Datalabels.Position = xlLabelPositionAbove

Author

Commented:
Sorry if I didn't make myself clear.  I meant position above the blue bars, not the green line.  Is it possible?  Thanks.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yes change this line:
Set ser = cht.SeriesCollection(2)

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial