Excel Pivot Tables
If you work with large amounts of data, you will almost certainly benefit from using Excel pivot tables, to assist you in interpreting and making sense of that data.
Imagine you have the simple spreadsheet below, containing sales data for 2009. The columns of the spreadsheet have the headings "Invoice No.", "Date", "Item Details", "Quantity", "Price Each" and "Price Total".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice No. | Date | Item Details | Price |
| 2 | BX00001 | 02/01/2011 | IPod | $600 |
| 3 | BX00002 | 02/01/2011 | Laptop Computer | $800 |
| 4 | BX00003 | 02/01/2011 | Digital TV | $1,400 |
| 5 | . . . |
. . . |
. . . |
. . . |
The rest of this page will show you how to create a simple Excel pivot table using the above spreadsheet, to show which were the peak months for numbers of sales and to find out if this varies for different item types.
As the interface for creating pivot tables is slightly different in Excel 2003 and Excel 2007, this page shows the process separately for these two versions of Excel.
Click on one of the links below to go to the step-by-step guide of how to create a pivot table in Excel 2003 or Excel 2007:
Create a Simple Excel 2003 Pivot Table
The following steps describe how to create a pivot table in Excel 2003 that groups the data in the simple sales spreadsheet below. The aim of the pivot table is to show the peak months for numbers of sales, and to find out if this varies for each item type.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice No. | Date | Item Details | Price |
| 2 | BX00001 | 02/01/2011 | IPod | $600 |
| 3 | BX00002 | 02/01/2011 | Laptop Computer | $800 |
| 4 | BX00003 | 02/01/2011 | Digital TV | $1,400 |
| 5 | . . . |
. . . |
. . . |
. . . |
Before creating your pivot table, click on a single cell inside the data set. From this, Excel should be able to detect the range of data to be used in the pivot table.
Then complete the following steps :
- From the drop-down menus at the top of your spreadsheet, select Data->PivotTable and PivotChart Report...
-
You will be presented with a window entitled 'PivotTable and PivotChart Wizard - Step 1 of 3'
Make sure the options
'Microsoft Office Excel list or database'
and
'PivotTable'
are selected and click Next
- Excel will ask you about the range of data to be used in your Pivot Table. Make sure the range that Excel has automatically entered is correct and then click Next
- In Step 3 of the PivotTable Wizard, select where you want to put your pivot table (the default setting of 'New Worksheet' is fine). Click Finish
-
Excel will now present you with an outline of a pivot table on your worksheet and you will see a box
labelled 'Pivot Table Field List' on the right of your spreadsheet.
We want to initially group our data by date, to find out which month had the largest number of sales. To do this:
-
In the 'Pivot Table Field List' window, drag the 'Date' field across to the Pivot Table in your spreadsheet.
Drop this field into the area marked 'Drop Row Fields Here'.
(this tells Excel that we want the rows of the Pivot Table to be grouped by date)
-
Again, select the 'Date' field from the 'Pivot Table Field List' window and this time, drag this into
the area marked 'Drop Data Items Here' in your Pivot Table.
This tells the pivot table to show the the count of the date entries (ie. the number of rows containing each date)
|
You will notice that the pivot table is now populated with the dates in the left hand column and the
count of each date (ie. the number of entries for each date) in the second column of the table, as shown
in the spreadsheet on the left. However, we want the data to be grouped by month. To do this, use the mouse to right-click on the left hand column (the dates column) of the pivot table and select the option Group and Show Detail → Group ... From the list that appears, select 'Months' and click OK. The grouping of the months results in the pivot table shown on the right. From this we can clearly see that the peak month, in terms of the number of sales was December. |
|
At any point, if you want to change the properties of your pivot table, you can use the mouse to right-click on the Pivot Table and then select PivotTable Wizard and then Layout.... This takes you to a simple layout structure for your Pivot Table, which you can easily change by dragging the titles.
To do this, instead of dragging the date field into the area marked 'Drop Data Items Here', drag the 'Price Total' field into this area. Because this field is a numeric value, by default, Excel displays the sum of the entries in the 'Price Total' field, rather than the count that was shown for the date field.
Create a 2-Dimensional Pivot Table in Excel 2003
If you now want to discover whether the peak month varies for each sales item, you can do this by creating a 2-dimensional pivot table.
Starting with the 1-dimensional monthly pivot table shown above, we now return to the 'Pivot Table Field List' Box. If this has disappeared, simply click anywhere on the pivot table and it should reappear at the right hand side of your spreadsheet - if not, use the mouse to right-click on the Pivot Table and select the option 'Show Field List'.
Drag the 'Item Details' field to the top of your Pivot Table, where the column headers should go. This is the box just above the column labelled 'Total' in your 1-dimensional Pivot Table (see right).
As soon as you have done this, you will see that the 'Item Details' fields are immediately inserted across the top of your pivot table, resulting in the final pivot table shown below.
From the final pivot table, it is now easy to see the monthly sales figures for each individual item type.
Create a Simple Excel 2007 Pivot Table
The following steps describe how to create a pivot table in Excel 2007 that groups the data in the simple sales spreadsheet below. The aim of the pivot table is to show the peak months for numbers of sales, and to find out if this varies for each item type.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice No. | Date | Item Details | Price |
| 2 | BX00001 | 02/01/2011 | IPod | $600 |
| 3 | BX00002 | 02/01/2011 | Laptop Computer | $800 |
| 4 | BX00003 | 02/01/2011 | Digital TV | $1,400 |
| 5 | . . . |
. . . |
. . . |
. . . |
Before creating your pivot table, click on a single cell inside the data set. From this, Excel should be able to detect the range of data to be used in the pivot table.
Then complete the following steps :
- Click on the Pivot Table button, which is generally located on the left, within the 'Insert' tab.
- You will be presented with the 'Create Pivot Table' input window. Make sure the option 'Select a table or range' is selected and make sure the range that Excel has automatically entered is correct.
- Select where you want to put your pivot table (the default setting of 'New Worksheet' is fine) and click OK
-
Excel will now present you with an outline of a pivot table on your worksheet and you will see a box
labelled 'Pivot Table Field List' on the right of your spreadsheet. - This is the window where we
define how the Pivot table is to be made up.
We want to initially group our data by date, to find out which month had the largest number of sales. To do this:
-
In the 'Pivot Table Field List' window, drag the 'Date' field into the 'Row Labels' Area
(this tells the pivot table that we want our rows grouped by date)
-
Again, select the 'Date' field and this time, drag this into the '∑ Values' Area
This tells the pivot table to show the the count of the date entries (ie. the number of rows containing each date)
|
You will notice that the pivot table is now populated with the dates in the left hand column and the
count of each date (ie. the number of entries for each date) in the second column of the table, as
shown in the spreadsheet on the left. However, we want the data to be grouped by month. To do this, use the mouse to right-click on the left hand column (the dates column) of the pivot table and select the option Group ... From the list that appears, select 'Months' and click OK. The grouping of the months results in the pivot table shown on the right. From this we can clearly see that the peak month, in terms of the number of sales was December. |
|
To do this, instead of dragging the date field into the '∑ Values' Area of the 'Pivot Table Field List' Box, drag the 'Price Total' field into this box. Because this field is a numeric value, by default, Excel displays the sum of the entries in the 'Price Total' field, rather than the count that was shown for the date field.
Create a 2-Dimensional Pivot Table in Excel 2007
If you now want to discover if the peak month varies for each sales item, you can do this by creating a 2-dimensional pivot table.
Starting with the 1-dimensional monthly pivot table shown above, we now return to the 'Pivot Table Field List' Box. If this has disappeared, simply click anywhere on the pivot table and it will reappear at the right hand side of your spreadsheet.
|
Drag the 'Item Details' field into the 'Column Labels' area. You will notice that the Item Details are immediately inserted across the top of your pivot table, resulting in the final pivot table shown below. 
|
From the final pivot table, it is now easy to see the monthly sales figures for each individual item type.
This page provides a very basic introduction to Excel Pivot Tables. However, there is a lot more to learn about this powerful Excel tool. Visit the Microsoft Office Training Website to find a number of excellent free Excel 2007 tutorials, including more in-depth training on Excel Pivot Tables.
