ianfraserconsulting - Learning and Development Consultants

Using Pivot Tables in Excel 2007

Written by Ian Fraser

Pivot Tables

Microsoft Excel Pivot Tables allow you to total a list.    They can manipulate a list to display it in many different  ways. 

When creating a Pivot Table you are able to choose field headings from your list, which you wish to display in the Pivot Table.  These headings are either row headings, column headings, or the data to be totalled.  A pivot table turns a one dimensional database into a two dimensional spreadsheet with column headings and row headings.

Let’s create a Pivot Table:

1. Click a single cell within the database
2. Click on the Insert Ribbon
3. Click on the PivotTable button
4. Check to make sure Excel has selected your list and press OK.

Tip:  If Excel fails to highlight the whole list you will need to highlight it manually.

Design your Pivot Table

Field headings from the database are shown on the right.  These headings need to be dragged into the row, column, totals and filter sections of the dialog box.

Drag the required headings into the appropriate locations, click on the Update button

Tip: The function contained in the Values section can be changed to numerous other functions including count, min, max, and average by clicking on it. And choosing Value Field Settings

Automatically Formatting a Pivot Table

A quick way to format a Pivot Table is to use one of the different designs available in Excel.

Filtering a Pivot Table

Filters allow you to show records that meet a given condition or criteria in the Pivot Table, these filters are very similar to the AutoFilter feature in Excel.

Number Formatting within a Pivot Table

Number formats, such as currency and decimal places, which are in the database, are not automatically carried over to the Pivot Table.  These must be manually formatted in the Pivot Table.

Let’s format the numbers:

1. Click on one of the numbers in your Pivot Table that you wish to format.
2. Right click on the number and choose Value Field Settings

1. Click on the Number format button and choose a number format in the usual manner.

Refreshing a PivotTable

PivotTables are linked to the original database but will not update automatically.  Therefore if the original data has changed and you wish the PivotTable to reflect these changes you must manually update the Pivot Table by pressing the Refresh button on the

Options Ribbon

If you insert new data, the refresh button does not update.  This is because the PivotTable does not know that the new information is part of the data.  Press the Change Source Data button on the Options ribbon and expand the selection.