I was recently working in a Google Docs spreadsheet (I guess they call it Google Sheets now), and had trouble adding a trendline to some time-series data. I didn’t want to have to go scare up a real spreadsheet app, since the data set was still small and I wasn’t doing anything very complex with it. But I wanted my trendline, darnit! I’ve got yet another startup brewing, and I’ll be living in spreadsheets for a while, eating finance and breathing analytics for the next N months. I needed to find a way to make it work. Searching the interwebs, I came across a number of other users expressing frustration at an apparently inconsistent, or at least non-obvious, user experience of the trendline function. So I started poking at it to figure it out, and I came up with a hack that made things work for me.

The trouble comes in when, with various combinations of options for the chart, and the formatting of the underlying data, the Trendline option in the chart’s Advanced Edit does not appear when it should. There is no user feedback to explain why it doesn’t show up, leading to the kinds of frustrated requests for help you’ll find if you search for it. The docs do say “You can add trendlines to scatter plot, line, column, and bar charts.” – and you will see the option disappear if you have a different kind of chart. But for some of these charts where it should work, it does not. If you select a column chart, the option can disappear. Or, if you format your time data as “Date Time” instead of date, it also disappears. Not cool! I’ll show you what I mean.

Let’s start with a basic time-series scatter plot:

A Basic Time-Series Scatter Plot

According to the Google Sheets documentation on the trendline feature, we can add one easily under the Advanced Edit section of the chart. And you can see below, the trendline option is available as it should be:

Trendline Option Is Available

When you change the column formatting to Date Time instead of Auto or Date, the option disappears!

Formatted as Date Time

And it’s gone.

No Trendline for Date Time

So here’s the hack:

1. Set the time column format to Date
2. Add the trendline under advanced edit, and set the options as you like
3. Change the time column format back to Date Time
4. The trendline stays put!
5. …
6. PROFIT!

So here I added the trendline after changing the date column to “Date” formatting. After twiddling the line to be a 5th-order polynomial, we have this nice result, verifying that Google’s directions work. This time, at least!

Add Trendline with Date format

And after changing it back to “Date Time”, the trendline stays! I even twiddled the time portion of the value, moving the hours around. The data points in the plot shift as appropriate, and the trendline updates correctly. This leads me to believe this is just a UX glitch in Google Sheets.

Trendline for Date Time in Google Sheets

So, this hack works for the scatter plot, but I have seen it not work for others (column, if I recall correctly). I’ve seen other users comment that the formatting selection made a difference for them, for other chart types and other than time-series data, so you’ll have to play with it. I didn’t enumerate all permutations of Google Sheets, charts, and trendlines!

Good luck, and have fun spreadsheeting =)