ExcelFunctions.net

Pivot Table Tutorial Part 2 - Create a Pivot Table in Excel 2007 or 2010

Home » Excel-Pivot-Table-Tutorial » Create-A-Pivot-Table-In-Excel-2007

This page shows you how to create a Pivot Table in Excel 2007 or Excel 2010, using an example spreadsheet of company sales figures during 2010. This spreadsheet, which was introduced in Part 1 of this tutorial, 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 .
.
.		 .
.
.		 .
.
.		 .
.
.		 .
.
.

We begin with a very simple pivot table example, in which we show the total sales for each of the four sales reps in the above spreadsheet. The following is a step-by-step guide of how to create this pivot table in Excel 2007:

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.

You will now be presented with an empty Pivot Table, and a 'Pivot Table Field List', which contains several data fields. Note that these are the headers from your initial data spreadsheet.

Within this 'Pivot Table Field List':

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

Your Pivot Table will be populated with the total sales for each sales rep, as shown on the right.

If you want the sales to be displayed as a currency, this is done by formatting the cells containing these values. To do this:

  • Select the column containing the cells to be formatted.
  • Right click on this column with the mouse and select the option Format Cells ...
  • A window will pop up. Ensure the Number tab of this window is selected
  • From the list of data types, select Currency, and then, from the options that appear on the right, select the currency type and the number of decimal places that you want to display.
  • Click OK

Your final Pivot Table will be as shown in the lower table on the right.

Pivot Table Before Formatting:
Excel 2007 Pivot Table

Pivot Table After Currency Formatting:
Excel 2007 Pivot Table