Pivot Tables

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.

Allow you to structure, summarise and compare data.

They are especially useful for:
Subtotalling and aggregating
Summarising based on categories
Collapsing data and drilling down
Filtering, sorting and formatting

In a PivotTable each column becomes a field

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.

Different Types of Calculation

Summary Function - SUM, COUNT, AVERAGE, MIN, MAX, PRODICT,
STDDEV, STDDEVP, VAR, VARP
Custom Calculations - Percentages, Running Totals, Index Values,
Differences. Once the report has used a summary function an additional
calculation is required
Formula - Lets you perform calculations against data in other fields and
return the results to a new Calculated Field in the Values area

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.

Potential Problems
If someone adds or deletes a column in the original source data, the
pivottable cannot be refreshed and displays an error.

You can use a pivot table to link to a range of data on another sheet and
display it in a different order.
A pivot table is extremely useful when you have lots of changing rows, or
blank rows , when the number of rows changes frequently.

Showing and Hiding Data

You can hide an inner field by double clicking on its outer field.
Double click the outer field heading to display the inner field.
Alternatively you can show and hide fields by selecting the corresponding
button on the PivotTable toolbar.

AutoShow
You can use the AutoShow feature to display only a certain number of items
in a field based on values in the data area of your table.
Choose the field in the pivot table and select (PivotTable > Field Settings)
Select Advanced.
Empty Cells and Cells containing Errors
By default cells that do not contain any numbers in the data area are left
blank.
It is possible to display a value or a text entry in any empty cells.

It is possible to use formulas to calculate additional fields in your pivot table,

and it is possible that this formula might return an error.
It is possible to display an empty cell, value or a text entry in any cells that
contain errors.

Using a subset of Data

If you only want to use part of a list then you must use the advanced filter to
filter the relevant data to another location on the worksheet.
Specify the location of your source data
Moving a Pivot Table
Once a pivot table has been created it can be easily moved.
It can be moved either by using the wizard or by cutting and pasting the cell
range that contains the pivot table.
Select any cell in the table and select (Data > PivotTable and PivotChart
Report). Specify a new cell address on the existing worksheet.

Pivot Table Restrictions

You cannot create a pivot table that contains more than 8000 total items.
You cannot create a pivot table that contains more than 256 fields in the
Data Area.

Multiple Consolidation Ranges Example

To switch from one summary function to another select an item in the data
area of your pivot table, click the field settings button and then choose from
the list that appears in the Pivot Table field dialog box.

Grouping on a particular field can be very useful if you want summarize data
over a range of values -- Right-click the field label and choose Group and
Outline | Group...
Blank rows in the data set will prevent you from using Grouping in your Pivot
Table

Use pivot tables as an alternative to linking workbooks using formulas

SS good examples
Important
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.

Calculated Fields
This is a new column
It is possible to add additional fields to your pivot table which are actually
functions of existing fields.
You can create an additional field in your pivot table which can be based on
other fields in your pivot table.

(PivotTable > Formulas > Calculated Field).

Type in the name of the field

You can use the normal operators to calculate your field.
Creating a Calculated Field
This dialog box lets you modify and delete and customised fields.

You can use any of the standard Excel formula options on this dialog box.
It includes all the normal mathematical operators including all the worksheet
functions.
The arguments used in your formula must match the field names in your
pivot table.
You cannot use any cell references or range names in your formulas.
Each calculated field that you add displays as an additional Data Field.
These will also automatically be added to the Field List Pane and the Layout
dialog box.

Removing a Calculated Field

To remove a calculated field, find the name in the drop-down list and press
Delete.
If you have other calculated fields that referred to a calculated field that has
been deleted then #NAME? will be displayed in your data area.
Important
The arguments used in your formula must match the field names in your
pivot table.
You can turn off the field calculation by double clicking on the field and set it
to None.
Calculated Fields can only be used in the Data Area of your pivot table.

Calculated Items
This is a new row
You can create new items that can appear in your fields that are not part of
the original source data.
A calculated field contains user-created formula to determine the value of
the item in a particular field.

(PivotTable > Formulas > Calculated Item).

You create a calculated item by adding a new item to a field and then
defining a formula that calculates the individual values for the item.

Creating a Calculated Item

Notice that the caption of the dialog box identifies the field you are going to
insert the item into.
Removing a Calculated Item
Pivot Table Tools > Options > Formulas > Solve Order
The calculated item solve order dialog box lists all the calculated items in the
pivot table report

List Formulas
Options > Formulas > List Formulas

Summary Functions

Field Calculations
If the data area contains numerical values then the SUM() function is used by
default
If the data area contains non numerical values then the COUNT() function is
used.
What are the options ?
You can specify which fields to include and the type of calculations used on
those fields.

For each combination of values in the row and column fields, the data field
takes on a different value and this value appears in the data area.

Default Calculations
It is possible to use other function in order to summarise your data.
There are actually a choice of eleven different aggregate functions that can
be used in your pivot table.

SUM This is the default function used when the data area contains numeric
numbers in a list or cell range.
COUNT This is the default function used when the data area contains non num
numeric values in a list or array of numbers.
COUNTA Count Nums The number of non blank cells in a list or cell range.
AVERAGE The arithmetic mean of a list or array of numbers.
MAX The largest value in a list or array of numbers.
MIN The smallest value in a list or array of numbers.
PRODUCT The product of all the numbers in a list or cell range.
STDDEV The standard deviation based on a sample.
STDDEVP The standard deviation based on an entire population.
VAR The compound variance based on a sample.
VARP The variance based on an entire population.

When you change the function, the Data area will reflect the changes
automatically.
It is possible to customise the selected function by adding some calculation
options on the pivot table field dialog box.
In addition to the eleven functions that are provide by default you can also
create your own custom calculations.

Custom Calculations
There are also a large number of custom calculations which you can use
including running totals and item percentages.

Some of these calculations require a field to use as well as the value for the
field.
To apply a custom calculation (PivotTable > Field Settings) "Options".
When you select a member of the Base Field, the corresponding items will
automatically be displayed in the Base Item.

Normal Default
Difference From Calculates the difference between two cells.
% of Calculates the percentage of a cell to a selected base value.
% Difference From Calculates the difference between two cell values.
Running Total in Calculates and displays the running total in each cell.
% of row Calculates the percentage of the cell value to the total row.
% of column Calculates the percentage of the cell value to the total column.
% of total Calculates the percentage of the value of the grand total.
Index Calculates the index value of the cell value.

Important
You also have the option of creating your own calculated fields and items.

Grouping
Grouping is a method that can be used to reduce the amount of data
displayed.
It is possible to combine items together from your row and column fields in
order to help organise and present your data.
A typical example is to group date fields in order to display month, quarter,
yearly aggregates when your data is daily.
As you divide a field into different groups new items will be created with the
corresponding group name.
After you have finished grouping your data you can remove the original field.
It is also possible to change the name displayed for a field or new group.
When you create a new group the subtotal will not appear automatically and
you will only see the totals for a group that is collapsed.

Can be used to display trends over a data period that is larger than that of
the actual data.
(PivotTable > Group and Show Detail) sub menu

For example if your data is daily but you want to display it monthly, then you
can group the data by months.

A common field to group is a date column.

Can you display it weekly

Adding a Group

Group -

Hiding and Showing Groups

If you want to hide a group, you can select the corresponding field and
deselect the name of the group from the drop-down list.

Hide Detail - In a PivotTable or PivotChart report, hides displayed detail data. On a

detail rows or columns of a selected summary row or column. Hides the items in a g
Show Detail - In a PivotTable report, displays detail data, if available, for the select
displays detail data, if available, for the selected field or item. On an outlined works
 rows or columns of a selected summary row or column. Show all the hidden items in

Removing a Group
To remove a group select (Data > Group and Outline > Ungroup).

UnGroup -
Once a group is removed from a pivot table it no longer exists. If you want to
display the group again at a later date, then it must be re-created.

Important
You cannot group items that belong to different rows or columns.
It is possible to select and group items from the same field which do not
appear in adjacent rows or columns.

Pivot Table Toolbar

Removed in 2007
This toolbar contains commands that are not on the (Data) drop-down menu
and will be displayed automatically when a pivot table is created or viewed.

Pivot Table Drop-Down - This displays a full list of pivot table commands.
Format Report - Displays a dialog box allowing you to apply a built-in
combination of formats, called an autoformat.
Pivot Chart - Starts the Chart Wizard, which guides you through the steps
for creating an embedded chart on a worksheet or modifying an existing
chart.
Hide Detail - In a PivotTable or PivotChart report, hides displayed detail
data. On an outlined worksheet, hides the detail rows or columns of a
selected summary row or column. Hides the items in a group.
Show Detail - In a PivotTable report, displays detail data, if available, for
the selected cell. In a PivotChart report, displays detail data, if available, for
the selected field or item. On an outlined worksheet, displays the hidden
detail rows or columns of a selected summary row or column. Show all the
hidden items in the pivot table.
Refresh Detail - Updates the data in a PivotTable or a PivotChart report if
the source data has changed. This is disabled when the source data hasn't
changed.
Include Hidden Items in Totals - Toggles the hidden filtered items from
being included in the totals.
Always Display Items - Toggles the display of all the items.
Field Settings - Displays a dialog box allowing you to change settings, such
as subtotals, sort options, and layout options, for the selected field in the
PivotTable or PivotChart report.
Show Field List - Displays or hides the PivotTable or PivotChart field
buttons on the PivotTable toolbar. Also, in a PivotTable report, displays or
hides the layout diagram outlines and labels.

Pivot Table Drop-Down Commands

Format Report - Displays a dialog box allowing you to apply a built-in

combination of formats, called an autoformat.
Pivot Chart - Starts the Chart Wizard, which guides you through the steps
for creating an embedded chart on a worksheet or modifying an existing
chart.
Wizard - Starts the PivotTable and PivotChart Wizard, allowing you to modify
the active PivotTable report.
Refresh Data - Updates the data in a PivotTable or a PivotChart report if the
source data has changed. Also refreshes an external data range on your
worksheet.
Offline OLAP - Provides client-server settings for PivotTable and PivotChart
reports that are based on source data from OLAP databases. Use this
command to create an offline cube file, modify an existing offline cube file, or
connect the report to either the server database or a cube file.
Hide - Removes the selected field from the report. This does not affect the
source data.
Select - This is a sub menu containing the following commands: Label, Data,
Label and Data, Entire Table and Enable Selection.
Group and Show Detail - This is a sub menu containing the following
commands: Hide Detail, Show Detail, Group and Ungroup.
Formulas - This is a sub menu containing the following commands:
Calculated Field, Calculated Item, Solve Order and List Formulas.
Order - This is a sub menu containing the following commands: Move to
Beginning, Move Up, Move Down and Move to End.
Field Settings - Allows you to change settings, such as subtotals, sort
options, and layout options, for the selected field in the PivotTable or
PivotChart report.
Subtotals - Toggles the display of subtotals.
Sort and Top 10 - Toggles the display of the top 10.
Property Fields - Toggles the display of the property fields.
Table Options - Displays the PivotTable Options dialog box, where you set
advanced formatting, layout, memory management, and external data
options for your PivotTable or PivotChart report.
Show Pages - Displays each page of a PivotTable page field on a new
worksheet in the same workbook.

Important
It is not essential to keep the pivot table toolbar displayed as all the
commands can also be found on the pivot table shortcut menu when you
press the right mouse button.
Enable Selection - Turns PivotTable structured selection on or off. When
pressed in, you can select elements of the PivotTable report for formatting or
for refining calculated item formulas by pointing to the top or left side of a
field or item label until the pointer changes to a down arrow or a right arrow,
and then clicking.

Pivot Table Wizard

Removed in 2007
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 y
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.

Type of Report
Highlight any cell within the data and select (Data > PivotTable and
PivotChart Wizard).
Your data must contain column headings as these will be automatically used
for the pivot table fields.

Creating just a pivot table with an Excel list is the default option so press
Next.
Note that the graphics down the left hand side change to confirm your
selection.
Microsoft Excel list or database - You data table must have unique
column labels at the top of each column.
External data source - Includes Access databases and other Excel
workbooks.
Multiple consolidation ranges - Multiple ranges containing similar data.
Another PivotTable or PivotChart -

Source Data
If you are using an Excel list and you have selected a cell within the list
before invoking the wizard then the continuous range of cells will be
selected.
If you select a single cell for the source data before displaying the Wizard the
current region will be automatically selected. You can change this in step 3
of the Wizard ??
All the data should be highlighted so press Next.

Excel will automatically select the range of cells in the continuous range.
It is possible to change the source data range after the pivot table has been
created.
Holding down the Shift key and pressing on the lower right cell will extend
the data source to include that cell.
You can also insert rows into the data source and the data will automatically
be included the next time the pivot table is refreshed.

Location
Decide whether you want to insert the pivot table onto a new worksheet or
the existing worksheet.
Before clicking on the Finish button you can select Layout button to define
the table layout of your pivot table.

New worksheet - A new worksheet will be inserted before the active sheet
containing your pivot table report. This is the default.
Existing worksheet -
Layout - You can always changes these at any point after the pivot table has
been created.
Options - Displays the (PivotTable > Table Options) dialog box.

Layout
After you have completed the steps the pivot table field list will be displayed
to let you change how the table is organised.

Final PivotTable
The following pivot table summarising your data will be displayed.
Important
You should also enter a name for the pivot table, the default will be
PivotTable1, PivotTable2, etc
If you assign the named range "Database" to your cell range it will be
recognised automatically.
If possible it is always better to base a new pivot table on an existing one as
it will use the same memory cache for both tables.