I have a pivot table that shows A/R by the week it was calculated and the age of the claim. The column labels are the Week label (Current, Week 2, Week 3) and then the age of the claim 0-30 Days, 31-60 Days, etc. The row label is the insurance company who is paying the claim. I am doing grand totals on the columns, but I need to see the column grand total as a percent of the total grand total. For example, I can see that my "Current" 0-30 grand total is $345 and my "Current" 31-60 grand total is $35. But I need to know what % the 345 and 35 are of all of the "Current" grand totals. I can see my "Week 2" 0-30 grand total is $1083, and the 31-60 grand total is $75, but I need to know what % the 1083 and 75 are of all of the "Week 2" column grand totals. I know how to show an individual cell as a % of the row total, but can't seem to figure out how to do that for the grand totals row.


and welcome to Stack! It's very difficult to understand from your input exact details, so for future questions try to provide workbook sample / screen or at list part of meaningful data typed here. However, the solution seems to be pretty simple (assuming I understood the input correctly): right click on the field you want to change, select Value Field Settings... and then select the option you need. For your needs I thing % of total is the choice:

pivot field settings

Hope that was helpful. In case you're happy with the solution - do not forget to tick the answer and therefore mark the thread solved. Good luck!

  • Thanks Peter L. Since I'm new I can't post an image, but here's a link to a screenshot that shows exactly what I'm trying to do. I was sure I didn't explain it very well. content.screencast.com/users/brianburkett/folders/Jing/media/… – Brian Burkett Jan 28 '13 at 14:01
  • @BrianBurkett Thanks for the screen! So, try to apply my suggestions to the field(s) - for me it seems that it will do the job. – Peter L. Jan 28 '13 at 14:27
  • OK. I applied your suggestion and ran into a couple of issues. It does show the %, but converts the whole column to %. That was fine because I could just add the same column to the pivot twice to show the value plus the %. However, it calculates the % over the entire pivot, not the groupings separately. So where I need it to just calculate over "Current" and "Week 2" separately. Any ideas? – Brian Burkett Jan 28 '13 at 14:33
  • @BrianBurkett If you could share a sample file - that would be extremely helpful, since it's very difficult to produce exactly the same table that you have for tryouts. – Peter L. Jan 28 '13 at 14:34
  • OK. I've posted a file with the pertinent columns intact. webzax.com/test.xlsx Thanks for your help! – Brian Burkett Jan 28 '13 at 14:44

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.