What is a PivotTable?

A PivotTable is a summary table of voluminous data. It used to summarize the data based on the selected data values. Consider
the following example

SalesPerson	Month	Volume
CHAN	OCT-07	450
LEE	NOV-07	430
MANI	NOV-07	230
CHAN	NOV-07	110
MANI	DEC-07	420
LEE	DEC-07	110
CHAN	DEC-07	210

From the above table,

• If we need the total sales volume of each SalesPerson, then we can use DATA / SUBTOTAL command for each change in
  SalesPerson
• If we need the total sales volume of each Month, then we can use DATA / SUBTOTAL command for each change in Month
• If we need SalesPerson and Month wise breakup in a matrix format, we cannot use DATA / SUBTOTAL command

PivotTable can be used for the above summary report. A sample PivotTable will look like this.

	OCT-07	NOV-07	DEC-07	Total
CHAN	450	110	210	770
LEE		430	110	540
MANI	230	420	650
Total	450	770	740	1920

Procedure

1. Prepare the List (You can type or fetch the data from any ODBC compatible database. Refer my earlier post in this blog)
2. Select the List
3. Select Data / PivotTable and PivotChart Report menu command
4. Select the output location (it can be a new sheet or part of the existing sheet)
5. Select the LAYOUT button and
6. Place the SalesPerson in Row area
7. Place the Month in Column area
8. Place the Volume in the Data area
9. Then Finish, now you can see the PivotTable

In the same way you can generate PivotChart

Sample Scenarios

1. A Warehouse wise, product summary
2. Yearwise, Monthwise, Product Sales/Purchase
3. Department wise, Year wise admission for a college

Other Points

• You can have 3 dimensional PivotTables by using Page Field

Once Again I am here to help in EXCEL based solutions, as a service to society. Your comments are driving force behind the success of this blog.