Top Nav

How to Create Pivot Tables in Microsoft Excel

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.

Student Marks

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.

Pivot Table Box

Excel will open a new sheet with some complex columns, but it’s not. It will look something like this.

Field List

Now, check all the boxes (Student, Subject, Score). Move the required fields in the 4 areas shown below.

Pivot Table Area

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.

Area Selection

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.

Related Article:  Office 2013 Deployment Tool for Click-to-Run Preview

Value Field

Select Average from the dialogue box and click OK.

Field Settings

It will display the average marks of class in each subject.

Student Average

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.

More Reading:

, ,

2 Responses to How to Create Pivot Tables in Microsoft Excel

  1. Azhar Chaudhary December 13, 2011 at 11:09 pm #

    Nice one! Thanks. I was always scared by Pivot tables but this has clarified the matter 🙂

  2. Chaitanya January 12, 2012 at 3:10 am #

    Nice Hammad. But i see difference in snapshot and your wording. Refer sixth snapshot and your paragraph. “I’m using Student Name as report filter, Subject as row labels and put scores in the value area”. Any way got the logic. Thanks!!

Leave a Reply