How to use a pivot table in excel.
In this Excel Training, I will show you the importance of grouping numbers, grouping dates and grouping texts in a pivot table report. If you have not used pivot table before then you are going to love this feature of excel. If you already created one before, you may want to start reading from here – Grouping numbers in Pivot table
A Quick Pivot Table Tutorial:
First of all, let’s understand why you should learn pivot table? A pivot table allows you to extract the significance from a large, detailed dataset. If you are involved in data processing then Pivot table is the best tool to use in Excel. A pivot table can sort, count total or give the average of the data on a presentable dashboard. It is a very powerful tool that helps to summarise the data. I strongly recommend avoiding using formula to summarise data, rather follow this simple pivot table tutorial
Let’s learn how to use Pivot Tables in Excel:
Assume that you are working in an HR department and you have a data set of employee records show tables with information like date of joining, employee name, salary, division, rating and Age.
Now you want to check division wise total salary, average salary and how many people are working on a specific division. If you are good in using formula then you can use the formula but pivot table will give you the result quickly.
To get a quick report, let’s apply Pivot Table:
- Choose your entire dataset. You can press a shortcut key Ctrl+A to choose the dataset or select the very first cell of your data range and then Ctrl + shift + right and down arrow to choose your data range.
- After your selection goes to insert tab and then click on the pivot table. The shortcut key is Alt + N+ V
It asks for 2 inputs:
- Which pivoting data should be used to create a Pivot table and
- Where do you want to create the pivot table? If you choose existing worksheet then you need to give the location where you want to see the report. I am selecting new worksheet so that my pivot table report will be on a new sheet.
As soon as you will press OK then a new sheet will be created with your pivot table field list. Here we will change few settings. Right click on your report area and click on pivot table options.
Follow these steps as shown in this pivot table tutorial: Display tab >> Classic layout
Now your layout is ready to prepare the report. Think about the first case where you need to show the result division wise total salary, average salary and how many people are working there.
To get this drag your division column in rows section and salary in value section for 3 times as you need the salary, average salary and count. If you follow this your pivot report should look like below:Now as you drag salary column for 3 times in values that’s why it is showing as a sum of salary for 3 times. Double click on the 2nd sum of salary tab and then choose average in Value Field Setting of Pivot table in Excel and for the third one choose count.
Now for the first value it will show you total salary and for the second column it will show you the average salary and for the third column, it show the count. The final table will look something like this
Your report is ready. With few drag-drops clicks, you can prepare a report which normally takes not more than 10 seconds. That’s why most of the professionals prefer working with a pivot table.
Now from the same data, If we wish to see division wise and rating wise head count
Just scroll this quick pivot tables tutorial and follow the same steps:
Remove all the selecting fields from your pivot table field list and be ready for another report i.e. division wise rating wise head count.
Now, this time, my division will be in row section, the rating will be in column section and name field will be in value section.
You can see your excel table is now ready and you are getting the report of division wise rating of headcount. Here’s how your report should look like
I think that this is enough for a basic Pivot table.
Learn more basic in our Free Online Excel Course