One chart - trend line for each year in it

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
634
Office Version
  1. 365
Platform
  1. Windows
I have charts that show a monthly value going back 4 years. I want to show a trend line but I want it to show me the trend for each individual year in the chart. Can that be done. Over the 4 years the trend is down but some years were up and others were down.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

sr12345

Active Member
Joined
Feb 20, 2009
Messages
317
Can you add another column to the data source, where you calculate the trend by year in the data set, and then simply plot the resulting line?
 

sr12345

Active Member
Joined
Feb 20, 2009
Messages
317
Or, you could try this. Let's assume that you data is in rows, with column A showing the month (and possibly year), and column B has the data you are displaying in a column graph.

First, go through your data and segregate the data by year. So, year 1 would be in column B, year 2 would be in column C, year 3 in column D, and year 4 in column E.

Set up the graph to take data from columns A through E.

Add a trend line to each data series.

Adjust the overlap and column width of each series of columns, so that you have 100% overlap (meaning that the bar will appear in the same spot for each month-wide bin), then fatten the columns up as desired.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,613
Messages
5,832,702
Members
430,155
Latest member
spacedad41

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top