11

I have a chart with dates along the X axis, but the chart displays them quite close together, producing a rather ugly graph. How might I reduce the number of visible labels to increase the readability a little?

enter image description here

Edit: Sheet in question https://docs.google.com/spreadsheets/d/1P4sTn6YOp_747Gc5nCF34af1MN3AjhO0wRj85FjAqkA/edit?usp=sharing

2
  • 1
    Share link of the sheet with sample data?
    – CodeCamper
    Nov 17, 2019 at 20:21
  • Added to main post
    – Dr-Bracket
    Nov 17, 2019 at 21:21

4 Answers 4

4

Answer:

... -> Edit chart -> Customize -> Gridlines -> Horizontal Axis (in drop down) -> Major gridline count

Under the Major gridline count you can select or type however many dates you want to see on the horizontal label here, for auto mine shows up with a reasonable number of dates but if you wanted to see 20 dates or 10 dates you can type 20 or 10 here respectively.

Note: if you do not see Horizontal Axis under gridlines you need to go to

Customize -> Vertical axis

make sure Treat labels as text is unchecked.

in your specific example dd-mm-yyyy is not a recognized 2014-04-24 date format so use the following formula to fix that, paste in cell F1 for example :

={"DateFormat";arrayformula(if(B2:B="","",date(index(split(B2:B,"-"),,3),index(split(B2:B,"-"),,2),index(split(B2:B,"-"),,1))))}

(make sure to Format->Number->Date on this entire new column F)

Bonus: Depending on how you want to present the data you can also create a fake label column to display important dates you want in various ways.

For example you can put this formula in G1 in conjunction with the note above

={"NotedDates";arrayformula(if(E2:E="","",F2:F))}

which will create a list of dates you can use as a text label to only display important dates, however with the amount of points you have in your data you have to stretch the chart out way larger than the web page to be able to display that information because google sheets is randomly deciding which label to omit because of size constraints.

6
  • 1
    Actually - I take back my comment. The Horizontal Axis isn't displaying, and under Under Customize -> Vertical Axis I don't have a Treat labels as text: i.imgur.com/VjoouLv.png
    – Dr-Bracket
    Nov 17, 2019 at 21:28
  • 1
    @Dr-Bracket you do not have the checkbox because you are using a date format that is not recognized by google sheets or excel as a valid date format, will update my answer with a solution for this if you can't manually correct the date format of your data... as per my bonus note you can simply create an alternate date column and simply delete dates you don't want to see, for example to show maybe the first of each month only.
    – CodeCamper
    Nov 17, 2019 at 21:31
  • Ahh makes sense - thank you!
    – Dr-Bracket
    Nov 17, 2019 at 21:40
  • @Dr-Bracket see the new formula above which will fix your date formatting.
    – CodeCamper
    Nov 17, 2019 at 22:08
  • 1
    No such "Major gridline count" option in "Horizontal Axis"
    – Raptor
    Oct 25, 2021 at 3:10
2

Following the advice of CodeCamper's answer above, I spent some time playing around with settings like "treat labels as text" to no avail. My issue was that no matter what I did, "major gridline count" never showed up, and I could only see an option for ticks:

cropped screenshot of Google Sheets - Chart editor > Customize > Gridlines and ticks > Horizontal axis - no gridline options visible

Eventually I found a workaround for a related issue that also fixed my problem at this link: https://support.google.com/docs/thread/2577719/can-t-set-horizontal-min-max-on-chart-when-data-is-dates

Basically, the workaround is to format the data first as a generic number (highlight spreadsheet column, Format > Number > Automatic), which looks like it satisfies some condition for Google Sheets and then it will show the gridline options. Once you're done setting those up as desired, you can go back to the spreadsheet and reformat the dates as desired. I did this and my formatting options were persistent even after switching back to yyyy/mm/dd format.

0

After a lot of searching, this comment from glenviewjeff had the answer I was looking for - Leaving breadcrumbs for myself and hopefully it will help others. It's a fairly complicated process. I started with a more complex chart for historical reasons. I had to modify my chart to get it to work. I moved around the columns on the "setup" tab of the chart such that the x-axis was the right data, and there was only one column for the "series." I changed the chart to be a simple "column chart." I checked the "use column A as labels" box (column A are dates for the horizontal axis in my case.) and unchecked "treat labels as text."

The breadcrumbs definitely helped. TL;DR - change it to a column chart, and uncheck "treat labels as text.". Viola!

Thank you glenviewjeff!

0

Note: if you're working with a date and a time, this won't work. Simply format the datetime column as just a date and all of the options mentioned in the answers will work.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.