Excel has trouble with defining your chart correctly if the X axis is numbers, unless…..
You can download the file here and follow along. If a preview appears, just look for the download arrow in the upper right corner.
Here is an example of a data range of sales for several years:
If we created a chart from this data (to do this quickly, highlight the data range and hit Alt + F1 to have it on the same sheet, or F11 to put it on a new sheet) we would get the following:
Notice how Excel interpreted the X Axis data of Years to be data for the chart rather than X Axis labels. One solution around this is to make the Years to be labels or text. This can be done by adding an apostrophe in front of each number (‘2001, ‘2002, etc.), or changing the label altogether by adding YR in front of each year (YR2001, YR2002, etc.). Either of these will work:
But perhaps the simplest way is to remove the header from the column you want to be the X Axis:
Excel will now interpret the Years column numbers as labels for the X Axis! You can then go back and add “Years” to the column header after the chart is created.