Articles → EXCEL → Pivot Table In Excel 2010

Pivot Table In Excel 2010

In this article I will explain about pivot table in excel.

Software Requirement

Before you start reading this tutorial you should make sure that excel 2007 (or above) is installed on your machine.

Technical Knowledge

In order to understand this tutorial you should know basics about Excel.

What Is Pivot Table?

In simple words pivot table is the data summarization tool of excel. If you are unable to get what do I mean by data summarization then don’t worry you will come to know about it when I work on the examples.

Sample Data

First of all create a sample data on which we can work. For this tutorial I will create a table array (Table array means range of cells in excel which represents data in tabular form) which contains employee name, education, gender and city. See the screen shot below

Click to Enlarge

How To Create Pivot Table

To create a pivot table

Click on the arrow on the top left side of the excel (see the figure below) and select data

Picture showing selecting the records in excel for creating the pivot table

Click to Enlarge

Go to ‘Insert’ tab and click on ‘Pivot Table’

Picture showing the Pivot Table option in excel

Click to Enlarge

A pop up window appears as shown in figure below

Click to Enlarge

As you can see in the figure above the table range gets selected automatically. There is one more option for placing pivot table. Either you can add it in ‘New Worksheet’ or the existing one.

Click ‘Ok’.
You can see that pivot table gets added (see the figure below)

Picture showing the Pivot table added in the excel

Click to Enlarge

Different Parts In Pivot Table

The pivot table is divided into 3 parts

Pivot table layout on the left hand side

Picture showing the left side layout of the pivot table

Click to Enlarge

Pivot tables field list. In this section all the columns of table array will be listed.

Picture showing the pivot table field list

Click to Enlarge

Drag fields between area

Picture showing different areas where you can drag different columns

Click to Enlarge

How To Summarize Data?

Here we will work on one example which clarifies the use of ‘drag field between areas’ section. Consider a scenario in which you are asked to find out number of male and female employees in the company.

So first of all we will drag ‘Name’ from the field list to ‘Row Labels’. You could see that all employees are added in different rows

Picture showing the name column dragged onto the pivot table

Click to Enlarge

Now drag ‘Gender’ to ‘Column Labels’ and you could see that all the distinct values for Gender becomes column

Picture showing the gender column dragged onto the pivot table

Click to Enlarge

Now what you want is count so drag ‘Gender’ in ‘Values’ area. So ‘Drag field between area’ becomes

Picture showing the different columns dragged on the areas section

Click to Enlarge

And layout area becomes

Picture showing the impact on layout when different columns are dragged on the areas section

Click to Enlarge

You can also drag ‘Gender’ field to filter so that you can filter records based on gender. Once you add ‘Gender’ your layout area becomes

Click to Enlarge

So you could see that pivot table is a useful tool for data summarization.

Posted By -	Karan Gupta

Posted On -	Saturday, November 22, 2014

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**