microsoft excelpivot table
In Excel, I have a PivotTable. I'd like to see all of the cells that went into individual calculation. For instance, if I would like to see all the individual values that made up B@ (Mark/Profit) in the PivotTable below, and have them displayed as a column, how would I do that?
| | Profit | Loss |
|------|--------|------|
| Mark | 5 | 10 |
| John | 7 | 8 |
Let's say this is our pivot table.
Now let's add our pivot chart - I've chosen a column chart for simplicity.
Copy your pivot table (cells A1:B10) and paste them elsewhere on the sheet - if you want the chart and the final table to be next to each other, lay them out accordingly now. I've pasted my second table into cell F1, and for clarity set it to be red.
Now in your original table, filter your row labels so that your pivot chart displays only the items you want.
Finally hide the original table (in this case I just hid columns A & B).
When you refresh the source data, both tables and the chart should all update. If for some reason they don't update, try using the Refresh All button on the ribbon.
My bad, not nearly as complicated as I was thinking
=SUMIF(A1:G1,"*total*",A2:G2)
Capitalization differences would need something like
=SUMIF(UPPER(A1:G1),"*TOTAL*",A2:G2)
Best Answer
To see all cells that went into the calculation, double-click the cell that holds the value.
A new sheet will open that shows the rows that were used.
Check documentation