One of the most useful features of Microsoft Excel is Pivot Tables. But many people don’t know how to use it properly. Pivot Tables are used to summarize, analyze and present your data in a user friendly way. For example, if you have a database of student marks for a semester and want to display results based on subject or view marks of a specific student in different subjects, you can use a Pivot Table.
Follow the guide below and you will be able to create pivot tables in seconds.
Open any data you want in Excel. I’m using Students marks as an example.
Click the Insert tab and select Pivot Table.
The Create PivotTable dialogue box comes up. Select the range of data and select New Worksheet if you want to display the pivot table in a new worksheet. I recommend displaying the Pivot Table in a new worksheet. Click OK.
Excel will open a new sheet with some complex columns, but it’s not. It will look something like this.
Now, check all the boxes (Student, Subject, Score). Move the required fields in the 4 areas shown below.
I’m using Student Name as report filter, Subject as row labels and put scores in the value area. What I’m doing is to finding the average score in each subject. You’ll find a lot from a single data file by interchanging the report filter. This one is simple.
Now, I want to find the average score in different subjects. Click the dropdown arrow in the Values Field and click on Value Field settings.
Select Average from the dialogue box and click OK.
It will display the average marks of class in each subject.
Similarly, you can create various other reports to summarize any data you want. If you have any questions, leave a comment and let me know.