Top 10 Basic Excel Formulas Useful For Any Professionals

Now in this set of video tutorial let us learn another very useful function from excel that is Pivot tables. Pivot tables 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 and letting you understand. Pivot tables 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 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, go to insert and under the pivot table option go to pivot. Once you do that you see a dialogue box 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 were 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 would have had opened up in the sheet 1 itself. Now this pivot table 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 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 your are drawing a table, so table will have a header, table will have a left hand column in terms of index so just look at these 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 these 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 a using pivot tables 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 percentage of total annual salary which we want so, 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 details, 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 sub categories 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 these 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 mentions 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, now let us try to generate another set of 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 in pivot table 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 dropdown 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 kind of options within the pivot table.Here are some articles that will help you to get more detail about the Microsoft Excel Tips and Tricks so just go through the link.Here are some articles that will help you to get more detail about the Microsoft Excel Tips and Tricks so just go through the link.