Excel VBA - Add Data labels to Bar Chart

Posted on 2011-10-18
Last Modified: 2012-06-21
Hi Guys,

I have the cde below which takes data from a worksheet called 'ALL' and creates a bar chart. this is working well however I would like the bar chart to display datalabels for the values and can't seem to work out how this is added. we are using Excel 2002.
Can anyone point me in the right direction on how to add data labels to the chart I have created


 'Get last populated column for graph
    For c = 1 To 26
        Sheets("ALL").Cells(1, c).Select
        If ActiveCell.Value = Empty Then
            lastColumn = Chr(64 + (ActiveCell.Column - 1))
            Exit For
        End If
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=Sheets("ALL").Range("A1:" & lastColumn & numProjects + 1), PlotBy:= _
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Project Defects Graph"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Project Defects"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "QC Project \ Project Name"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Defect Count"
    End With
    Sheets("Project Defects Graph").Select
    Sheets("Project Defects Graph").move Before:=Sheets(1)
Question by:victoriaharry
    LVL 2

    Expert Comment


     if I remember correctly it was something along these lines

    hope this helps

    LVL 41

    Accepted Solution

    ActiveChart.SeriesCollection(1).ApplyDataLabels 'use this for each series, indexing starting with 1

    Author Comment

    Hi, thanks for the fast replys

    Can you please explain what is meant by a series. Is it a row of data?
    LVL 41

    Expert Comment

    If you have a bar chart with one dimension - e.g., you're showing Sales Volume by month - that would be a series.

    If you added a bar to that, with say, Sales Earnings by month - now you have two bars per month - that's two series.

    Make sense?

    LVL 41

    Expert Comment

    Here's an example - one independent variable (their vernacular) is for one series, and there's an example with two series.


    Author Comment


    I have attached a screenshot of the chart without the data labels. I require a data label for each of the values in the stack. Would this still be considered one series so all I would need would be the code below or are stacked columns treated differently



    Gavin Chart
    LVL 41

    Expert Comment

    You can apply one datalabel, but then you'd need to overwrite the label with the real value of the series added together, if I'm reading the bar right.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now