1

Tap the Power of the PivotTable

Master Excels Greatest Reporting Tool
2
T
a
b
l
e

o
f

C
o
n
t
e
n
t
s
Quit struggling with data analysis and start saving time
using PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Specify what your PivotTable displays in place of empty cells and errors. . . . . . . . . . . . . . . . . . . . . . 7
Consolidate the minute details: 3 ways to group data in a PivotTable. . . . . . . . . . . . . . . . . . . . . . . . . 8
Let Excel format your PivotTable so you can concentrate on the data. . . . . . . . . . . . . . . . . . . . . . . . 12
Increase your PivotTables power with error-free custom calculations . . . . . . . . . . . . . . . . . . . . . . . 13
Combine widespread data into a PivotTable and keep its original
location intact . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16
Piece together a PivotTable from several locations without
sacricing exibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
Dont let PivotTable changes mess up your formula references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23
Leave static charts behindvisualize data on the y with a PivotChart . . . . . . . . . . . . . . . . . . . . .27
Always creating the same PivotTable? Do it in seconds with a macro . . . . . . . . . . . . . . . . . . . . . . . . 31
Take your programmatic PivotTable to the next level with formatting
and structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Copyright
2007 Eli Journals. This work is an independently produced publication of Eli Research, the Content of which is the property of Eli Research or
its afliates or third-party licensors and which is protected by copyright law in the United States and elsewhere. The right to copy and publish the
Content is reserved, even for Content made available for free, such as sample articles, tips, and graphics, none of which may be copied in whole or
in part or further distributed in any form or medium without the express written permission of Eli Research. Requests for permission to copy or re-
publish any Content may be directed to Holly Smith at (800) 508-1316 or via email at holly@medville.com. All product names or services identied
throughout this journal are trademarks or registered trademarks of their respective companies. Have information on copyright violations? Call us!
Well share 25% of the net proceeds of all awards related to copyright infringement that you bring to our attention. Direct your condential inquiry to
Holly Smith at (800) 508-1316.
PDFIMEPIVO
3
A
t

a

G
l
a
n
c
e
Quit struggling with data analysis and start saving time
using PivotTables
by Kara Soos
Application:
Microsoft Excel 97/2000/2002/
v. X/2003
Operating Systems:
Microsoft Windows, Macintosh
It isnt necessary to rearrange the same data into different worksheets
whenever you need to change the angle of your analysis. Instead, Pivot-
Tables give you the ability to change the way you analyze your data on the
ywithout re-entering any of it.
To introduce you to PivotTables, well:
Prepare data for conversion to a PivotTable.
Use the PivotTable And PivotChart Wizard to create a simple PivotTable.
Illustrate the anatomy of a PivotTable and its main components.
Often, databases contain extraneous information
that makes data analysis confusing. For example,
your spreadsheet may contain product informa-
tion, including price and quarterly gross income,
for each company branch in three countries. If you
only want to see this quarters net gross income for
one product in one country, it may feel like trying
to nd a needle in a haystack. The situation only
gets more complicated when you want to analyze
the same data in a different way. This is where
PivotTables can save you a lot of work, as shown in
Figure A; weve arranged the same data in four ways
with a PivotTable.
Get ready for the leap to PivotTables
PivotTables intimidate many Excel users because
they look complicated and overwhelming. How-
ever, once you prepare your data, you can use a
wizard to create a PivotTable and make it work
for you.
Prepare your data for a successful PivotTable
There are a few things to keep in mind when youre
thinking about creating a PivotTable from a data
table. Your data must conform to the following
prerequisites:
Include descriptive column headings to identify
the data in a column.
Eliminate empty rows or columns so your data is
contiguous.
Ensure columns of data contain only one type of
data (e.g., only text or only numerical).
Remove any calculations, such as totals or sub-
totals, since the PivotTable performs these calcu-
lations for you.
These requirements are simple, and most likely,
your data already meets them. You can use our
sample worksheet, as shown in Figure B, as a guide,
or you can download and extract the le Pivot-
Tables.xls from the URL given at the beginning of
the article.
A: You can rearrange your
data in several ways once
you format it as a PivotTable,
making data analysis a
breeze.
Download:
http://download.elijournals.com/
excelfund/200505/PivotTables.zip
4
Use the PivotTable And PivotChart
Wizard
The easiest way to create a PivotTable is to launch
the PivotTable And PivotChart Wizard. Not only
does the Wizard simplify the creation of your Piv-
otTable, but it also gives you a taste of what Pivot-
Tables can do.
To launch the PivotTable And PivotChart Wizard:
1. Select any cell within the data range youre
including in your PivotTable.
2. Choose Data | PivotTable And PivotChart
Report (Data | PivotTable Report in 97/v. X)
from the menu bar.
To work through the PivotTable And PivotChart Wizard:
1. In the Step 1 Of 3 window, leave the Microsoft
Ofce Excel List Or Database option button
(Microsoft Excel List Or Database option but-
ton in 97/2000/2002/v. X) selected.
2. Ensure the PivotTable option button is
selected. (This isnt available in versions
97/v. X.) Click Next.
3. In the Step 2 Of 3 window, check the range
Excel selects and ensure that it includes all
of the data in your data table, as shown in
Figure C.
4. Adjust the selection border, if necessary, by
entering a different range in the windows text
box or by manually selecting the range with
your mouse pointer.
5. Click Next to advance to the nal screen of the
Wizard.
6. In the Step 3 Of 3 window, select the New
Worksheet option button and click Finish.
Note: If a message box appears with some information on
using less memory during the PivotTable And PivotChart
Wizard, just skim it over and then dismiss it.
For our PivotTable, we didnt stray from Excels
default settings. However, you probably noticed
the other options available in the Wizard. You can
create a PivotTable from external data, multiple
consolidation ranges, or even another PivotTable.
Also, you can insert the PivotTable in a new work-
sheet, or in any location in your current worksheet.
B: The data in our sample worksheet is ready to become a PivotTable.
C: Excel automatically selects the data thats contiguous with the
active cell.
Excel 97s limitations and differences
Excel 97 doesnt offer PivotCharts, and its PivotTables oper-
ate differently. The wizard has an extra dialog box that
prompts you to create the layout of your PivotTable. After
you click and drag your column headings to the chart areas,
it continues with the wizard. Once youve created the Pivot-
Table, theres no PivotTable Field List to easily change your
PivotTables arrangement. Instead, you have to click the
PivotTable button on the toolbar and choose Wizard from
the menu, which takes you to the wizards layout dialog box.
These differences give Excel 97s PivotTables less exibility
than those in later versions.