1005 Pivot Table - Value Field Settings - "Summarize Values By" - Sum, Average, Count, Max etc.

Advanced Excel Crash Course Section 10: Pivot Table
5 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

Hi there, I assume that you would have watched the prerequisites and the intro part of the pivot table, because now we are going to step on the actuator and start making the pivot table reports that we intend to. So as of now, I am going to select the data lying in front of us Ctrl A, ensuring all the headings are proper with no columns with a blank heading. So once having done so I go to Insert Tab and pick up the pivot table. The general problem that it provides me is do you want to use the same data which you have chosen? Of course, yes. And the new worksheet is the one where the pivot table grid area is going to be generated.

Once that is generated, as we have seen earlier, I right click on the grid area, go to pivot table options, and then go to display. Choose the classic pivot table layout options on OK. Once having done so. Then let's start making our pivot table report. The starting point is going to be where I'm going to present the report in a manner which shows me one of the lists of divisions and what salary Are you paying to Each division, so total total sorry that goes inside every division, that is what I'll be wanting. Not only that, I also want how many people are working every division and finally, average salary per employee for every division. So let's start, I go to division and I pick up the fee.

Now practically speaking, you can make a tick mark on it directly takes to the roof ease, or else you can drive this using nouns and put it in the roof ease I find that very convenient. And once having done so you get is the same thing as what he got earlier by checking this checkbox on. Also, this division, you can also throw inside this third box called rolls it automatically feeds that in the row field. So practically, you have three options, three ways three approaches with which you're going to start making the pivot table report. Once having this division in front of my screen, next thing I want is salary total. Now salary has to be a number of course and that is Want a mathematical calculation of in terms of sum?

So let me go to salary, drag this up and put there in the value field option. This is the area where we had referred as mean, action area are all mathematical calculations happen here. So I just threw that there. Uh huh. Yes, I got a data. Let me optimize the window.

I got a data which spans over millions. And it's telling me $95 million happens to be the total salary outflow for this organization. And this is the breakup. Now, eventually, I'll be doing the formatting part of it. But as of now, let me also go ahead and apply a technique which gives me a count of people. Now the trick is, I use the same salary fee and drag it and put it on top of these set of numbers.

Notice, I am not throwing that on top of the header total. But on top of the numbers category area, this is the main action area. If I just leave the mouse click it's good. to pop up with another column which says sum of salary. Now, what I'm going to do is, I am going to focus on this heading which says sum of salary. Why?

Because if you double click on this header right now, you get something called value field settings. When do I double click wherever you get to see a terminology of mathematics, so I double click there. Now when I go to value field settings, I want to watch convenience cited. Well, if I start reading about it, I will see. So let me get straight to the point, this particular tab which allows you to change it to count as you do so you get the count of salary for when people working for division ad. If I double click again, on the heading, you still get the same thing value field settings, which you can change it to average weight.

My question was get all three at one go. So let me bring back salary once again on top of the main action area, release the button of the mouse and then go to the third heading. Double click. If you're not preferring double click, you may also right click and then go to Salou field settings. So this allows you to manipulate the calculation to, let's say, count. And once I do so, I get in sequence, the sum of salary, average salary, and the count of salary.

It's very good technique. If you want to have a summary sheet for any data you're working with. For example, if you have a sales data, you get to see how many client names are there and how many sales order Have they given to you and what is the amount of sales that has been affected to them. If you have purchase data, you can find all the vendor names you can find out how many purchase order you have issued, and what the total amount of purchases that have pleased before them. So once having done so I just take some extra seconds to ensure the formatting is picture perfect. So I go to whom I apply the comma style for the million separator and I reduce the decimal and there you go.

This becomes your pivot table. Report. So this is a starting point. We'll do more stuff as we progress

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.