Google Sheets Trend Line – How to Include Comparison Trend Line

google sheetsgoogle-sheets-charts-trendlines

I am using google sheets to record sales data and have a separate worksheet for each calendar year.

For each calendar year I include a couple of charts which illustrate things like track dollar value of sales per week and I've included a trendline.

Now we've ticked over to a new calendar year, I'd like to include the trendline from last year's dollar value chart into this year's chart, so I can see how the trends compare.

I don't really want to include the week by week data in the chart (it will look messy & I don't need that level of detail) – is there a way I can just include the trend line?

Best Answer

I have 'solved' this by pulling in the previous year's data, including the trend line in the series & then setting the colour for the series to none. That way the trendline appears but the individual data points don't.

I suspect this is the right way to do this but I'd be interested to hear of any other approaches.

Related Solutions

Google Sheets – How to Add an Average Line to a Scatter Graph

First, calculate the linear regression factors: y=ax+b with the following formula:

=LINEST(B2:B21;A2:A21)

Then add another column next to the y-axis and name it calculated y-axis. Build the following formula to calc. the y-axis and copy that for all x-values.:

=$D$3*A2+$E$3

Next is a piece of cake. Add the calculated y-axis to the existing data range or add it seperately.

UPDATE: I've added the calculated mean value as well, in order to be absolutely sure to have answered your question properly, see plot: Plot with mean and linear regression

See example file for more details: plot regression example

Google-sheets – 2-axis line chart in Google Sheets

What you need to do is a few steps:

Insert to Chart then add your Data Series, as you normally would with a Single Axis chart.
(Optional) Change chart type to Line. You can change it to anything else later. Line seems to be a safe bet for the later options to appear. Not sure if this step is by Google design...
Right-click on the chart and select Series.
Change Apply Series to whichever Data Series you want to move to the Second Axis.
Change Axis to Right Axis.
Now you can go back to Chart type to make it a scatter, or bar, or anything else - the program will remember the Axis assigned to the Data Series, regardless of whatever options may be available.

tldr; the Google Support answer is broken for a few chart types. Do your Axis assignments with Line charts, and change the Chart Type after.

Best Answer

Related Solutions

Google Sheets – How to Add an Average Line to a Scatter Graph

Google-sheets – 2-axis line chart in Google Sheets

Related Topic