Let me address a common problem that many of you may face while trying to enable the horizontal axis gridlines, i.e. vertical gridlines, in charts in Google Sheets.

The issue is that you may not see the option inside the chart editor panel to add/enable the horizontal axis gridlines.

Even if some of you could enable it, it may cause irregular data labels on the X-axis. In this post, I’m trying to address these two problems.

Google Sheets chart editor panel has an option to enable or disable, I mean to adjust the horizontal as well as vertical axis gridlines.

If your chart misses the vertical gridlines, that means, you must set the major gridline count that under Chart Editor > Customize > Gridlines > Horizontal Axis from “None” to “Auto” or any numbers from 0 to 10.

Sometimes you may not see the option “Horizontal Axis” to adjust values as above. I’ll tell you the reason why it’s not available so that you can sort out the issue.

Why Horizontal Axis Gridlines are Missing in My Line, Column, or Scatter Chart in Google Sheets?

There are mainly three reasons. Here are the three most prominent reasons. I’ve tested this only with Column, Line, and Scatter charts.

1. The X-axis (the concerned column in your data) contains text strings, not numerical values (Number, Date, DateTime, or Time). If you have numerical values, then check it’s not formatted as “Plain Text” in the menu Format > Number.
2. An option to change numeric X-axis values to text (labels) automatically got enabled in the chart editor panel.
3. You have enabled the “Aggregation” option in the chart editor panel.

To get horizontal axis gridlines or you can say to add/enable vertical gridlines to a chart in Google Sheets, the X-axis values must be numbers, dates, timestamps, or time values.

If your data satisfies this, then you have no problem to get the option. Here is one example.

Sample Data # 1 (A1:C13):

If you plot a chart using this data you won’t be able to enable the horizontal axis gridlines in your chart editor panel as the X-axis values (column A) are text strings.

So in column A in your sheet, enter the month as Jan-20 in cell A2, Feb-20 in cell A3… which will be treated/converted as dates.

If not, enter 01/01/2020 in cell A2, 01/02/2020 in cell A3… (mine is DD/MM/YYYY format), and format that dates to month from the menu Format > Number > More formats > Custom Number Format.

Type mmm in the filed there.

Plot a Line Chart and Display Vertical and Horizontal Gridlines

I hope you have entered the data in A1:A13 as per the instructions above. If so, please follow the below steps to plot the line chart.

1. Click Insert > Chart.
2. The Setups Under the Chart Editor > Setup:
   • Chart Type: Line Chart.
   • Aggregate: Disable.
   • Switch Rows/Columns: Disable.
   • Use Row 1 as Headers: Enable.
   • Use Column A as Labels: Enable.
3. The Setup Under the Customize Tab:
   • Vertical Axis > Treat Labels as Text: Disable.

If you follow the above settings, then you would get both vertical and horizontal axis gridlines on your line chart as shown below.

If you check the data labels on the X-axis, you can see that it doesn’t contain all the labels (months). How to solve it?

How to Get All the Data Labels on the X-Axis

I normally disable vertical gridlines (horizontal axis gridlines) by enabling the “Treat Labels as Text” option (please see point # 3 above).

This will help me to get all the data labels on the X-axis but in the cost of the horizontal axis gridlines.

I want to keep the horizontal axis gridlines and also show all the data labels. How to do that?

Click “Customize” in the chart editor and click Gridlines > Horizontal Axis.

Then change “Major Gridline Count” from “Auto” to 10. This way you can show all the labels on the X-axis on a Google Sheets chart.

You have learned how to enable vertical gridlines in a line chart in Google Sheets. Now let’s see what to do when you want to aggregate the data for the chart.

Query or Pivot Table Instead of Chart Aggregation (Workaround)

As I have mentioned at the beginning of this post, you can’t enable horizontal axis gridlines, if you have selected the “Aggregate” in the “Setup” tab of the chart editor.

In case you want to aggregate the data and also want the vertical gridlines enabled, either use the Data menu Pivot Table to aggregate the data or use a Query formula. Then use that new range/summary for creating the chart.

Here is that example.

Sample Data # 2 (A1:C13):

The above data is in “Sheet1”. Use the below formula in cell A1 in “Sheet2”.

=query(
     Sheet1!A:C,
     "Select A, sum(B),sum(C)
     where A is not null
     group by A
     label A 'Date', sum(B) 'Zone 1', sum(C) 'Zone 2'"
     ,0
)

Alternatively you can use the Pivot Table to aggregate the data as per the below settings.

1. Select A1:C.
2. Click Data > Pivot Table.
3. Enable “New Sheet” and click “Create”.
4. Then please follow the pivot table settings as per the image below.

In the pivot editor, under “Filters” click the drop-down and unselect “(Blanks)”.

The chart based on this output will have the horizontal axis gridlines unless the “Treat Labels as Text” is enabled in the chart editor.

That’s all. Enjoy!