How-to Make Excel Put Years as the Chart Horizontal Axis Categories

I saw this post in a forum yesterday and thought that everyone should know this.  Lord knows that I didn’t know this until just a few years ago and it would drive me crazy.

Here was the question from the forum:

“Thanks in advance – this is embarrassing. Very basic question. My data below:Year Gross Profit2013 $61.5 2014 $50.3 2015 $43.8 I simply want to the years displayed on the X-axis on a bar chart … but it keeps displaying the years as values, so I have two values – one year and one gross profit – with data series labeled 1,2,3. Thank you.”

Here is what the Excel user’s data would look like with years on the left and gross profit on the right:image

When the user created the 2-D column chart, here is what he got:image

Notice that Microsoft Excel created another series for the year.  It was supposed to be the categories for the horizontal axis.   But instead, Excel put in the generic, 1, 2, 3 for the axis.

So how do you fix it?

Solution: Remove the column header of “Year” from the chart data range.

See what happens when you delete “Year” from row a1?  The chart does what is desired.image

Now you can always put the year back into the column header after the fact:image

It is a simple Excel Tip and Trick, but until you see it once, you probably didn’t know about it.  Maybe they teach this as the first thing in all the classes, but I didn’t see this for the longest time.  Now you know too Smile

Video Tutorial

Check this out in action with this video demonstration:

Please remember to subscribe to my blog so that you get the next post delivered directly to your inbox.  Also, let me know your best Excel trick in the comments below.