1007 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Column/Row Total

Advanced Excel Crash Course Section 10: Pivot Table
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hello there, I assume you would have spent some time in practicing the earlier practice session exercises. Let me take you a few notches above in terms of the complexity of the features of pivot table. Now, inside value fee show value as tab, you would have noticed in the earlier videos we talked about grand total percentage of grand total. Well, this time with an example I'm going to show you examples how percentage of column or how percentage of through tutorial can benefit us if we have the requisite data. So right now, what I'm gonna do is I'm going to keep the slate slate clean and return back to the normal grid area of the pivot table. I keep the division in the roofie.

Once having done so, let me keep the rating in the column fees area. So basically this report is going to tell me under which division how many people got reading 1234 and five, one being the good rating, five being the bad rating. Now I need name in the main action area. Notice I'm not simply taking this name on because by default, since name is a text fee, it has been thrown inside roofie, which then gives you the list of all the names. I won't name not in the roofie. But in the main action area where I'm going to throw this particular fee into.

Once I have the numbers before me, this automatically gives me count of name. single reason being since name is a text, nothing can be averaged Nothing can be some only one mathematics can be applied. And that happens to be what count? Yes. Now, from here, the story begins. What if I asked you that I want a calculation in the pivot table, which says seven by 41.

So typically, we would like to select the cells seven divided by 41, correct, pressing Enter. So this shows me approximately 17%. Or at times, I may want a calculation with cells seven divided by the grand total, the total employees in the organization, Enter. And that's how much revenue 1.6% and at times, I may ask you equal to seven divided by 63, which is at the bottom of this column. And so that's approximately 11%. So every one of these calculations have different interpretations and their approaches who generate them is also different.

So we'll see how those will be generated, and what interpretation does it bring before us. So what I'm gonna do is I'm going to choose this yellow cell, the one which I've colored right now can't count off name. And within which I'm going to double click upon, I go to the second tab, which says show value as an A value field settings. And here, let me start with percentage of grand total, okay. So as you can see, this divided by two to 417 happens to be 1.68 percentage. So this calculation is an overall calculation and is relevant when all the variables are of same nature or same scale.

Currently, the nature of the ratings are different one is a good rating five is a bad rating. So either Perhaps me want to calculate this number divided by the relevant total of that one column. So I double click on the header, I go to show value as and then I choose percentage of column. I press OK. This calculation seven divided by 63. That is one by nine hamstring 11.7%.

So what story does it hold for us? Well, this tells you that out of 63 people, one third of them belong to already who have received rating one within rating one pool 27% belongs to which division HFD. So this story would have not been exhibited had used the earlier example of percentage of gratitude. In fact, I'll show you one more variation, where I'm going to double click on the header and get back to the percentage drop down and choose percentage of row total. Once I press OK, this time, the each division is being shown as a separate business unit SBU Within the same business unit, if I have to bifurcate the entire group of people into five categories rating one till five, I get to see the percentage. This will be even more clear if I reduce the decimal so as to make this look more easy on the eyes.

There you go. So, if I read the line number one which pertains to rating A B, it tells me with an ad 17% have got rating 120 percent have received rating to 32% have received them average rating rating three and remaining 32% have got rating four and five in total. So, this is giving you an overall view of division by division. So, as we proceed, the more data we take up we'll see how different interpretations hold different answers and clues to the question we might ask.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.