Excel PivotTables and charts

289 Pages·2006·12.07 MB·english

by  Peter G. Aitken

01_772402 ffirs.qxd 11/30/05 9:37 PM Page i
Excel® PivotTables
and Charts
Peter G. Aitken
02_772402 ftoc.qxd 11/30/05 9:49 PM Page x
01_772402 ffirs.qxd 11/30/05 9:37 PM Page i
Excel® PivotTables
and Charts
Peter G. Aitken
01_772402 ffirs.qxd 11/30/05 9:38 PM Page ii
Excel®PivotTables and Charts
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN-13: 978-0-471-77240-8
ISBN-10: 0-471-77240-2
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1MA/RZ/RS/QV/IN
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the
1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment
of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone],
fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,
Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at http://www.
wiley.com/go/permissions.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO
REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE
CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR
EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN
MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE
PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF
PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON
SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES
ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A
CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR
OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR
RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES
LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN
AND WHEN IT IS READ.
For general information on our other products and services or to obtain technical support, please contact our Customer Care
Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].
Library of Congress Cataloging-in-Publication Data
Aitken, Peter.
Excel Pivot tables and charts / Peter Aitken.
p. cm.
Includes index.
ISBN-13: 978-0-471-77240-8 (paper/website)
ISBN-10: 0-471-77240-2 (paper/website)
1. Microsoft Excel (Computer file) 2. Business—Computer programs. I. Title.
HF5548.4.M523A455 2005
005.54—dc22
2005029714
Trademarks:Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates,
in the United States and other countries, and may not be used without written permission. Excel is a registered trademark of
Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective
owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
01_772402 ffirs.qxd 11/30/05 9:38 PM Page iii
About the Author
Peter Aitken has been writing about computers and software for 20 years and has some
45 books as well as hundreds of magazine and web articles to his credit. He specializes in
Excel, Visual Basic programming, and XML, but he has also ventured into such varied top-
ics as personal finance, digital imaging, and camera phones. Peter also does technical
writing in the medical and pharmaceutical fields and is a part-time faculty member at
Duke University Medical Center.
Acknowledgments
An author does not create a book all alone—along the way are many helping hands that
are essential to the process. My thanks to all of these talented people: Katie Mohr, my
acquisitions editor; Mike Talley, who reviewed the book for technical accuracy; and Sydney
Jones, my developmental editor.
Credits
Acquisitions Editor:Katie Mohr Project Coordinator:Ryan Steffen
Development Editor: Sydney Jones Graphics and Production Specialists:
Sean Decker, Denny Hager,
Technical Editor:Mike Talley
Jennifer Heleine, Heather Pope,
Production Editor:Kenyon Brown
Melanee Prendergast, Barbara Moore
Copy Editor:S. B. Kleinman
Quality Control Technician:David Faust,
Editorial Manager:Mary Beth Wakefield Joseph Niesen
Production Manager:Tim Tate Proofreading: Susan Sims
Vice President and Executive Group Indexing:TECHBOOKS Production Services
Publisher:Richard Swadley
Vice President and Executive Publisher:
Joseph B. Wikert
02_772402 ftoc.qxd 11/30/05 9:49 PM Page iv
Contents at a Glance
Part I: Understanding PivotTables and Charts 1
Part II: Understanding Data Sources for PivotTables 15
Part III: Using PivotTable Tools and Formatting 59
Part: IV: Working with PivotTable Components 77
Part V: More About PivotTable Components 111
Part VI: Understanding and Using PivotCharts 137
Part VII: Using PivotTables with Multidimensional Data 159
Part VIII: Getting Hard Data from a PivotTable 183
Part IX: PivotTable Alternatives 197
Part X: Programming PivotTables with VBA 211
Appendix A: Troubleshooting PivotTables and PivotCharts 237
Appendix B: Excel Version Differences for PivotTables 241
Appendix C: An Excel Chart Primer 245
Index 263
02_772402 ftoc.qxd 11/30/05 9:49 PM Page v
Contents
Part I: Understanding PivotTables and Charts 1
Tips and Where to Find Them 2
Understanding How PivotTables Work 3
Working with PivotTables 4
Creating a PivotTable Report 5
Creating a PivotTable Report with Multiple Columns 9
Creating a PivotChart 12
Part II: Understanding Data Sources for PivotTables 15
Tips and Where to Find Them 16
Using Excel Data from the Same Workbook 17
Using Excel Data from Another Workbook 20
Using Data from Other Sources 23
Using Data from Databases 25
Existing Data Source 25
XXXX Database 27
<New Data Source> 28
Queries 30
OLAP Cubes 30
Creating a PivotTable Report from Data in an Access Database 31
Using Other External Data Sources 39
Using Multiple Consolidation Ranges 39
Letting Excel Create a Single Page Field 42
Creating Your Own Page Fields 44
Creating a Single-Page Field PivotTable Report from Multiple Consolidation Ranges 47
Creating a Multiple-Page Field PivotTable Report from Multiple Consolidation Ranges 50
Basing a PivotTable on Another PivotTable Report 54
Creating a PivotTable with Inner and Outer Row Fields 54
Part III: Using PivotTable Tools and Formatting 59
Tips and Where to Find Them 60
Using the Layout Dialog Box in the PivotTable and PivotChart Wizard 61
Understanding the PivotTable Toolbar 63
Customizing the PivotTable Toolbar 63
02_772402 ftoc.qxd 11/30/05 9:49 PM Page vi
vi
Contents
Using the PivotTable Menu 64
Setting PivotTable Options 65
Format Options 66
Data Options 68
Formatting PivotTables 69
Autoformatting a PivotTable 70
Changing the Number Format 72
Changing Other Formats 73
Applying Formatting to a PivotTable Report 74
Part IV: Working with PivotTable Components 77
Tips and Where to Find Them 78
Using Drop Areas 79
The Field List 79
Dragging a Field Off a PivotTable 80
Using the Row Drop Area 80
Using Multiple Row Fields 82
Using the Column Drop Area 84
Creating a PivotTable with Two Column Fields and Two Row Fields 85
Using the Data Drop Area 87
Using Multiple Data Fields 88
Using the Page Drop Area 90
Creating a PivotTable with Three Page Fields 91
Working with Field Settings 94
Using Settings for Data Fields 95
Using Different Summary Functions 96
Working with Settings for Row and Column Fields 99
Advanced Row and Column Field Settings 100
Field Layout Settings 100
Working with Settings for Page Fields 102
Understanding Advanced Field Settings 103
Page Field Options 103
Sort Options 104
Using the Sort and AutoShow Options 105
Setting Advanced Data Field Options 107
Part V: More About PivotTable Components 111
Tips and Where to Find Them 112
Working with Calculated Fields and Items 113
Calculated Fields 113
Creating and Using a Calculated Field 114
02_772402 ftoc.qxd 11/30/05 9:49 PM Page vii
vii
Contents
Working with Calculated Items 119
Creating and Using a Calculated Item 120
Showing and Hiding Detail 124
Viewing Detail for Data Items 124
Viewing Details for Field Items 124
Viewing Detail in OLAP Reports 126
Grouping PivotTable Items 128
Grouping Numeric Items 128
Grouping Dates 130
Grouping Other Items 132
Grouping Category Data 132
Part VI: Understanding and Using PivotCharts 137
Tips and Where to Find Them 138
Understanding PivotCharts 139
Creating a PivotChart 139
Create a PivotChart from an Existing PivotTable 139
Create a PivotChart from Scratch 139
Creating a PivotChart from Scratch 141
Understanding the Parts of a Chart 144
Working with the PivotChart Menu 145
Understanding and Changing PivotChart Types 146
Understanding a PivotChart’s Structure 148
A Simple PivotChart 148
A PivotChart with Two Row Fields 149
A PivotChart with Two Column Fields 152
Creating a 3-D PivotChart 153
Part VII: Using PivotTables with Multidimensional Data 159
Tips and Where to Find Them 160
Using Multidimensional Data 161
Understanding Multidimensional Data 162
Flat Data 162
Relational Data 162
Multidimensional Data 164
Where Are Multidimensional Data Stored? 170
Multidimensional Data Terminology 171
Creating a Cube File 172
Creating a PivotTable from a Cube File 179
Working with OLAP PivotTables 182