Minnesota Bridge Image
St. Anthony Main
219 Main Street SE, Suite 302
Minneapolis, Minnesota 55414

612.623.9110
(f) 612.623.8807


How to insert multi-level horizontal axis labels in Excel

Did you know that if you want to label both the year and the month on the horizontal axis of a line chart that Excel will automatically do that for you? This is what I mean:

 

I may be late to the party on this one, but I was so excited when I discovered this trick a few years ago, and I wanted to (finally) share the love in case any one else was in the dark about it.

Here’s the problem we were running into. Sometimes in a line chart there were two calendar years represented within the same series. We often used one column in Excel to represent both the month and the year, entering “Jul-2015”, for example. But that resulted in a lot of unnecessary repetition with the year labels, which cluttered the horizontal axis and, frankly, made me twitch.

month and year appears for all horizontal axis labels

Another option we sometimes used was to only include the month in the Excel data, entering “Jul” for example, and then we manually added text boxes to label the years. It took a little more time, but the repetition issue was resolved and we had control over how the labels looked. Definitely an improvement.

only months are automatically added to the horizontal axis; years are manually inserted

But then I discovered that Excel is smarter than I sometimes give it credit for. There is also the option of entering the years in the first column and the months in the second column. Excel will automatically understand that you have two levels of horizontal axis labels and format them as such. Whaaaat?! While you have less control over the look of the labels, the automation is pretty stinkin’ sweet.

months and years automatically added on two different levels of the horizontal axis

How do others approach labeling the years and months in a line chart when there are two calendar years represented within the same series? Or has this function served you in another way with different types of data? I’m sure there are other tricks out there that we don’t know about, so please share!

 

Posted in Excel, General