Excel – Add a trendline to a stacked bar chart

For Excel to create a Stacked Column Chart, you'll need to separate the items you want "stacked" into separate series so Excel will know what to do with them. Here's a quick way to accomplish that:

1. Convert your data into an Excel Table. Name Column A Date & Column B Value.
2. Add two helper columns to your Table (not completely necessary, but they'll make it easy):
   • Month =Month(A1)
   • ValueID =IF([@Month]=A1,B1+1,1) Basically counts each month's values. If you have another way to identify the values, you can use it instead of this.
3. Insert a Pivot Table based upon your chart, with the following values:
   • Row Labels = Month
   • Column Labels = ValueID
   • Values = Values (sum)
4. Create a Stacked Column Pivot Chart from your Pivot Table.

Here's what it looks like with Excel defaults:

Assuming your data is set up correctly, you should be able to add accomplish this by:

1. Add Data Labels
2. Format Data Labels
   • Check Label Contains to Series Name
   • Uncheck Label Contains Value
   • Check Label Position to Inside End
3. Repeat for each series
4. Delete the legend (which is almost always a good practice).

But it sounds like your chart is trying to do too much-in regards to how the reader will be able to comprehend it. Have you considered alternates? For showing trends over time, often Line Charts are better suited than Column Charts. You could also consider resorting the Stacking in your chart (e.g. stack by date, rather than series). You could also consider small multiples/panel charts, or having most series similarly formatted (e.g. gray scale), with areas of interest highlighted in color.