Q:

Trying to add 'year' to X axis in an Excel file

I have the following chart, and I want to show each year under its specific data: 2000, 2001, 2002, ... 2016, instead of the general (Year 2000-2016) caption which can be seen right now. 

But when I try to add years, the whole chart becomes unstable and my information gets lost! How should I do this?

A:

My assumption is that you've only selected column B when creating the chart. You should try to create a new chart but this time selecting both columns. It would be also good to add headers to the columns (i'm assuming that the titles in the chart are hand-written). I guess that in order to show the second axis value, you should click on that "+" button you see to the top right of the chart. A menu should appear (chart elements). In this menu you'll see the axis entry. Move the cursor over it and a black triangle should appear on its side. Click on that triangle. A sub menu should appear. In it you'll see 3 entry: primary horizontal, primary vertical and other options. Primary vertical is already active, primary horizontal is not. Click on it to turn it on. Now your horizontal axis will have the values specified. They will probably be numbers from 1 to 17. That's because you haven't created a chart selecting both column A and column B. Ence that's why i've suggested you to create a new chart. You might also edit your actual chart to cover the column A, but i don't know how much longer you'd like to read my explanation. :)
Anyway, this code also should give you the chart you are looking for:
Sub SubAddChart()

    'Declarations.
    Dim Report As Object

    'Creating data.
    Range("A1").FormulaR1C1 = "Number of uses in Neurosurgery"
    Range("A3").FormulaR1C1 = "2000"
    Range("A4:A19").FormulaR1C1 = "=R[-1]C+1"
    Range("A2").FormulaR1C1 = "=""YEAR ("" & MIN(" & Range("A3:A19").Address(True, True, xlR1C1) & ") & ""-"" & MAX(" & Range("A3:A19").Address(True, True, xlR1C1) & ") & "")"""
    Range("B2").FormulaR1C1 = "Number of uses"
    Range("B3").FormulaR1C1 = "0"
    Range("B4").FormulaR1C1 = "0"
    Range("B5").FormulaR1C1 = "2"
    Range("B6").FormulaR1C1 = "2"
    Range("B7").FormulaR1C1 = "1"
    Range("B8").FormulaR1C1 = "1"
    Range("B9").FormulaR1C1 = "8"
    Range("B10").FormulaR1C1 = "14"
    Range("B11").FormulaR1C1 = "15"
    Range("B12").FormulaR1C1 = "12"
    Range("B13").FormulaR1C1 = "13"
    Range("B14").FormulaR1C1 = "17"
    Range("B15").FormulaR1C1 = "21"
    Range("B16").FormulaR1C1 = "25"
    Range("B17").FormulaR1C1 = "31"
    Range("B18").FormulaR1C1 = "24"
    Range("B19").FormulaR1C1 = "29"

    'Creating chart.
    Set Report = ActiveSheet.Shapes.AddChart2(201, xlColumnClustered)

    With Report
        With .Chart
            'Setting source data.
            .SetSourceData Source:=Range(Range("A2"), Range("A2").End(xlDown).Offset(0, 1)), PlotBy:=xlColumns
            'Deleting years series collection.
            .SeriesCollection(Range("A2").Value).Delete
            'Changing X axis labels.
            .FullSeriesCollection(Range("B2").Value).XValues = "=" & ActiveSheet.Name & "!" & Range(Range("A3"), Range("A2").End(xlDown)).Address(, , xlR1C1)
            'Adding chart title.
            .HasTitle = True
            .ChartTitle.Caption = "=" & ActiveSheet.Name & "!" & Range("A1").Address(, , xlR1C1)
            'Adding axis title.
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Caption = "=" & ActiveSheet.Name & "!" & Range("B2").Address(, , xlR1C1)
            .Axes(xlCategory, xlPrimary).AxisTitle.Caption = "=" & ActiveSheet.Name & "!" & Range("A2").Address(, , xlR1C1)
            'Adding data labels.
            .SetElement (msoElementDataLabelOutSideEnd)
        End With
    End With

End Sub

To make it work, you can rightclick the sheet's tag where you want to place the chart (i suggest a new sheet for the first try), click on "Show code", paste the code, click on it, press F5.
Extra explanation: i'd point to a chart introduction tutorial like this one: https://www.youtube.com/watch?v=DAU0qqh_I-A . Following it one should be able to obtain the chart he is looking for. The main problem behind this question was that while creating the chart not all the data was selected/specified. Selecting only the data in column B gives Excel only part of the data. Enouth to make a chart, not enouth to make the chart we wanted. Excel won't look outside the selected/specified range for labels or extra data in general. We need point at them first. The selected/specified range is all Excel will consider in creating the chart. So by selecting only the B column, Excel would had no idea that the data in column A were supposed to be the data labels in our chart. Chart title and axis title were added manually. This could have been done automatically by Excel itself if we were to add at the top of our data some headers; of course we should had also to select/specify those headers while creating the chart. One has also to consider if the wanted elements (like data labels) are set to be in the chart. To make it happend, one can use the top-right plus button nearby the chart (once selected) to set what elements are to be in the chart.

