How to add X axis labels in Google Sheets

Show Month and Year in X-axis in Google Sheets [Workaround]

Prashanth

Google Sheets Column, Line, and Bar charts do have the capability to display multiple categories in the x-axis. But its limited and not like the ones that you may have experienced in other applications. Here in this workaround that to show month and year in x-axis in Google Sheets, you will see that limitation.

Table of Contents Show

Show Month and Year in X-axis in Google Sheets [Workaround]
How a Normal Column Chart Displays Monthly Data
Essential Column Chart Settings Related to Monthly Data
The Workaround to Display Month and Year in X-axis in Sheets
Split Month and Year to Use as Categories and Sub-categories in Chart
Chart Editor Settings to Display Month and Year in X-axis

Here the limitation means, the chart will display the years in the x-axis, but the month names will be displayed as x-axis labels.

By displaying month and year in the x-axis, we intend to use years as the main category and months as its sub-category. I hope the below workaround will address that moderately.

How a Normal Column Chart Displays Monthly Data

If you have monthly consumption data as below, its possible to show month and year in x-axis in a combined form.

Assume I have the monthly consumption of gasoline by a fleet of trucks (fleet fuel spending data) in our company for the last two years.

I have that data in a Google Sheets file, formatted as above. With that data, I can plot a column, bar, line, etc. graphs in Docs Sheets.

Must Read: Built-in Charts, Dynamic Charts and Custom Charts in Google Sheets.

Here I am going to create a column chart which is more common in such type of visualization. In that, the category axis (x-axis) will contain the month and year values. But it will be cluttered as below.

This is the normal way of displaying the month and year in a column chart. In this, you can see that the x-axis is crowded with values. Its somewhat cluttered. How to solve this?

Before going to explain how to show month and year in x-axis using the workaround, see the chart settings of the above column chart. This can be valuable for newbies to column chart in Sheets.

To plot the chart, select the data range and go to the menu Insert > Chart. You can also use the keyboard shortcut Alt+I+H to insert a chart from the selected range in a flash.

The above action will take you to the chart editor in Sheets. Below are the chart editor settings that you must make under the Setup tab to plot the above column chart.

Chart typeColumnStackingNoneData rangeA1:B25X-AXISA1:A25 (by default you will see the field label)SERIESB1:B25 (by default you will see the field label)Check Mark Against:Use row 1 as headersUse column A as labels

Under the Customize tab, click on Horizontal axis and enable (toggle) Treat labels as text.

The Workaround to Display Month and Year in X-axis in Sheets

First of all, see how the chart will look like. I think its clutter free compared to the above column chart.

Here the category is the years and the month names are the sub-categories to it which are shown as axis-labels.

Do you like this chart? Then read on to understand how to plot this.

The important part of this workaround is re-structuring the data. For that, you can use formulas or manual method.

In order to plot the above column chart, the data should look like as below. Then only we can show month and year in x-axis that in a clutter-free manner.

Split Month and Year to Use as Categories and Sub-categories in Chart

I am using a few basic formulas to format the data. See the modified data in column D, E and F. You can use formulas to format the data as below.

First enter the labels in the header row D1:F1. Then use the below formulas.

Cell D2:

=to_text(year(A2))

Cell D3:

=if(year(A3)<>year(A2),to_text(year(A3)),)

You should copy this formula to the cells down. But that is not applicable to the following formulas.

Cell E2:

=ArrayFormula(text(A2:A25,"mmm"))

Cell F2:

={B2:B25}

Chart Editor Settings to Display Month and Year in X-axis

Here are the chart settings that I have followed.

Chart typeColumnStackingNoneData rangeD1:F25X-AXISD1:D25 (by default you will see the field label)LABELE1:E25 (* refer the screenshot below)SERIESF1:F25 (by default you will see the field label)

If you face difficulties to follow the above table to set the axis, see the axis settings again.

Thats all. See you again with another Google Sheets tutorial. Enjoy!