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:
When the user created the 2-D column chart, here is what he got:
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.
Now you can always put the year back into the column header after the fact:
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 
Video Tutorial
Check this out in action with this video demonstration:This embedded resource is unavailable in the restored edition.
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.
Steve=True
Reader discussion
Comments preserved from the original page. New comments are closed.
Reader
April 2, 2018 At 2:27 pm
Spent over an hour trying to show the Years on the X horizontal on my Combo Excel chart, and your solution was so simple. As suggested in other posts, I tried reformatting the type of cell several ways, and it did not work that well. Thanks.
Reader
April 2, 2018 At 6:35 pm
Thank you for the GREAT feedback. So glad to help!
Reader
April 6, 2018 At 12:19 am
very helpful. tnx a lot 🙂
Reader
April 11, 2018 At 10:06 am
Thanks for the nice comment.
Reader
June 28, 2018 At 1:30 pm
Saved the day! Thank you.
Reader
June 28, 2018 At 1:41 pm
Awesome, thanks for the great comment!
Reader
August 7, 2018 At 9:56 am
Thanks for an amazingly simple solution to a most frustrating problem. After wasting an hour and a half I can now get on with the job in hand.
Reader
August 8, 2018 At 8:12 am
you are welcome. yeah, drove me nuts for a while too.
Reader
February 6, 2019 At 12:18 am
Thanks a lot for saving the time for putting Year in X horizontal its really very simple solution.
Reader
February 6, 2019 At 2:30 pm
You are welcome Shrutika. Thanks for the great comment. Glad to help out.
Reader
February 18, 2019 At 10:11 am
can anyone tell me the cause of the trick works?
Reader
February 20, 2019 At 7:49 am
Hi Sandip, of course, they do 🙂 I wouldn’t post without it really working. This is an easy one, so watch the video and give it a shot.
Reader
August 7, 2019 At 6:24 pm
I think sandip is asking WHY this works.
Reader
August 9, 2019 At 6:25 am
Thanks Kelli, I missed that. Excel sees any column of numbers as another data series when it has a header text. Removing the header text tells Excel to consider this column of numbers as the category label and not a data series. NOTE: that it only works on the left most column of numbers/years. If you have a column of years in the middle of other columns of numbers, removing the header text will not remove it as a series. In essence, if you have the following columns:
Dept….Year…..Value1….Value2
If you remove the Dept and Year header text, it will create a a multi level level category label of Dept/Year and you will have 2 series of values. If you leave Year header text, the year will be considered a series and you will have 3 series of values that also includes the year.
Hope this helps!
Reader
February 12, 2021 At 1:53 am
Type a single apostrophe immediately before the year in each cell (eg ‘2020).
Reader
February 12, 2021 At 8:02 am
That is a great work around. thanks!