How to Create Pivot Tables in Microsoft Excel

Insert PivotTable

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.

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.



  1. Azhar Chaudhary  

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

  2. Chaitanya  

    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!!

  3. Cynthia  

    Thank you this was very helpful. I had been educated in this area in college, but it has been awhile. Instead of using pivot tables, couldn’t also use access.Access will also find your averages and separate however you wish. Thanks for the reminder. Good post. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *


To Top