How to tell Excel that "Years" are "Years", not data

Question

I have googled everywhere about this but no solution found. I have three columns in Excel:

• A: Years, from 1960 to 2013
• B: Maize production (in tonnes)
• C. Wheat production (in tonnes)

If I make a line chart from A, B and C, the years appear on the chart as a (very straight) line. The horizontal axis just goes from 1 to 20 (the row numbers).

If I select only columns B and C, the horizontal axis is still 1 to 20. If I try to replace that with "Years" through "Select data", I see no option for replacing 1 to 20 - only an "Edit" option that doesn't include "Put the years on the horiziontal axis!!!"

Answer 1

Got it! In line 1 of the 3 columns, I had named the columns "Year", "Maize" and "Wheat".

Prompted by intuition gained from years of battling with Powerpoint and other MS products, I deleted "Year" from the head of column 1 (i.e. I left the cell blank) and zippo-facto, Excel treats the numbers that follow (1961, 1962, 1963...) as years (or, at least, decides they must be the horizontal axis).

"Counter-intuitive" - that's always been a feature of MS products.

Answer 2

Assuming Excel 2010 (although the other versions are similar):

1. Open the Select Data dialog box
2. If necessary, delete the Years series from the Legend Entries (left) window.
3. Select the Edit button from the Horizontal (Category) Axis Labels (right) window.
4. In the Axis Labels dialog box, select Year Column data.

Now, you should have two lines (Wheat & Maize) and the Horizontal Axis should be Years.

Answer 3

There are many things you could do.

1. Make the chart, then use the Select Data dialog to fix the way Excel parsed your data. This is what @dav suggested.

2. Delete the top left cell ("Years"), which tells Excel that the first column is different (X not Y values) and the first row is different (names not Y values). It guessed at the first row, because it was text. You figured this out yourself, bravo!

3. Start by creating an XY Scatter chart. Excel uses the first numerical column of an XY chart as X values. Then you can change the chart type to a Line chart.

4. Enter actual dates in the first column: 1-1-1960, 1-1-1961, etc., then use a number format of YYYY to show only the years. This makes the first column recognizably different to Excel (dates not numbers) so it will use this column for X values.

5. Select just the Y values and make your line chart, then use the Select Data dialog to add the years as category labels (a subset of @dav's answer).

This is a topic I cover when I do my charting workshops.