Leading the way in Microsoft Office Development

Excel

What is a Pivot Table ?

A Pivot table allows you to "interactively" aggregate your data and is a great way to generate a dynamic summary report.

The term pivot refers to being able to change the position of the fields and to being able to transpose the rows and columns of your table.

The more data you have to analyse then the more appropriate it is to use a pivot table.

Pivot Tables are designed for dynamic viewing of database data (either contained within Excel or from an external source).

Pivot Tables are always linked to the data they are derived from.

When a Pivot table is created Excel builds a special memory cache containing your data. This allows you to change and recalculate your data.

The data you will want to analyse will normally be numerical values, although it is possible to also use text values as well.

Looking at the Excel list it is hard to identify how much is being spent a month on each division.

The pivot table below summarises this Excel list and makes it much easier to understand.

Advantages of using a Pivot Table

To help identify relationships within your data that would otherwise be hard to see due too the quantity of data.

To aggregate and summarise a large quantity of data into a smaller more condensed table.

To organise your data into a format that would be easy to chart.

Pivot tables are dynamic allowing you to change the appearance instantly and because the table is linked to your original data it can be refreshed extremely quickly.

You can quickly include and exclude individual fields from your summary table.

Data can be grouped together using outlines and individual rows and columns can be hidden.

Creating a Pivot Table

You create a pivot table report by selecting (Data > PivotTable and PivotChart Wizard).

Select any cell within the list from which you want to create your table.

You can use pivot tables to aggregate data from a wide range of sources including external databases.

There are four places you can use for the data for your pivot table

1) A list or database table in Excel. The excel data must be arranged in the correct way to get the maximum benefit.

2) An external source, database or another workbook

3) A consolidation of multiple ranges from different worksheets.

4) Another Pivot Table or Pivot Chart.

Pivot tables that are based on external data can use a great deal of memory and the size of the workbook can increase dramatically.

Using the Pivot Table Wizard

You can display the Pivot Table wizard by selecting (Data > PivotTable and PivotChart Report).

 Pivot Table Report - Starts the Pivot Table and Pivot Chart Wizard, which guides you through creating or modifying a PivotTable or PivotChart report.

There are three steps allowing you to change the type of report, the data to use and the location of your pivot table.

The pivot table wizard will take you through the three stages.

Step 1 - Type of Report - Lets you choose where the data is coming from either an Excel list or database, external source, consolidation of ranges or even another pivot table.

Step 2 - Source Data - Lets you select the cells and define the range to use for the source data. If the insertion point is anywhere within the Excel list or database then the whole block of cells will be selected automatically.

Step 3 - Location - Lets you decide whether to display the pivot table report on a new worksheet or on the existing worksheet

Step 3b - Layout - This is not a separate step in the Wizard as it can either be done before or after the pivot table has been created. This lets you determine the exact layout of the report by dragging the fields onto a pivot table diagram.

Updating a Pivot Table

A pivot table is not linked directly to its source data but instead is linked to a special hidden cache of the data.

When a change is made to the data the PivotTable is not updated automatically.

 Refresh Detail - Updates the data in a PivotTable or a PivotChart report if the source data has changed.

You can refresh the data at any time by clicking on the Refresh Button on the PivotTable toolbar or by using the shortcut menu.

You must refresh the pivot table manually by using the refresh button located both on the PivotTable toolbar and on the shortcut menu.

It is possible to update your pivot table automatically every time the workbook is opened. Select (PivotTable > Table Options) and select "Refresh on Open".

Can you refresh your data at regular intervals, does it refer to Excel data ??

Pivot Charts

You can create charts that are directly linked to your pivot table results.

It is even possible to change the data layout by adjusting the chart.

Things to Remember

Pivot Tables work well when you have large amounts of data to analyse.

The simplest type of pivot table is just an aggregate of a single column which could be used to show the number of occurrences of each item in a list.

If you are using a data list on a worksheet then all your columns must have labels as these will be used as the field names.

You must remove any automatic totals or subtotals from your list before the pivot table as the pivot table report will create any necessary total automatically.

Referring to a named range on a different worksheet in the same workbook works. Referring to a named range in a different workbook doesn't work.

The entire list will be used to generate the pivot table and any hidden rows will be included.