Get a month of TabletWise Pro for free! Click here to redeem

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

5 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.17
You save:  د.إ146.91
List Price:  A$201.46
You save:  A$57.56
List Price:  ৳11,880.65
You save:  ৳3,394.71
List Price:  CA$190.27
You save:  CA$54.36
CHF 94.09
List Price:  CHF 131.73
You save:  CHF 37.64
List Price:  kr922.14
You save:  kr263.48
List Price:  €123.82
You save:  €35.37
List Price:  £110.81
You save:  £31.66
List Price:  HK$1,085.07
You save:  HK$310.04
List Price:  ₹10,524.04
You save:  ₹3,007.08
List Price:  RM597.26
You save:  RM170.66
List Price:  ₦54,219.52
You save:  ₦15,492.40
List Price:  kr1,320.98
You save:  kr377.45
List Price:  NZ$212.97
You save:  NZ$60.85
List Price:  ₱6,921.33
You save:  ₱1,977.66
List Price:  ₨23,247.81
You save:  ₨6,642.70
List Price:  S$194.74
You save:  S$55.64
List Price:  ฿4,384.27
You save:  ฿1,252.74
List Price:  ₺961.22
You save:  ₺274.65
List Price:  R2,346.24
You save:  R670.40
Already have an account? Log In


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