Google-sheets – Blank Redundant Month

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-charts

I have a large list of days and quantities that I wanted to chart so we could see how many of something we sold on a specific day.

Give this goes back 600 days, there is a lot of data so I have been asked to have the x-axis only display the month + a year for a given chunk of the data, i.e. all the Jan – 2016 sales just have a single label saying "Jan- 2016" for the next 20-30 data points.

However, the data itself should not be aggregated. This is purely a cosmetic change, the shape of the chart will not itself change.

This is proving more difficult than I anticipated since the days aren't evenly spaced and I don't exactly want to brute force check every preceding cell to see if the next cell is a different month.

I just feel there is some basic config I have missed for this as I can see the Y-axis behaves that way.

Example with a single item

Best Answer

  • insert 2 more columns between A and B column
  • paste this formula into B2 cell: 
    =ARRAYFORMULA(IF(LEN(A2:A), TEXT(A2:A, "mmm - yyyy"), ))
     4
  • paste this formula into C2 cell and drag it down: 
    =IF(COUNTIF($B$2:B2, B2)>1, , B2)
     4
  • create your line chart from C and D columns

  • reverse horizontal axis order if needed:

    1

Related Topic