How To Use Pivot Table in MS Excel ?

How To Use Pivot Table in MS Excel 

Microsoft Excel has many options, which are so useful that they make our work easy. In this blog we are going to check one such option Pivot Table.

What is Pivot Table?

"A Pivot table is a table of statistics that summarises the data of more extensive table.This summary might include sum,averages or other statistics.which the pivot table groups together in meaning full way."

Let's discus a real time scenario.

Imagine if we have a data containing 3 Columns for Country,Product and Unit sold for each product.

For example:

Country	Product	Units Sold
Canada	Paseo	292
Canada	Montana	345
Germany	Paseo	367
United States of America	Montana	615
Germany	Carretera	888
Germany	Montana	921
Mexico	Montana	958
Mexico	Paseo	974
Germany	Paseo	1006
Germany	Carretera	1321
Germany	Carretera	1513
Germany	Montana	1545

Let's assume from the above table ,I need to calculate sum of total products based on Country.How should I do that? 

• I have an option to filtering out each country and taking sum manually. 
• Or else I can do some complex formula's and get output. 

What you think?Both of them are little bit tedious right.

Here the saviour Pivot Table comes to action.

We will go through step by step to solve this:

Note: Below images are from MS Excel 2007 version. In other versions there might be small differences in views, However the options will remain same.


1. In Excel select the table for which you need summary.

Table selected in MS-Excel 

2. Select Insert > PivotTable > PivotTable.

Select Pivot Table Option from Insert Tab

3. Leave "Select the table or range" option as it is,Because we have already selected data. In the Option for "Choose where you want the PivotTable report to be placed", select the option which you want.

Create Pivot Table Dialog 

If you need the summary report in other sheet then select "New Worksheet" Or else you need report in the same sheet then select "Existing Worksheet". In this example I am selecting "Existing Worksheet".

4.Click on sheet where you want the upper left corner of the pivot table to be placed,Click "OK".

Select location

In this example I have clicked E2 cell.

5. You will get a new empty Table as shown in below image.On the right hand side there is a task pane named "Pivot Table Field List".In the panel you can see column headers are shown as list of check boxes.

An empty Pivot Table

6. For this example drag the option "Country" to the "Row Labels"area below the task Pane.In the same way drag "Units Sold" to the "Values field".Check if drop down under "Values" filed is set to Sum of Units Sold.If not you, can change this by clicking on drop down list and going to Value Filed Settings.

Choose Field Task Pane

7. Now you can see the Empty Pivot Table has been filled with Country and Sum of Units Sold.

Pivot Table with Sum of Units
Sold for each Country

Yeah, here is what we were looking for.This looks simple right?

You can just try to play with this table,So that you can know how to customise this table as per your requirements.Try to change Sum to Average or see what happens if you drag Product to Column Labels or Product to Row Labels(add product below Country).


Thanks for reading this post.If you find this post useful, then please subscribe to my blog and share this post with your friends & colleagues.

If you have any feedback or queries, please comment it at the bottom of this post.


If interested you can try free CSV tool for CSV related tasks.For more information read this post.