I would like to know how to put in percentage trend lines for individual levels in a stacked bar chart using the data below. I want a usage trend line for the total, as well as certain specific levels.

I appreciate any tips you can give me and am sorry I cant describe it better in words, but believe the images will clarify.

                    Data                                                                            Example
 Data  Example

(click above for larger image)

  • Please describe the relationship between count and utilization. In your data table, the total utilization is 80% and your Level 4 utilization is also 80%, but on your chart the values are dramatically different (Level 4 looks about 25%). Also, since your comparing integers and percentages you'll want one of those values on a secondary axis. So, what values should match 0% and 100%? Is 84 (your max count) 100% or should it be something else? – dav Sep 20 '19 at 12:36

It's a little irregular to compute trendlines for stacked series; Excel doesn't even want to do it. But we're smarter than Excel, so here's how to do it.

I'll do it for the top data set; the protocol is the same for the other set also.

First, I added some cumulative calculations. Cum1 is just series 1. Cum2 is the sum of series 1 and 2. Cum3 is the sum of series 1, 2, and 3. So I'm calculating the height of the first stacked bar (Cum1), the second stacked bar (Cum2), etc.

data range: individual and cumulative values

I built a stacked column chart from the first six series in the first chart.

In chart 2 I've added the Cum1 through Cum6 data, which Excel adds as more stacked columns, and I converted them to simple line and marker series.

In chart 3 I deleted the legend, because it's just going to get larger and more confusing. I also selected the lowest line chart series (Cum1), clicked the plus icon next to the chart, and checked Trendline. You can see the dotted trendline though it's mostly obscured by the data series itself (the lines and markers).

In chart 4 I hid the Cum1 series, by formatting it to have no line and no marker.

In chart 5 I added trendlines for the other series (Cum2 through Cum6).

In chart 6 I hid the other series (Cum2 through Cum6).

I would probably format the trendlines with solid, not dotted, lines. I would also try to do something with the colors, because series 5 and 6 are not distinguishable. But you get the idea.

charts: building the trendlines

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.