Add Trendline to stacked column graph

[Carrie]

I have a stacked column graph but I cannot add a trend line to it. Does any one know how to do this?

 

[Elliot Penna]

Carrie,

If you do not already have one, make a column that sums your stacked data. You can add this series to your existing stacked column graph on a secondary axis, make your trendline, and then format the summary data invisible (no border, no area).

How:

Right click on your graph.
Select Series tab.
Click on Add button
For Values select the column of summed data
OK

Now the summed data series sits stacked on top of your other stacked data.

Right click this added series on your graph.
Format data series
Select Axis tab
Click Secondary axis radio button
OK

Now the summed data series sits on top of, and is hiding, your stacked data.

Right click on this series (again).
Select Add trend line
Chose the type you want
OK

Your stacked data is still hidden on the graph, but now you have a trendline for the summed data.

Right click on series (yet again!).
Select Format Data Series
Select Patterns tab
Click None for both Border and Area radio buttons

If you have a legend you will have to select the summed data series and delete it, and you should be good to go.

 

[carisajo]

Hello, I have excel 2007 and a stacked column chart with two colors of data. In my layout chart tab, the add trendline is grayed out? Any suggestions?
Thanks!

 

[wigi]

Hello

Trendlines are not available for that type of chart. For a line chart, for instance, a trendline will be available.

 

[Jon Peltier]

What you need to do is compute your own trendlines.

For each series compute slope and intercept. The first trendline is calculated based on slope1 and intercept1, the second is calculated based on slope1+slope2 and intercept1+intercept2, etc. Compute points for these trendlines right in the worksheet, then add this data to the chart as new series, and convert them to line type series.

Note that stacked column charts are harder to interpret than clustered column charts, and stacking trendlines in this way will only increase the difficulty.

 

[stevebrittsplace]

This works right up until this instruction;

Right click on this series (again).
Select Add trend line
Chose the type you want
OK

In Excel 2010 the "ADD TREND LINE" function is grayed out... Now what?

Steve

Carrie,

If you do not already have one, make a column that sums your stacked data. You can add this series to your existing stacked column graph on a secondary axis, make your trendline, and then format the summary data invisible (no border, no area).

How:

Right click on your graph.
Select Series tab.
Click on Add button
For Values select the column of summed data
OK

Now the summed data series sits stacked on top of your other stacked data.

Right click this added series on your graph.
Format data series
Select Axis tab
Click Secondary axis radio button
OK

Now the summed data series sits on top of, and is hiding, your stacked data.

Right click on this series (again).
Select Add trend line
Chose the type you want
OK

Your stacked data is still hidden on the graph, but now you have a trendline for the summed data.

Right click on series (yet again!).
Select Format Data Series
Select Patterns tab
Click None for both Border and Area radio buttons

If you have a legend you will have to select the summed data series and delete it, and you should be good to go.

 

[Jon Peltier]

Read my post right before yours. Calculate and plot your own trendlines.

 

[BrianMH]

This works right up until this instruction;

Right click on this series (again).
Select Add trend line
Chose the type you want
OK

In Excel 2010 the "ADD TREND LINE" function is grayed out... Now what?

Steve

I know this post is old but anyone looking now may find this helpful.

After you've added the total column right click this and change the series to a standard column instead of a stacked column. Then click on that column series individually and you can now add a trend line through the normal tool.

 

[Jon Peltier]

Or change it to a line instead of a stacked column. Otherwise you need to either turn all stacked columns to regular columns, or you need to put the unstacked one onto the secondary axis, adding unneeded complexity.