Trendline in pivot table stacked bar chart

Mar 28, 2013
Reaction score
In Excel 2010 I have some bug data which looks something like the following, pasted directly from our bug tracking software:

Bug no.-Priority ----Date Raised Week no. (added myself, see below)

001 ------ Low ----- 07.02.2013-------6
002 ------ High ----- 11.02.2013-------7
003 ------ Medium --12.02.2013-------7


I've added a WEEKNUM column so that each 'date raised' entry will yield a week number.

I've successfully created a pivot table that shows the number of bugs raised per week, split out by priority. It has the week numbers along the x axis and no. of bugs along the y. Each week's column is a stack split by priority.

What I'd like to add now is a trendline for the total bugs each week.
Of course the stack means I can't, it treats each bug priority as a series.
Googling a solution the advice is to create a 'total' column in the data so you can add it as a series and trend that. However, my total of the different categories of bugs, and overall totals per week, was extrapolated by the pivot table, it isn't a column of numbers that I can somehow get the pivot table to pick up from the source data given the above source data format.

The only way I could see I could do it would be to have cells in a new column on the end of the source data I've pasted that contained some formula that said 'count as 1 all the cells in the Week No. column that contain a specific value and total it here', for each week.

If that's the case, what would the formula look like, or is there another way to do this?

Last edited:


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question