1006 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Grand Total

2 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.60
You save:  A$55.89
List Price:  ৳11,872.36
You save:  ৳3,392.34
List Price:  CA$187.37
You save:  CA$53.54
CHF 91.26
List Price:  CHF 127.77
You save:  CHF 36.51
List Price:  kr884.75
You save:  kr252.80
List Price:  €118.74
You save:  €33.93
List Price:  £107.27
You save:  £30.65
List Price:  HK$1,084.99
You save:  HK$310.02
List Price:  ₹10,502.85
You save:  ₹3,001.03
List Price:  RM586.20
You save:  RM167.50
List Price:  ₦52,986.21
You save:  ₦15,140
List Price:  kr1,265.84
You save:  kr361.69
List Price:  NZ$211.96
You save:  NZ$60.56
List Price:  ₱6,870.27
You save:  ₱1,963.07
List Price:  ₨23,504.32
You save:  ₨6,716
List Price:  S$192.17
You save:  S$54.91
List Price:  ฿4,370.86
You save:  ฿1,248.90
List Price:  ₺1,021.10
You save:  ₺291.76
List Price:  R2,469.09
You save:  R705.50
Already have an account? Log In


Hi there. In the series of pivot table videos, what I'm first doing is making you aware of all the important tips and tricks. Eventually, as we progress, and we have been made aware of all the important tips and tricks, we will take bigger data. Right now I'm continuing with a previous section where we had created a list of all the division names the sum of salary, average salary, and count of salary. Now, you might also wonder that besides the absolute numbers, you may also wish to get the percentage value. That means if out of 417 employees 41 belong to the division ad, which is approximately 10% that I should have the 10% value here.

So let me do one thing. Let me go back one step. Let me take away the salary option away from this checkbox. And this pivot table gets back to one level down. Now, against the division names, I'm going to put salary in the main action area not once but twice. Now, with salary amount, I want the public percentage.

Okay, so how do I proceed? Well, I go to the second heading which says sum of salary, the one which is highlighting right now I double click on that, my intention is to go to value field settings. So I go to value field settings. And then there are two tabs side by side, I go to the second tab. and here if I start looking at the various options as show value as as what, and as I click on the drop down menu, you notice that it says percentage of grand total percentage of column and row total. In fact, the terminologies might be slightly different in the older version of Excel in 2007 version, these are v 2010, and 13.

But never mind. I'll take you through all the various options eventually, as of now, I'm interested in percentage of grand total was grand total, this is grand total for this particular column. So what is it going to do? It is going to do 8 million divided by $95.7 million and that is how the percentage gets derived. If you Want to sort this entire list based on the percentage? Yes, you can you can see right click and sort sort largest to smallest.

So what we saw was a very, very brief overview how a particular number in the value field gets converted to percentage, which can be looked at side by side besides the absolute number. We'll see more variations as we go ahead.

Sign Up