Add totals line to pivot chart in Excel 2010

4
votes

I have a very simple pivot table that basically sums the hours of all the data records and pivots them with month as a row label and billable/non-billable as a column label. The pivot table actually shows a totals column which is the sum of billable hours and non-billable hours by month.

I created a pivot chart to represent the data, and it includes lines for billable hours and non-billable hours, but for some reason I can't get a third line to show the total hours. The numbers exist in my pivot table, but I'm just not sure how to get those data points to graph on the chart. Based on my research, it seems like a calculated field might do the trick, but that's frustrating--I hate to add a calculated field to calculate a value that's already displayed in the pivot table (though I'd do that if I had to--just couldn't get that to work either). Any suggestions?

2
excelexcel-2010pivot-table
In your source data, do you have one column for billable and one column for non-billable? – nutsch
No--hours is in one column, and billable/non-billable is indicated in another column. The pivot takes care of breaking it out and adding the total column. – rryanp
similiar issue here: did you ever find a solution? – Raoul

2 Answers

1
votes

Unless you need the flexibility of a PivotChart, bring your PivotTable values down into regular cells below the table using simple cell references, then create a regular chart using the values of the new range.

For example, if pivot table results are in cells, say, A1-E5, including row and column label and the Grand Total Line, then pull the results down to cells F7-E10. That is, F7 formula will be "=A1", etc. Then, insert the new chart using range F7-E10.

0
votes

You can add a calculated item, to the field to the column label, as shown in Excel Pivot Table Calculated Item

Thereafter, you will see a third line in your chart, BUT at expense of observe that in pivot table, the grand total will show the double the proper amount, as now it consider the total "calculated item" together with other itens!