Excel Pivot Tables

NOTE: If Excel recognizes your set of data as a database you can sort the data

The pivot table is a powerful analysis tool that allows the analyst to organise any database into tables with all kinds of sub-totals (SUM, COUNT, AVERAGE...) comparing sales of products by stores, sales of products by cities, sales of products by stores, by month AND by cities, etc.

First let's demystify the pivot table. The pivot table is a powerful data analyzing tool. Pivot tables are designed for dynamic analyses of large quantities of data but as a reporting tool it is somewhat limited (if the report is for you or your immediate colleagues everything is fine but if the report is for your boss or a VP or the public then...). To develop a report with the exact layout that you need 75% of the automation is made possible through SUMPRODUCT.

Before you get into pivot tables be sure that you know how to use the filters in Excel and that you have discovered the magic function (SUMPRODUCT).

Here is an example of a pivot table:

You have data supplied to you as TXT or CSV files or your import data from a database with Microsoft Query in the following format:

Date/Month	Product	City	Qty	Amount
January	Brooms	New York	536	1072
February	Brooms	New York	756	1512
March	Brushes	New York	654	1308
January	Brushes	New York	365	730
February	Brooms	Montreal	758	1516
March	Brushes	Montreal	445	890
January	Brooms	Montreal	255	510
February	Brushes	New York	654	1308
March	Brooms	New York	324	648
January	Brushes	Montreal	156	312
February	Brushes	Montreal	753	1506
March	Brooms	Montreal	135	270

Imagine such a table in your spreadsheet with 50,000 lines of data and you want to create a table answering to the following questions:

How many of each products were sold by city?

	Montreal	New York	Total
Brooms	1,148	1,616	2,764
Brushes	1,354	1,673	3,027
Total	2,502	3,289	5,791

How much of each products were sold by city?

	Montreal	New York	Total
Brooms	$2,296	$3,232	$5,528
Brushes	$2,708	$3,346	$6,054
Total	$5,004	$6,578	$11,582

To complete such a task instantaneously, you will use the pivot table. To create the first pivot table (Products by City), copy the raw data in Excel. Select the table go to "Menu/Data/PivotTable and PivotChart Report" . In the first dialog box, choose "Microsoft Excel list or database" , click "Next" . In the second dialog box, accept the "Range" by clicking "Next" . In the third dialog box Click on "Layout..." and you will see the following dialog box:

Excel Pivot Table Window

Drag the small gray buttons on your right onto the white shape in the middle, "Qty" over "ATA" , Product over "RW" and "City" over "COLUMN" . Click "OK" and then "Finish" .

You have just created your first pivot table.

To create the second Pivot Table with the same data, right click anywhere on the first Pivot Table, select "Wizard/Layout" . Drag the "QTY" button off "DATA" and replace it by "Amount" . To create any other Pivot Table from the same data, right click anywhere on the first Pivot Table, select "Wizard/Layout" and move the gray buttons around. For example, "Product" over "ROW" and "Date/Month" over "COLUMN" will give you a pivot table about "Sales of products by month" .

Try this one: "Product" over "ROW" and, "City" and "Date/Month" over "COLUMN" . You have now "Sales of products by city and by month" .

Discover AutoFilter in Excel and the magic function SUMPRODUCT before your go too far with pivot tables.

There are five sections in this website

Section 1: About Databases: 4 lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 7 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables.
Section 4: Reporting with Excel: 7 lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.