Pivot Table Reports
(From the Excel 2000 Help File)
A PivotTable report is an interactive table that you can use to quickly
summarize large amounts of data. You can rotate its rows and columns to see
different summaries of the source data, filter the data by displaying different
pages, or display the details for areas of interest.
An example of a simple PivotTable report. The source data is
in the table on the left.
When to use a PivotTable report
- When you want to compare related totals, especially when you have a long table of figures to summarize and you want to compare several facts about each figure.
- When you want Microsoft Excel to do the sorting, subtotalling, and totalling for you.
In the example above, you can easily see how the third-quarter golf sales in cell F5 stack up against sales for another sport or quarter, or grand total sales. Because a PivotTable report is interactive, you or other users can change the view of the data to see more details or calculate different summaries.
Creating a PivotTable report
To create a PivotTable report, use the PivotTable
and PivotChart Wizard as a guide to locate and specify the source data you
want to analyse and to create the report framework. You can then use the
PivotTable toolbar to arrange the data within that framework.
1. Open the workbook where you want to create the PivotTable report. If
you are basing the report on a Microsoft Excel table or database, click a cell
in the table or database.
(Table – a series of worksheet rows that contain related data, such as an
invoice database or a set of client names and phone numbers. A table can be used
as a database, in which rows are records and columns are fields. The first row
of the table has labels for the columns.)
2. On the Data menu, click PivotTable and PivotChart Report….
3.
In step 1 of the PivotTable and PivotChart Wizard, follow the
instructions, and click PivotTable under What kind of
report do you want to create?
4.
Follow the instructions in step 2 of the wizard. (If you were already within the
data you wanted to put in the pivot table, Excel will complete the range for
you. However, it is good practice to use Named Range or Dynamic Named Range for
the database. [see Excel Named Ranges lesson]
5. In step 3 of the wizard, determine whether you need to click
Layout.
You can lay out the PivotTable or PivotChart report directly on the sheet
so that you can view the data while you arrange the fields. However, as you get
the hang of creating PivotTables you will probably find using a separate sheet
preferable in which case you may find the Wizard easier to use
An example of a blank Pivot Table Report
Click Layout in step 3 of the PivotTable and PivotChart Wizard to lay out
your PivotTable or PivotChart report in the following situations:
- You're using certain external data sources For some types of external data
sources, including large databases, on-sheet layout will be too time consuming.
For example, a report based on a cube created with the OLAP Cube Wizard in
Microsoft Query is likely to be slow to display layout changes. If you start to
do on-sheet layout and data is slow to appear after you drag the fields into
place, you can click PivotTable Wizard on the PivotTable toolbar to return to
step 3, and then click Layout.
- You need to change the page field settings Use Layout if your PivotTable or
PivotChart report is based on external non-OLAP source data and you want to
create a page field that retrieves data for each item separately. Learn about
using page fields to work with large databases. For PivotTable and PivotChart
reports that are based on source data from OLAP databases, the page field
options are not available.
6. Do one of the following:
-
If you clicked Layout in step 3, after you lay out the report in the
wizard, click OK in the PivotTable and PivotChart Wizard – Layout dialog
box, and then click Finish to create the report.
If you did not click Layout in step 3, click Finish, and then lay out the
report on the worksheet.
Note: When you create a PivotChart report, Excel automatically
creates an associated PivotTable report.
Types of PivotTable reports
A default PivotTable report looks like the example. You
can also display a PivotTable report in indented format, to view all the
summary figures of the
same
type in one column. You can create a PivotChart report to view the data
graphically. You can also make a PivotTable report available on the Web
by using a PivotTable table on a Web page. When you publish an Excel
PivotTable report to a PivotTable table (save as HTML), others can view and
interact with the data from within their Web browsers.
Source Data
You can create a PivotTable report from a Microsoft Excel table, an external database, multiple Excel worksheets, or another PivotTable report. The source data used for the rest of the sample reports in this topic is available to download so that you can experiment with layouts
How to set up the source data
Source data from Excel tables and most databases is organized
in rows and columns. Your source data must have similar facts in the same
column. In the example, the region for a sale is always in column E, the amount
sold is in column D, and so forth.
You can use data from an Excel worksheet as the basis for a PivotTable or
PivotChart report. Because Microsoft Excel uses the data in the first row of the
worksheet table for the field names in the report, the source table or database
must contain column labels.
Using filtered data
Microsoft Excel ignores any filters you have applied to a
table by using the commands on the Filter submenu of the Data menu. The
PivotTable or PivotChart report automatically includes all data in the
table.
Including totals
Excel automatically creates subtotals and grand totals in the PivotTable
report. If the source table contains automatic subtotals and grand totals,
remove all totals before you create the PivotTable or PivotChart report.
Fields and items
A PivotTable report contains fields, each of which corresponds to a
column in the source data and summarizes multiple rows of information from the
source data. Fields in a PivotTable
report
table items of data across rows or down columns. The cells where the rows and
columns intersect show summarised data for the items at the top of the column
and the left side of the row.
In this PivotTable report, the Product field contains the items Shirts,
Socks and Trousers
Data fields and cells
A data field, such as Sum of #Sold, provides the values that are summarized in
the PivotTable report. In the example, cell I5 shows the total for Large
Shirt sales — that is, the sum of the sales figures from every row in the source
data that contains Large in the Size column and Shirts for the Item.
Summary functions
To summarize the data field values, PivotTable reports use summary
functions, such as Sum, Count, or Average. These functions also provide
subtotals and grand totals automatically, where you choose to show them. In this
example, the data from the # Sold column in the source table is summarized with
Sum, showing subtotals and grand totals for each Itemfor the
Viewing details
In most PivotTable reports, you can view the detail rows from the source
data that make up the summary value in a particular data cell.
Changing the layout
By dragging a field button to another part of the PivotTable report,
you can view your data in various ways and calculate different summarized
values. For example, you can view the names of Items across the columns instead
of down the rows.
Row fields
Fields from the underlying source data that are assigned a row orientation in a
PivotTable report. In the preceding example, Item and Size are row
fields. A PivotTable report that has more than one row field has one
inner row field (Size, in the example), the one closest to the data area. Any
other row fields are referred to as outer row fields. Inner and outer row fields
have different attributes. Items in the outermost field are displayed only once,
but items in the rest of the fields are repeated as needed.
Column field
A field that's assigned a column orientation in a PivotTable report. In
the example opposite, Items is a column
field
with each Item displayed across the PivotTable. A PivotTable
report can have multiple column fields just as it can have multiple row fields.
Most indented format PivotTable reports do not have column fields.
Item
A subcategory, or member, of a PivotTable field. In the first example,
Shirts and Trousesr are items in the Product field. Items represent unique
entries in the same field, or column, in the source data. Items appear as row or
column labels or in the drop-down tables for page fields.
Page field
A field that's assigned to a page, or filter, orientation. In the example, Qtr
is a page field that you can use to filter the report by quarters. With the Qtr
field, you can display summarized data for only the irst region, for only the
second qurter, or for other quarters. When you click a different item in a page
field, the entire PivotTable report changes to display only the
summarized data associated with that item.
Page field item
Each unique entry or value from the field, or column, in the source table or
table becomes an item in the page field table. In the example, All is the
currently selected item for the Qtr page field, and the PivotTable report
displays the summarized data for only the all Qtrs, but the drop down allows
individual items.
Data field
A field from a source table or database that contains data to be summarized. In
the example, Sum of #Sold is a data field that summarizes the entries from the
#Sold field, or column, in the source data. A data field usually summarizes
numeric data, such as statistics or sales figures, but the underlying data can
also be text. By default, Microsoft Excel summarizes text data in PivotTable
reports by using the Count summary function and summarizes numeric data
by using Sum.
Data area
The part of a PivotTable report that contains summary data. The cells of
the data area show summarized data for the items in the row and column fields.
Each value in the data area represents a summary of data from the source
records, or rows.
In the first example, the value in cell I8 is a summary of Total Shirt Sales —
that is, a summary of the sales figures for every record, or row, in the source
data that contains the items Shirt. In the indented-format report example, this
information is shown in cell H8.
Field drop-down arrow
The arrow at the right side of each field. Click this arrow to select the items
that you want to show. By default it is usually All.
Formatting PivotTables
Once you have created a PivotTable to your satisfaction
you can produce some effective reports by simply using the AutoFormat Dialog.
Click any cell in he PivotTable, then in the PivotTable Toolbar
click
the Format Report icon (the one with the lightning bolt). Then simply
click on any pre-formatted report that you want to use. Click OK. You can change
the format by repeating these steps if you don't like your fist choice
Some examples of how a PivotTable can be formatted
The sample workbook can be downloaded here