I was trying to find a way to get a pivot table to include the grand total column for a call report I am doing. Seems it can't be done in a clean, easy way. Very frustrating! I would think adding the grand total column would be a no-brainer. Oh well. Through some experimentation I believe I found a workaround.
Basically what I did was make a static table. For my purposes, it has headers of 5:00 AM, 5:30 AM, 6:00 AM, etc. (This report shows how many calls were received at a certain time).
In the cell below each time header I put a Countif formula. It references another sheet in the workbook that has a full list of the calls. Here is the formula I use:
It's basically saying "look for all the calls that have a code of 1 (a queue we have) and then subtract the number of calls greater than or equal to this time from all the calls greater than or equal to this time".
The 2nd criteria in that countif is what I just came up with though. It basically also tells the countif formula to only count calls that are equal to the date entered into a certain cell. That cell is a single cell of a pivot table I created. Basically I manually entered a bunch of dates (1/1/2012, 1/2/2012, etc.) in column A, then I inserted a pivot table a few cells below the date list in the same column based on those date entries. Then I inserted a slicer. From that slicer you can choose your date and the pivot table's cell will change dates based on what you pick. The cell in the pivot table is then, in turn, what's referenced in the static table's formulas, and then what the chart is based upon.
This is basically a crude way of making a "manual" pivot table, but it works surprisingly well. I believe it could be adapted to the "grand total" rather easily. Just make a "grand total" field in your static table and make the formula contingent upon the date you choose with your slicer as outlined above. Then you can just hide your static table and the pivot table depending on how you want to present it.
Anyway, I hope this helps someone, because it took me hours to figure this out and I saw no solutions anywhere online. It's QUITE frustrating. Hopefully Microsoft will include this functionality in their next version, as it's a rather glaring omission in my opinion.
***Also: if anyone has any suggestions on how to streamline this (particularly the formula), by all means, let me know. Thanks!
Basically what I did was make a static table. For my purposes, it has headers of 5:00 AM, 5:30 AM, 6:00 AM, etc. (This report shows how many calls were received at a certain time).
In the cell below each time header I put a Countif formula. It references another sheet in the workbook that has a full list of the calls. Here is the formula I use:
It's basically saying "look for all the calls that have a code of 1 (a queue we have) and then subtract the number of calls greater than or equal to this time from all the calls greater than or equal to this time".
The 2nd criteria in that countif is what I just came up with though. It basically also tells the countif formula to only count calls that are equal to the date entered into a certain cell. That cell is a single cell of a pivot table I created. Basically I manually entered a bunch of dates (1/1/2012, 1/2/2012, etc.) in column A, then I inserted a pivot table a few cells below the date list in the same column based on those date entries. Then I inserted a slicer. From that slicer you can choose your date and the pivot table's cell will change dates based on what you pick. The cell in the pivot table is then, in turn, what's referenced in the static table's formulas, and then what the chart is based upon.
This is basically a crude way of making a "manual" pivot table, but it works surprisingly well. I believe it could be adapted to the "grand total" rather easily. Just make a "grand total" field in your static table and make the formula contingent upon the date you choose with your slicer as outlined above. Then you can just hide your static table and the pivot table depending on how you want to present it.
Anyway, I hope this helps someone, because it took me hours to figure this out and I saw no solutions anywhere online. It's QUITE frustrating. Hopefully Microsoft will include this functionality in their next version, as it's a rather glaring omission in my opinion.
***Also: if anyone has any suggestions on how to streamline this (particularly the formula), by all means, let me know. Thanks!
