In Excel, you cannot take a stacked chart and add a trendline to it. But I wanted to. I had resorted to using Paint in the past, but this particular chart was going to be rather complex, and I had several of them to build, so I wanted to figure a "better way (TM)". Here's what I did in Excel 2010.
Select your data and create the stacked chart. That is the easy part. For a stacked chart, you would normally have groups with quantities such as this:
| Month | Widget 1 | Widget 2 | Widget 3 | Total |
| March | 26 | 6 | 13 | 45 |
| April | 21 | 9 | 11 | 41 |
| May | 25 | 7 | 12 | 44 |
Create the chart without using the totals column.
Right click on the "Totals" data and select "Format Data Series". On the "Series Options" tab click the "Secondary Axis" radio button under "Plot Series On". The "Totals" data will now be hiding the stacked data.
Right click again on the "Totals" data and "Change Series Type" to a regular column. Click the "Layouts" tab on the Excel Ribbon, click the "Totals" column, and now "Trendline" should be an option. Select the trendline you want to use, in my case a linear trendline.
Now you have the trendline, but your stacked data is hidden. Right click again on the "Totals" series and click on "Format Data Series". Change the "Fill" to "No Fill", change the "Border Color" to "No line", click "Close.
There you go!
Now to copy this to multiple charts without having to walk thru all of the steps. Begin by creating your stacked chart, but this time include the totals column. Right click on the first chart and "copy". Click on the new chart, and, on the Ribbon, under the "Home" Tab, click on the little arrow under "Paste" and select "Paste Special. On the dialog box, select the radio button for "Formats". All done!!
Credit to mrexcel.com and techrepublic.com for the tips that I put together to create this.
