ExcelFunctions.net

Pivot Table Tutorial Part 3 - Advanced Pivot Table in Excel 2007 or 2010

Home » Excel-Pivot-Table-Tutorial » Advanced-Pivot-Table-In-Excel-2007

This page shows you how to create a Pivot Table in Excel 2007 or Excel 2010, using the same example spreadsheet as was used to create the basic pivot table in part 2 of this tutorial. This spreadsheet is repeated below:

  A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 05/01/2010 2010-0001 $33,242 Smith North
3 07/01/2010 2010-0002 $41,234 Barnes South
4 08/01/2010 2010-0003 $3,255 Brown North
5 12/01/2010 2010-0004 $34,124 Brown North
6 15/01/2010 2010-0005 $4,312 Smith North
7 19/01/2010 2010-0006 $41,233 Jones South
8 22/01/2010 2010-0007 $10,313 Smith North
9 30/01/2010 2010-0008 $26,001 Jones South
10 02/02/2010 2010-0009 $34,234 Barnes South
11 .
.
.		 .
.
.		 .
.
.		 .
.
.		 .
.
.

For this advanced pivot table example we will create a pivot table that shows the total sales for each month of the year, broken down by sales region and sales rep. The process for creating this Pivot Table is described below. Note that the initial steps are the same as for the basic pivot table in part 2 of this tutorial.

1.

Select any cell within the data range or select the entire data range to be used in your Pivot Table.

Note: If you select a single cell in the data range, Excel will automatically identify, and select the whole data range for your Pivot Table. In order for Excel to successfully do this, the following must be satisfied:

- Each column in the data range has a header
and
- The data does not contain any blank rows
 Select Cell in Data Range Before Starting Pivot Table
2.

Click on the Pivot Table button, which is generally located on the left, within the 'Insert' tab.

Excel 2007 Pivot Table Select Button
3.

You will be presented with the window entitled 'Create PivotTable' (shown on the right).

Make sure that the selected range is the range that you want to use for your Pivot Table.

There is also an option asking where you want the Pivot Table to be placed. If you are not sure, select the option 'New worksheet'

Click OK

Excel 2007 Create Pivot Table Window
4.

Excel will present you with an empty Pivot Table, and a 'Pivot Table Field List', which contains the data fields for your Pivot Table. Note that these are the headers from your data spreadsheet.

We want the Pivot Table to show the sums of the sales figures for each month, broken down by region and sales rep.

Therefore, from the 'Pivot Table Field List':

  • Drag the 'Date' field into the area marked 'Row Labels'
  • Drag the 'Amount' field into the area marked 'Σ Values'
  • Drag the 'Region' field into the area marked 'Row Labels'
  • Drag the 'Sales Rep.' field into the area marked 'Row Labels'
 Excel 2007 Advanced Pivot Table Field List
5.

The resulting Pivot Table (shown on the right) will be populated with the daily sales totals for each sales region and each sales rep.

However, we want to group the dates by month. To do this:

  • Right click on any of the dates in the left hand column of the Pivot Table
  • Select the option Group...
  • A window will pop up. Select the option Months
    (note that you can also group dates and times by other time periods, such as quarters, days, hours, etc)
  • Click OK

The resulting Pivot Table is shown below:

Excel 2007 Advanced Pivot Table
Advanced Pivot Table in Excel 2007, Grouped by Month

As required, the final pivot table shows the total monthly sales, broken down by sales region and sales rep.

The Pivot Table might be improved visually, by formatting. For example, if columns B - G are formatted as currencies, this will make the Pivot Table easier to read. This has been done in the spreadsheet used in the following section (below).


Pivot Table Page Field

The Pivot Table page field allows you to view the data for a single value in one of your data fields. For example, in the pivot table above, you could view just the data for the North sales region or just the data for the South sales region. This is illustrated below.

In order to view just the data for the 'North' sales region, return to the 'Pivot Table Field List', and drag the 'Region' field header into the page drop area, as shown below.

Add Page Field in Excel 2007 Advanced Pivot Table
Excel 2007 Advanced Pivot Table with page field

You will see that the 'Region' field is now located in the page drop area, at the top left of the Pivot Table. Use the drop-down list for this field to select the Region 'North'. The resulting Pivot Table, which shows just the sales for the North region, is shown on the right.

You can also quickly view just the sales for the South region by selecting 'South' from the drop down menu.