Pivot Tables - 1

I have been receiving numerous feedbacks to write about pivot tables. It was only when I started writing I realised that writing about pivot tables in a manner that can be easily understood by layman is not an easy task. In this article I have put down an introduction to pivot tables and method of creating a simple pivot tables. We will continue this with more enhanced features on pivot table in articles to follow.

Pivot Table – An introduction
A pivot table is an interactive worksheet table that quickly summarises large amounts of data using a format and calculation methods you choose. It provides an easy way for you to display and analyse summary information about data already created in Microsoft Excel.
For example, with a Microsoft Excel database that contains sales records for several sales managers, a pivot table can help analyse sales data by name, region, month, year, etc that can be chosen from source data.
The table below is the source data:
Month
Year
Type
Sales Managers
Sales Rs.
Sales Units
Region
Jan
2005
Beverages
Mr. A
2354
50
East
Jan
2005
Hair Oil
Mr. B
3312
100
Nort
Jan
2005
Edible Oil
Mr. C
5456
50
South
Feb
2005
Beverages
Mr. B
5419
300
West
Feb
2005
Hair Oil
Mr. C
8943
200
East
Feb
2005
Edible Oil
Mr. A
9866
500
Nort
Mar
2005
Beverages
Mr. C
1425
100
South
Mar
2005
Hair Oil
Mr. A
3218
55
West
Mar
2005
Edible Oil
Mr. B
6589
30
East
Apr
2005
Beverages
Mr. C
6456
110
Nort
Apr
2005
Hair Oil
Mr. A
1211
250
South
Apr
2005
Edible Oil
Mr. C
9553
355
West

A sample pivot table based on above table is shown below:
Sum of Sales Rs.
Sales Managers



Month
Mr. A
Mr. B
Mr. C
Grand Total
Jan
2354
3312
5456
11122
Feb
9866
5419
8943
24228
Mar
3218
6589
1425
11232
Apr
1211

16009
17220
Grand Total
16649
15320
31833
63802

‘Month’ and ‘Sales Managers’ field is used from the source table to organise data in pivot table.

The data of ‘Sales Rs.’ is used from the source table and is summarised and totaled.

Steps to create a simple pivot table
You create a pivot table using the Pivot Table wizard. The Pivot Table wizard is an interactive set of dialog boxes that guide you through the steps of choosing the source data and layout you want to use for the pivot table.
From Data menu, choose the Pivot Table command. Step 1 of the wizard will pop-up as shown:


Where is the data that you want to analyse?
Select Microsoft Office Excel list or database (Option 1)

What kind of report do you want to create?
Select Pivot Table (Option 1)

Click Next

Step 2 of the wizard will pop-up as shown:

Select the entire source data as the range and click Next

Step 3 of the wizard will pop-up as shown:

If you want your pivot table in the same sheet as the source table select Existing worksheet, else select New worksheet.

Click on ‘Layout’, the layout designer wizard will appear as follows:

Drag-drop the ‘Month’, ‘Sales Manager’ and ‘Sales Rs.’ field as shown above. The layout should look as shown below:


Click Ok and then click Finish. Your Pivot table as required is ready.

Try creating similar pivot tables with other fields. Also use Page (as highlighted above) in the layout designer and see the powerful analysis that it helps you with. The above article will help you create simple pivot tables, we would see some more features of pivot tables in articles to follow.