Add Trendline to stacked column graph


Image by digital_monkey via Flickr

Adding a trendline to a stacked column graph is not that easy. But there is a way to do it.
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

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

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

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

Right click on series
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.

Tagged with: ,
Posted in Uncategorized
6 comments on “Add Trendline to stacked column graph
  1. Annmarie says:

    Spot on with this write-up, I really believe this site needs a great deal more attention.
    I’ll probably be returning to read through more, thanks for the info!

  2. I just couldn’t leave your web site before suggesting that I actually enjoyed the standard info an individual provide for your guests? Is going to be back ceaselessly to inspect new posts

  3. Matt says:

    Thankyou! This technique worked excellent for creating Agile burndown charts!

  4. Erik Toft says:

    Thank your for this. Is still valid with Excel 2016 with one small addition: The chart type for the additional series needs to be changed from stacked to clustered column. This is done by ‘Design’ ,and ‘Change Chart Type’.

  5. Graham Smith says:

    thanks this worked well the only hicup I had was that I didn’t realize that I need to change the series chart type to a cluster bar graph

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: