Curtis Frye’s Microsoft Excel 2010 Step by Step is now available!
You can find the book’s Table of Contents and very detailed Introduction describing the book’s coverage here.
Today we’d like to share an extended excerpt from the book.
By the way, via our ongoing anonymous customer survey we received many comments saying that accessing the practice files on the companion disc that came along with Microsoft Office Excel 2007 Step by Step was difficult. So we’ve moved the practice files to the Web. Simply click the “Practice Files” link in the left margin at the book’s catalog page.
OK, on to our excerpt.
Each chapter in our Step by Step series begins with a graphical snapshot of the chapter’s most important topics:
Each chapter section covers a topic and then ends with a hands-on exercise (set off by “SET UP” and “CLEAN UP”).
Creating Dynamic Worksheets by Using PivotTables
In this chapter, you will learn how to
- Analyze data dynamically by using PivotTables.
- Filter, show, and hide PivotTable data.
- Edit PivotTables.
- Format PivotTables.
- Create PivotTables from external data.
When you create Microsoft Excel 2010 worksheets, you must consider how you want the
data to appear when you show it to your colleagues. You can change the formatting of
your data to emphasize the contents of specific cells, sort and filter your worksheets based
on the contents of specific columns, or hide rows containing data that isn’t relevant to the
point you’re trying to make.
One limitation of the standard Excel worksheet is that you can’t easily change how
the data is organized on the page. For example, in a worksheet in which each column
represents an hour in the day, each row represents a day in a month, and the body of
the worksheet contains the total sales for every hourly period of the month, you can’t
change the worksheet quickly so that it displays only sales on Tuesdays during the
There is an Excel tool with which you can create worksheets that can be sorted, filtered,
and rearranged dynamically to emphasize different aspects of your data. That tool is the
In this chapter, you’ll learn how to create and edit PivotTables from an existing worksheet,
focus your PivotTable data using filters and Slicers, format PivotTables, and
create a PivotTable with data imported from a text file.
Practice Files Before you can complete the exercises in this chapter, you need to copy
the book’s practice files to your computer. The practice files you’ll use to complete the
exercises in this chapter are in the Chapter09 practice file folder. A complete list of
practice files is provided in “Using the Practice Files” at the beginning of this book.
Analyzing Data Dynamically by Using PivotTables
With Excel worksheets you can gather and present important data, but the standard worksheet
can’t be changed from its original configuration easily. As an example, consider a
worksheet that records monthly package volumes for each of nine distribution centers in
the United States.
Troubleshooting The appearance of buttons and groups on the ribbon changes depending
on the width of the program window. For information about changing the appearance of
the ribbon to match our screen images, see “Modifying the Display of the Ribbon” at the
beginning of this book.
The data in the worksheet is organized so that each row represents a distribution center
and each column represents a month of the year. When presented in this arrangement,
the monthly totals for all centers and the yearly total for each distribution center are
given equal billing: neither set of totals stands out.
Such a neutral presentation of your data is versatile, but it has limitations. First, although
you can use sorting and filtering to restrict the rows or columns shown, it’s difficult to
change the worksheet’s organization. For example, in this worksheet, you can’t easily
reorganize the contents of your worksheet so that the months are assigned to the rows
and the distribution centers are assigned to the columns.
The Excel tool to reorganize and redisplay your data dynamically is the PivotTable. You
can create a PivotTable, or dynamic worksheet, that enables you to reorganize and filter
your data on the fly. For instance, you can create a PivotTable with the same layout as
the worksheet described previously, which emphasizes totals by month, and then change
the PivotTable layout to have the rows represent the months of the year and the columns
represent the distribution centers. The new layout emphasizes the totals by regional
To create a PivotTable, you must have your data collected in a list. Excel tables mesh
perfectly with PivotTable dynamic views; not only do Excel tables have a well-defined
column and row structure, but the ability to refer to an Excel table by its name also
greatly simplifies PivotTable creation and management.
In the Excel table used to create the distribution PivotTable, each row of the table contains
a value representing the distribution center, date, month, week, weekday, day, and
volume for every day of the years 2009 and 2010.
Excel needs that data when it creates the PivotTable so that it can maintain relationships
among the data. If you want to filter your PivotTable so that it shows all package
volumes on Thursdays in January, for example, Excel must be able to identify January 11
as a Thursday.
After you create an Excel table, you can click any cell in the table, display the Insert tab
and then, in the Tables group, click PivotTable to open the Create PivotTable dialog box.
In this dialog box, you verify the data source for your PivotTable and whether you want to
create a PivotTable on a new worksheet or an existing worksheet. After you click OK, Excel
displays a new or existing worksheet and displays the PivotTable Field List task pane.
Tip You should always place your PivotTable on its own worksheet to avoid cluttering the
If the PivotTable Field List task pane isn’t visible, you can display it by clicking any cell in
the PivotTable to display the PivotTable Tools contextual tabs. On the Options contextual
tab, in the Show/Hide group, click Field List.
To assign a field, or column of data, to an area of the PivotTable, drag the field header
from the Choose Fields To Add To Report area at the top of the PivotTable Field List task
pane to the Drag Fields Between Areas Below area at the bottom of the task pane. For
example, if you drag the Volume field header to the Values area, the PivotTable displays
the total of all entries in the Volume column.
It’s important to note that the order in which you enter the fields in the Row Labels
and Column Labels areas affects how Excel organizes the data in your PivotTable. As an
example, consider a PivotTable that groups the PivotTable rows by distribution center
and then by month.
The same PivotTable data could also be organized by month and then by distribution
In the preceding examples, all the field headers are in the Row Labels area. If you drag
the Center header from the Row Labels area to the Column Labels area, the PivotTable
reorganizes (pivots) its data to form a different configuration.
To pivot a PivotTable, you drag a field header to a new position in the PivotTable Field List
task pane. As you drag a field within the task pane, Excel displays a blue line in the interior
of the target area so you know where the field will appear when you release the left mouse
button. If your data set is large or if you based your PivotTable on a data collection on
another computer, it might take some time for Excel to reorganize the PivotTable after a
pivot. You can have Excel delay redrawing the PivotTable by selecting the Defer Layout
Update check box in the lower-left corner of the PivotTable Field List task pane. When
you’re ready for Excel to display the reorganized PivotTable, click Update.
If you expect your PivotTable source data to change, such as when you link to an external
database that records shipments or labor hours, you should ensure that your PivotTable
summarizes all the available data. To do that, you can refresh the PivotTable connection
to its data source. If Excel detects new data in the source table, it updates the PivotTable
contents accordingly. To refresh your PivotTable, click any cell in the PivotTable and then,
on the Options contextual tab, in the Data group, click Refresh.
In this exercise, you’ll create a PivotTable by using data from a table, add fields to
the PivotTable, and then pivot the PivotTable.
SET UP You need the Creating_start workbook located in your Chapter09 practice
file folder to complete this exercise. Start Excel, open the Creating_start workbook,
and save it as Creating. Then follow the steps.
1. Click any cell in the Excel table.
2. On the Insert tab, in the Tables group, click the PivotTable button (not the arrow).
The Create PivotTable dialog box opens.
3. Verify that the DailyVolumes table name appears in the Table/Range field and
that the New Worksheet option is selected.
4. Click OK.
Excel creates a PivotTable on a new worksheet.
5. In the PivotTable Field List task pane, drag the Center field header to the Row
Excel adds the Center field values to the PivotTable row area.
6. In the PivotTable Field List task pane, drag the Year field header to the Column
Excel adds the Year field values to the PivotTable column area.
7. In the PivotTable Field List task pane, drag the Volume field header to the
Excel fills in the body of the PivotTable with the Volume field values.
8. In the PivotTable Field List task pane, in the Column Labels area, drag the
Year field header to the Row Labels area, and drop it beneath the Center field
Excel changes the PivotTable to reflect the new organization.
CLEAN UP Save the Creating workbook, and then close it.
Enjoy the book! And give us feedback via the link given earlier in this post. We’ll use your comments to improve our offerings.