<< . .

. 41
( : 51)



. . >>

list) or an external database file. Although Excel can generate a pivot table from any
database, not all databases benefit.
Generally speaking, fields in a database table can consist of two types:
¦ Data: Contains a value or data to be summarized. In Figure 20-1, the Amount field
is a data field.
¦ Category: Describes the data. In Figure 20-1, the Date, AcctType, OpenedBy,
Branch, and Customer fields are category fields because they describe the data in
the Amount field.
A single database table can have any number of data fields and category fields. When you
create a pivot table, you usually want to summarize one or more of the data fields.
Conversely, the values in the category fields appear in the pivot table as rows, columns, or
pages.
Exceptions exist, however, and you may find Excel™s pivot table feature useful even for
databases that don™t contain actual numerical data fields. The database columns A:C in
Figure 20-4, for example, don™t contain any numerical data, but you can create a useful pivot
table that counts the items in fields rather than sums them. The pivot table cross-tabulates
the Month Born field by the Sex field; the intersecting cells show the count for each
combination of month and gender.




Figure 20-4: This database doesn™t have any numerical fields, but you can use it to
generate a pivot table.
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 473


Pivot Table Terminology

Understanding the terminology associated with pivot tables is the first step in mastering this
feature. Refer to the accompanying figure to get your bearings.




¦ Column field: A field that has a column orientation in the pivot table. Each item in the field
occupies a column. In the figure, Customer represents a column field that contains two items
(Existing and New). You can have nested column fields.
¦ Data area: The cells in a pivot table that contain the summary data. Excel offers several
ways to summarize the data (sum, average, count, and so on). In the figure, the Data area
includes C5:E20.
¦ Grand totals: A row or column that displays totals for all cells in a row or column in a pivot
table. You can specify that grand totals be calculated for rows, columns, or both (or neither).
The pivot table in the figure shows grand totals for both rows and columns.
¦ Group: A collection of items treated as a single item. You can group items manually or
automatically (group dates into months, for example). The pivot table in the figure does not
have any defined groups.
¦ Item: An element in a field that appears as a row or column header in a pivot table. In the
figure, Existing and New are items for the Customer field. The Branch field has three items:
Central, North County, and Westside. AcctType has four items: CD, Checking, IRA (Invest-
ment Retirement Account), and Savings.
¦ Page field: A field that has a page orientation in the pivot table ” similar to a slice of a
three-dimensional cube. You can display only one item (or all items) in a page field at one
time. In the figure, OpenedBy represents a page field that displays the New Accts item.
¦ Refresh: To recalculate the pivot table after making changes to the source data.

Continued
Part III ¦ Beyond Mastery: Initiative within Office
474


Continued
¦ Row field: A field that has a row orientation in the pivot table. Each item in the field occu-
pies a row. You can have nested row fields. In the figure, Branch and AcctType both repre-
sent row fields.
¦ Source data: The data used to create a pivot table. It can reside in a worksheet or an
external database.
¦ Subtotals: A row or column that displays subtotals for detail cells in a row or column in a
pivot table. The pivot table in the figure displays subtotals for each branch.


Creating a Pivot Table
You create a pivot table by using a series of steps presented in the PivotTable and PivotChart
Wizard. You access this wizard by choosing Data _ PivotTable and PivotChart Report.
Then, carry out the steps outlined here.

This discussion assumes that you use Excel 2000 or later. The procedure differs slightly in
Note
earlier versions of Excel.


Step1: Specifying the data location
After you choose Data _ PivotTable and PivotChart Report, you see the dialog box shown
in Figure 20-5.




Figure 20-5: The first of three PivotTable and PivotChart Wizard dialog boxes.
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 475

In this step, you identify the data source. Excel is quite flexible in the data that you can use
for a pivot table. (See the nearby sidebar, “Pivot Table Data Sources.”) This example uses a
worksheet database.

You see different dialog boxes while you work through the wizard, depending on the location of
Note
the data that you want to analyze. The following sections present the wizard™s dialog boxes for
data located in an Excel list or database.



Pivot Table Data Sources
The data used in a pivot table can come from a variety of sources, including Excel databases or
lists, data sources external to Excel, multiple tabled ranges, and other pivot tables. These sources
are described here.

Microsoft Excel List or Database
Usually, the data that you analyze is stored in a worksheet database (also known as a list).
Databases stored in a worksheet have a limit of 65,535 records and 256 fields. Working with a
database of this size isn™t efficient, however (and memory may not even permit it). The first row in
the database should contain field names. No other rules exist. The data can consist of values, text,
or formulas.

External Data Source
If you use the data in an external database for a pivot table, use Query (a separate application) to
retrieve the data. You can use dBASE files, SQL Server data, or other data that your system is set
up to access. Step 2 of the PivotTable and PivotChart Wizard prompts you for the data source.
Note that in Excel 2000 or later, you also can create a pivot table from an OLAP (OnLine Analytical
Processing) database.

Multiple Consolidation Ranges
You also can create a pivot table from multiple tables. This procedure is equivalent to consolidat-
ing the information in tables. When you create a pivot table to consolidate information in tables,
you have the added advantage of using all of the pivot table tools while working with the consoli-
dated data.

Another Pivot Table Report or Pivot Chart Report
Excel enables you to create a pivot table from an existing pivot table or pivot chart. Actually, this is
a bit of a misnomer. The pivot table that you create is based on the data that the first pivot table
uses (not the pivot table itself). If the active workbook has no pivot tables, this option is grayed ”
meaning you can™t choose it. If you need to create more than one pivot table from the same set of
data, the procedure is more efficient (in terms of memory usage) if you create the first pivot table
and then use that pivot table as the source for subsequent pivot tables.
Part III ¦ Beyond Mastery: Initiative within Office
476


Step 2: Specifying the data
To move on to the next step of the wizard, click the Next button. Step 2 of the PivotTable
and PivotChart Wizard prompts you for the data. Remember that the dialog box varies,
depending on your choice in the first dialog box; Figure 20-6 shows the dialog box that
appears when you select an Excel list or database in Step 1.




Figure 20-6: In Step 2, you specify the data range.

If you place the cell pointer anywhere within the worksheet database when you select Data
_ PivotTable Report, Excel identifies the database range automatically in Step 2 of the
PivotTable and PivotChart Wizard.
You can use the Browse button to open a different worksheet and select a range. To move on
to Step 3, click the Next button.

If the source range for a pivot table is named Database, you can use Excel™s built-in Data Form
Tip to add new data to the range. The named range will extend automatically to include the new
records. In addition, if you create the pivot table from a list (designated by using the Data _ List
_ Create List command), the pivot table will be linked to the list. Therefore, the pivot table will
be accurate if the list shrinks or grows.


Step 3: Completing the pivot table
Figure 20-7 shows the dialog box for the final step of the PivotTable and PivotChart Wizard.
In this step, you specify the location for the pivot table.




Figure 20-7: In Step 3, you specify the pivot table™s location.
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 477

If you select the New Worksheet option, Excel inserts a new worksheet for the pivot table. If
you select the Existing Worksheet option, the pivot table appears on the current worksheet.
(You can specify the starting cell location.)
At this point, you can click the Options button to select some options that determine how the
table appears. (Refer to the nearby sidebar “Pivot Table Options.”) You can set these options
at any time after you create the pivot table, so you do not need to do so before creating the
pivot table.
You can set up the actual layout of the pivot table by using either of two techniques:
¦ By clicking the Layout button in Step 3 of the PivotTable and PivotChart Wizard.
You then can use a dialog box to lay out the pivot table.
¦ By clicking the Finish button to create a blank pivot table. You then can use the
PivotTable Field List toolbar to lay out the pivot table.
Both of these options are described in the following subsections.

Using a dialog box to lay out a pivot table
When you click the Layout button of the wizard™s last dialog box, you get the dialog box
shown in Figure 20-8. The fields in the database appear as buttons along the right side of the
dialog box. Simply drag the buttons to the appropriate area of the pivot table diagram (which
appears in the center of the dialog box).




Figure 20-8: Specify the table layout.

The pivot table diagram has four areas:
¦ Page: Buttons in this area appear as page items in the pivot table.
¦ Row: Buttons in this area appear as row items in the pivot table.
¦ Data: Buttons in this area indicate the data that is summarized in the pivot table.
¦ Column: Buttons in this area appear as column items in the pivot table.
Part III ¦ Beyond Mastery: Initiative within Office
478

You can drag as many field buttons as you want to any of these locations, and you don™t have
to use all the fields. Any fields that you don™t use simply don™t appear in the pivot table.
When you drag a field button to the Data area, the PivotTable and PivotChart Wizard applies
the Sum function if the field contains numeric values; it applies the Count function if the
field contains non-numeric values.
While you set up the pivot table, you can double-click a field button to customize it. You can
specify, for example, to summarize a particular field as a Count or other function. You also
can specify which items in a field to hide or omit. If you drag a field button to an incorrect
location, just drag it off the table diagram to get rid of it. Note that you can customize fields
at any time after you create the pivot table.
Figure 20-9 shows how the dialog box looks after dragging some field buttons to the pivot
table diagram. This pivot table displays the sum of the Amount field, broken down by
AcctType (as rows) and Customer (as columns). In addition, the Branch field appears as a page
field. Click OK to redisplay the PivotTable and PivotChart Wizard ” Step 3 of the dialog box.




Figure 20-9: The table layout after dragging field buttons to the pivot table diagram.


Using the PivotTable Field List toolbar to lay out a pivot table
You may prefer to lay out your pivot table directly in the worksheet by using the PivotTable
Field List toolbar. The technique closely resembles the one just described because you still
drag and drop fields. But in this case, you drag fields from the toolbar into the worksheet.

You cannot use this technique with versions prior to Excel 2000. Also, note that Excel 2000
Note
doesn™t have a PivotTable Field List toolbar. Rather, the fields are displayed as buttons on the
PivotTable toolbar.

Complete the first two steps of the PivotTable and PivotChart Wizard. If you want, set options
for the pivot table by using the Options button that appears in the third dialog box of the
wizard. Don™t bother with the Layout button, however. Select a location for the pivot table and
choose Finish. Excel displays a pivot table template similar to the one you see in Figure 20-10.
The template provides you with hints about where to drop various types of fields.
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 479




Figure 20-10: Use the PivotTable Field List toolbar to drag and drop fields onto the pivot
table template that Excel displays.

Drag and drop fields from the PivotTable Field List toolbar onto the template. Or select the
field name, choose the location from the drop-down list, and click the Add To button. Excel
continues to update the pivot table as you add or remove fields. For this reason, you™ll find
this method easiest to use if you drag and drop data items last. In other words, set up the
field items and then specify the data to summarize.
If you make a mistake, simply drag the field off the template and drop it on the worksheet ”
Excel removes it from the pivot table template. All fields remain on the PivotTable Field
List toolbar, even if you use them.

The finished product
Figure 20-11 shows the result of this example. Notice that the page field displays as a drop-
down box. You can choose which item in the page field to display by choosing it from the
list. You also can choose an item called All, which displays all the data.




Figure 20-11: The pivot table created by the PivotTable and PivotChart Wizard.
Part III ¦ Beyond Mastery: Initiative within Office
480


Pivot Table Options
Excel provides plenty of options that determine how your pivot table looks and works. To access
these options, click the Options button in the final step of the PivotTable and PivotChart Wizard to
display the PivotTable Options dialog box. You also can access this dialog box after you create the
pivot table. Right-click any cell in the pivot table and then select Table Options from the shortcut
menu. The accompanying figure shows the PivotTable Options dialog box.




Here are its choices:
¦ Name: You can provide a name for the pivot table. Excel provides default names in the form
of PivotTable1, PivotTable2, and so on.
¦ Grand Totals for Columns: Check this box if you want Excel to calculate grand totals for
items displayed in columns.
¦ Grand Totals for Rows: Check this box if you want Excel to calculate grand totals for items
displayed in rows.
¦ AutoFormat Table: Check this box if you want Excel to apply one of its AutoFormats to the
pivot table. The selected AutoFormat sticks with the pivot table, even If you rearrange the
table layout.
¦ Subtotal Hidden Page Items: Check this box if you want Excel to include hidden items in
the page fields in the subtotals.
¦ Merge Labels: Check this box if you want Excel to merge the cells for outer row and column
labels. Doing so may make the table more readable.
¦ Preserve Formatting: Check this box if you want Excel, when it updates the pivot table, to
keep any of the formatting that you applied.
¦ Repeat Item Labels on Each Printed Page: Check this box to set row titles that appear on
each page when you print a pivot table report.
Continued
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 481


Continued
¦ Mark Totals with: Available only if you generated the pivot table from an OLAP data source.
If checked, displays an asterisk after every subtotal and grand total to indicate that these
values include any hidden items as well as displayed items.
¦ Page Layout: You can specify the order in which you want the page fields to appear.
¦ Fields per Column: You can specify the number of page fields to show before starting
another row of page fields.
¦ For Error Values, Show: You can specify a value to show for pivot table cells that display
an error.
¦ For Empty Cells, Show: You can specify a value to show for empty pivot table cells.
¦ Set Print Titles: Check this box to set column titles that appear at the top of each page
when you print a PivotTable report.
¦ Save Data with Table Layout: If you check this option, Excel stores an additional copy of
the data (called a pivot table cache), which is stored with the workbook. If this option is not
enabled, then Excel must refresh the pivot table with the file is opened.
¦ Enable Drill to Details: If checked, you can double-click a cell in the data area of the pivot
table to view the records that contributed to the summary value.
¦ Refresh on Open: If checked, the pivot table refreshes whenever you open the workbook.
¦ Refresh Every x Minutes: If you are connected to an external database, you can specify
how often you want the pivot table refreshed while the workbook is open.
¦ Save Password: If you use an external database that requires a password, you can store
the password as part of the query so that you don™t have to reenter it.
¦ Background Query: If checked, Excel runs the external database query in the background
while you continue your work.
¦ Optimize Memory: This option reduces the amount of memory used when you refresh an
external database query.


Grouping Pivot Table Items
One of the more useful features of a pivot table is the ability to combine items into groups.
To group items, select them, right-click, and choose Group and Outline _ Group from the
shortcut menu that appears.
When a field contains dates, Excel can create groups automatically. Figure 20-12 shows a
portion of a simple database table with two fields: Date and Sales. This table has 370 records
and covers dates between June 1, 2001, and October 31, 2002. The goal is to summarize the
sales information by month.
Part III ¦ Beyond Mastery: Initiative within Office
482




Figure 20-12: You can use a pivot table to summarize the sales data by month.

Figure 20-13 shows part of a pivot table created from the data. Not surprisingly, it looks
exactly like the input data because the dates have not been grouped. To group the items by
month, right-click the Data heading and select Group and Show Detail _ Group. You™ll see
the Grouping dialog box shown in Figure 20-14.

In versions prior to Excel 2002, the shortcut menu command is Group and Outline _ Group.
Note






Figure 20-13: The pivot table, before grouping by month.
Chapter 20 ¦ Analyzing Data with Pivot Tables in Excel 483




Figure 20-14: Use the Grouping dialog box to group items in a pivot table.

In the list box, select Months and Years, and verify that the starting and ending dates are
correct. Click OK. The Date items in the pivot table are grouped by years and by months (as
shown in Figure 20-15).




Figure 20-15: The pivot table, after grouping by month.


If you select only Months in the Grouping list box, months in different years combine together.
Note
For example, the June item would display sales for both 2001 and 2002.
Part III ¦ Beyond Mastery: Initiative within Office
484


Copying a Pivot Table

A pivot table is a special type of object, and you cannot manipulate it as you may expect. For
example, you can™t insert a new row or enter formulas within the pivot table. If you want to manipu-
late a pivot table in ways not normally permitted, make a copy of it.
To copy a pivot table, select the table and choose Edit _ Copy. Then activate a new worksheet
and choose Edit _ Paste Special. Select the Values option and click OK. The contents of the pivot
table are copied to the new location so you can do whatever you like to them. You also may want
to repeat the Edit _ Paste Special command and select Formats (to copy the formatting from the
pivot table).
This technique is also useful when you want to create a standard chart. If you attempt to create a
chart from a pivot table, Excel always creates a pivot chart that contains field buttons. Sometimes
you may prefer a standard chart.
Note that the copied information is no longer linked to the source data. If the source data changes,
your copied pivot table does not reflect these changes.


Creating a Calculated Field or Calculated Item
After you create a pivot table, you can create two types of calculations for further analysis:
¦ A calculated field: A new field created from other fields in the pivot table. A
calculated field must reside in the Data area of the pivot table. (You can™t use a
calculated field in the Page, Row, or Column areas.)
¦ A calculated item: A calculated item uses the contents of other items within a field
of the pivot table. A calculated item must reside in the Page, Row, or Column area
of a pivot table. (You can™t use a calculated item in the Data area.)

<< . .

. 41
( : 51)



. . >>