Pivot Table In MS Excel

Updated June 12, 2023

Part -20 – Pivot Table In MS Excel

Download Excel Templates – Pivot Table In MS Excel

Pivot table in ms excel (without solution)

Pivot table in ms excel (with solution)

Transcript For The Video – Pivot Table In MS Excel

Video on Pivot Table in MS Excel

Now in this set of video tutorial, let us learn another very useful function from Excel, that is Pivot tables. Pivot table in MS Excel essentially are found in the insert tab, and on the left-hand side, the functionality of pivot tables can be best explained by actually making a pivot table in ms excel and letting you understand. Pivot table in ms excel are generally helpful when we want to really organize a large set of data or do an analysis on the large set of data like the one we have as of now for XYZ Company. So let’s apply pivot table in ms excel on this data and see how it makes sense. So first and foremost is, select the data on which we want to make a pivot table in ms excel, go to insert, and under the pivot table in ms excel option, go to pivot. Once you do that, you see a dialogue box that asks you for two things, for confirmation, select a table or a range. So since I have already selected the table, that’s ok, or you can also have a different or external data source if data is not inside this worksheet. And another thing that you can do is choose where you want the pivot report to be placed. Should it be in the existing worksheet or in the new worksheet? So let me just keep it as it and what will happen is that a new worksheet opens up if it was in the existing worksheet, the pivot table in ms excel would have had opened up in the sheet 1 itself. Now, this pivot table in ms excel is available in sheet 9. So once you see this sheet 9, which gives you a layout something like this on the right-hand side, you may see the pivot table field less extra.

Don’t be scared because this is just the layout, and it is very simple to use. Now imagine that you are drawing a table, so the table will have a header the table will have a left-hand column in terms of index so. Just look at this set of labeling on the right-hand corner. There is a row label; there is a column label and the values that you want to show, and there is something called report filters. I will show you all of these and the meaning of this one by one by giving an example. Let’s say if we want to actually make a report, let’s say we want department-wise salary distribution, Ok. We want department-wise salary distribution, and we can actually do it using a pivot table in ms excel. So as you can see, the right-hand side becomes invisible if I click outside this table like the one I was typing department wise salary distribution, but it comes back when I click here in this area, so let’s click here in this area, and we find that the field is or the other things are visible here now. Now let’s say I want department-wise salary distribution the one field which is important is department, and the other field that is important is, let’s say, the percentage of total annual salary which we want to, on the rows from 4,5,6,7. Likewise, I want departments to be distributed, so what I will do is I will select this, and the moment I do that, as you can see, it automatically gets populated under the row section. These are known as row labels.

Ok. Now what do I want under the column label? On the column, label would be these, so under the column label, actually, I don’t want anything. Because I just want the values respective to the percentage of the total annual salary. So those who are working in finance, sum total their annual salaries, and give it to me, so as you can see, there is another column which is known as sum total, this is a summation sign. So what you will need to do is just click here, and drag and drop to the summation sign like this and just leave it, and you will find that the percentage of total annual salary is visible here. Now that we have understood the department-wise percentage annual salary. Let us now probably define this analysis in to further detail. Let us see you know how this distribution is on the basis of gender. So, say, for example, within each department, finance under male and female, how this would really work? How can I actually go ahead and choose gender, and what you need to do here is if you click here gender, you will find that within finance, there are two subcategories that are introduced, female and male, and their bifurcation is also mentioned, sum total of these two is mentioned above which is in bold. So within each department, as you can say, finance, IT, marketing, and support all of them have this set of bifurcations, so this is another way of looking at the distribution.

One thing which is very important here is something called the report filter, as you can see on the top, so one way which we have done is gender-wise distribution among the departments. We can also look at using report filter and come to the same objective. So what we will do here is, let’s say I pull this or drag this gender from this field to the report here, so what happens now on the left-hand side is that there is another column which gets introduced called gender, and under which you can see there is a drop-down, drop-down mention all, all means male and female both included. So the result for all is essentially provided here. Let’s say if we choose only one, let us say female, so the distribution would be limited to only female employees and their salaries. Likewise, if I want to have a report only on the basis of male, as you can see, the report will only be on the basis of male itself. So if I consider all of them, the report will be considering all the employees from the company. This is how you know you can kind of use report filters now; another thing that you must know is that this table, when you are doing things here, changes dynamically. So you may want to save this table so that you may use this table for final reports or presentations. For doing that, you can select these rows and columns and just copy this and paste as values so that your table is saved. So this is a table which will not change. This is pasted as values, now tomorrow let’s say I just want to generate only a report on the basis of male. I can separately save this report and paste it as values, so this report corresponds to only male employees, and this report corresponds to all employees, so you may generate different kinds of reports and save it for your usage.

Now let us try another kind of a report. Let’s say I don’t want a report like the one we had just created. Let me go back and not select any so that it goes back to the pivot table in ms excel. Now let us try to generate another set of the report so. For the time being, I will just uncheck department and uncheck the percentage salaries so that the original report is not included. Now the report which I want to generate here is that I want to understand how the department-wise gender distribution is so earlier what we have seen is department-wise salary distribution, now I want to understand department-wise gender distribution, Ok.

So this is what my objective is and for this what we require is on the left-hand side the row would be obviously department and what I also want is gender so finance, male, female, male, female so on and so forth and I want the count of male and female under each subheading. So for counting that, what we will have to do is, please note, I am pulling gender again and dragging and dropping it here. So the moment I drag and drop here, what it does is under each category, the male and female gets counted and are distributed in the format of this kind of a table. So it is now very easy to kind of understand. Let us say IT, only one female works however in let’s say, in support, there are two females, so likewise, you can have different kinds of analysis that you may like to do on the set of data which you have. At this point, I really want to point out here is the number of male and female be counted. Suppose you wanted to count the monthly salary and the annual salary that also you could have done, but in order to count that, you could have used another function which is the sum total of these salaries. So there are different kinds of function that are available, max, min, product, average, count, sum. So let’s say I wanted to calculate how much the salary of the of finance employees is, so you can also do that. Another important thing with pivot table in ms excel is this column here, on the right-hand side, on the right-hand bottom corner, the count of gender where we have calculated as of now. Let’s say we wanted to calculate the summation of the salaries under individual departments and within the genders for example, what would be the salary of all the females under the finance department if that was the condition that we wanted to explore? The count function would have been redundant so we would have used the summation function and summation function on the annual salary. So let me undo this, so let’s just drag and drop it here so that the summation sign or the summation boxes vacant, and let me drag and drop the annual salary within this box, and you can see that the sum sign is automatically identified as excel being one of the intelligent tool. It automatically identifies that the salaries have to be sum totaled. However, if you wanted to change the automatic tool used by Excel, you could have actually used the drop-down, and you can change the value settings from here, so let’s say if you wanted to change the value field settings to let’s say to an average so you could have also have done that a maximum, minimum, a product or a count you could have changed these from this options as such, so let’s say if you wanted to use an average we can just select that and click ok. So this is how you know the things would change, and the display can be used for different kinds of options within the pivot table in ms excel.

Recommended courses

This article has been a guide to the Pivot Table in MS Excel which can be used efficiently to sort complex data into meaningful insights. This post is a complete heads-up on how we can create a pivot table in MS Excel. These are the external link related to MS Excel, so just go through the link for more detail: