Excel Pivot Tables Recipe Book: A Problem-Solution Approach

336 Pages·2006·2.842 MB·English

by  Debra Dalgleish

6293ch00FM.qxd 2/1/06 5:46 PM Page i
Excel Pivot Tables Recipe Book
A Problem-Solution Approach
DEBRADALGLEISH
6293ch00FM.qxd 2/1/06 5:46 PM Page ii
Excel Pivot Tables Recipe Book: AProblem-Solution Approach
Copyright © 2006 by Debra Dalgleish
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-59059-629-6
ISBN-10 (pbk): 1-59059-629-3
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use atrademark symbol with every occurrence
of atrademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Ewan Buckingham
Technical Reviewer: Don Reamy
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Jason Gilmore,
Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Dominic Shakeshaft, Jim Sumser,
Matt Wade
Project Manager: Kylie Johnston
Copy Edit Manager: Nicole LeClerc
Copy Editor: Liz Welch
Assistant Production Director: Kari Brooks-Copony
Production Editor: Katie Stence
Compositor: Kinetic Publishing Services, LLC
Proofreader: Elizabeth Berry
Indexer: Valerie Perry
Cover Image Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA
94710. Phone [phone], fax [phone], e-mail [email protected], or visit http://www.apress.com.
The information in this book is distributed on an “as is” basis, without warranty. Although every precau-
tion has been taken in the preparation of this work, neither the author(s) nor Apress shall have any
liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly
or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.comin the Source Code section.
6293ch00FM.qxd 2/1/06 5:46 PM Page iii
Contents at a Glance
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■ CHAPTER 1 Creating a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
■ CHAPTER 2 Sorting and Grouping Pivot Table Data. . . . . . . . . . . . . . . . . . . . 39
■ CHAPTER 3 Calculations in a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
■ CHAPTER 4 Formatting a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
■ CHAPTER 5 Extracting Pivot Table Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
■ CHAPTER 6 Modifying a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
■ CHAPTER 7 Updating a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
■ CHAPTER 8 Securing a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
■ CHAPTER 9 Pivot Table Limits and Performance. . . . . . . . . . . . . . . . . . . . . . 195
■ CHAPTER 10 Publishing a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
■ CHAPTER 11 Printing a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
■ CHAPTER 12 Pivot Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
■ CHAPTER 13 Programming a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
iii
6293ch00FM.qxd 2/1/06 5:46 PM Page iv
6293ch00FM.qxd 2/1/06 5:46 PM Page v
Contents
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■ CHAPTER 1 Creating a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.1. Planning a Pivot Table:Getting Started. . . . . . . . . . . . . . . . . . . . 1
1.2. Planning a Pivot Table:Accessing the Source Data. . . . . . . . . . . . . 2
1.3. Planning a Pivot Table:Source Data Fields. . . . . . . . . . . . . . . . . . . . 3
1.4. Planning a Shared Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.5. Preparing the Source Data:Using an Excel List. . . . . . . . . . . . . . . . 5
1.6. Preparing the Source Data:Excel List Invalid Field Names . . . . . . 7
1.7. Preparing the Source Data:Using a Filtered Excel List . . . . . . . . . 7
1.8. Preparing the Source Data:Using an Excel List
with Monthly Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.9. Preparing the Source Data:Using an Excel List
with Monthly Columns and Text Fields . . . . . . . . . . . . . . . . . . . . . . 10
1.10. Preparing the Source Data:Using an Access Query . . . . . . . . . . . 11
1.11. Preparing the Source Data:Using an Access Query
with Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.12. Preparing the Source Data:Using a Text File . . . . . . . . . . . . . . . . . 12
1.13. Preparing the Source Data:Using an OLAP Cube . . . . . . . . . . . . . 12
1.14. Preparing the Source Data:Creating an OLAP Cube. . . . . . . . . . . 13
1.15. Preparing the Source Data:Using Multiple
Consolidation Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.16. Preparing the Source Data:Alternatives to Using
Multiple Consolidation Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.17. Preparing the Source Data:Setting Up Multiple
Consolidation Ranges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.18. Preparing the Source Data:Benefits of Using
Another PivotTable or PivotChart Report . . . . . . . . . . . . . . . . . . . . 14
1.19. Preparing the Source Data:Problems Caused by
Using Another PivotTable or PivotChart Report . . . . . . . . . . . . . 15
v
6293ch00FM.qxd 2/1/06 5:46 PM Page vi
vi ■ CONTENTS
1.20. Preparing the Source Data:Page Field Settings When
Using Another PivotTable or PivotChart Report. . . . . . . . . . . . . . . . 15
1.21. Connecting to the Source Data:Selecting a Large
Range in an Excel List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.22. Connecting to the Source Data:Using Arrow Keys in
an Excel List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.23. Connecting to the Source Data:Installing Drivers
for External Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
1.24. Connecting to the Source Data:Creating a New
Source for External Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
1.25. Connecting to the Source Data:Excel Hangs
When Using External Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
1.26. Connecting to the Source Data:Using Pages with
Multiple Consolidation Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.27. Understanding PivotTable Options:Table Name. . . . . . . . . . . . . . . 23
1.28. Understanding PivotTable Options:Table Naming Rules. . . . . . . . 23
1.29. Understanding PivotTable Options:Row and
Column Grand Totals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.30. Understanding PivotTable Options:AutoFormat Table. . . . . . . . . . 24
1.31. Understanding PivotTable Options:Subtotal
Hidden Page Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
1.32. Understanding PivotTable Options:Merge Labels. . . . . . . . . . . . . 25
1.33. Understanding PivotTable Options:Merge Labels
Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.34. Understanding PivotTable Options:Preserve Formatting . . . . . . . 27
1.35. Understanding PivotTable Options:Repeat Item
Labels on Each Printed Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
1.36. Understanding PivotTable Options:Page Layout . . . . . . . . . . . . . . 28
1.37. Understanding PivotTable Options:Fields per
Column/Fields per Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
1.38. Understanding PivotTable Options:Error Values. . . . . . . . . . . . . . . 29
1.39. Understanding PivotTable Options:Empty Cells. . . . . . . . . . . . . . . 30
1.40. Understanding PivotTable Options:Set Print Titles . . . . . . . . . . . . 31
1.41. Understanding PivotTable Options:Mark Totals with *. . . . . . . . . 32
1.42. Understanding PivotTable Options:Save Data
with Table Layout. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
1.43. Understanding PivotTable Options:Enable
Drill to Details. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
1.44. Understanding PivotTable Options:Refresh on Open . . . . . . . . . . 34
1.45. Understanding PivotTable Options:Refresh
Every nMinutes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
6293ch00FM.qxd 2/1/06 5:46 PM Page vii
■ CONTENTS vii
1.46. Understanding PivotTable Options:Save Password. . . . . . . . . . . . 35
1.47. Understanding PivotTable Options:Background Query. . . . . . . . . 36
1.48. Understanding PivotTable Options:Optimize Memory . . . . . . . . . 36
1.49. Understanding Pivot Table Layout. . . . . . . . . . . . . . . . . . . . . . . . . . . 37
■ CHAPTER 2 Sorting and Grouping Pivot Table Data. . . . . . . . . . . . . . . . . . . . 39
2.1. Sorting a Pivot Field:One Row Field. . . . . . . . . . . . . . . . . . . . . . . . . 39
2.2. Sorting a Pivot Field:Outer Row Field . . . . . . . . . . . . . . . . . . . . . . . 41
2.3. Sorting a Pivot Field:Inner Row Field. . . . . . . . . . . . . . . . . . . . . . . . 43
2.4. Sorting a Pivot Field:Renamed Numeric Items . . . . . . . . . . . . . . . 44
2.5. Sorting a Pivot Field:New Items Out of Order . . . . . . . . . . . . . . . . 44
2.6. Sorting a Pivot Field:Sorting Items Geographically. . . . . . . . . . . . 45
2.7. Sorting a Pivot Field:Data Source Order. . . . . . . . . . . . . . . . . . . . . 48
2.8. Sorting a Pivot Field When Some Items Won’t Sort Correctly . . . . . 48
2.9. Using Top 10 AutoShow:Specifying Top Items Overall. . . . . . . . . 49
2.10. Using Top 10 AutoShow:Specifying Items Over
a Set Amount. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
2.11. Using Top 10 AutoShow:Referring to a Cell Value. . . . . . . . . . . . . 50
2.12. Grouping:Error Message When Grouping Items
in a Date Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2.13. Grouping:Error Message When Grouping Items
in a Numeric Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
2.14. Grouping:Error Message When Grouping Items
in a Date Field with No Blanks or Text. . . . . . . . . . . . . . . . . . . . . . . 52
2.15. Grouping the Items in a Page Field. . . . . . . . . . . . . . . . . . . . . . . . . . 52
2.16. Grouping the Items in a Page Field:Using an
External Source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
2.17. Grouping:Incorrect Error Message About
Calculated Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
2.18. Grouping Text Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
2.19. Grouping Dates by Week . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
2.20. Grouping Dates by Fiscal Quarter. . . . . . . . . . . . . . . . . . . . . . . . . . . 54
2.21. Grouping Renamed Numeric Items . . . . . . . . . . . . . . . . . . . . . . . . . 54
2.22. Grouping Months. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
2.23. Grouping Dates Using the Starting Date. . . . . . . . . . . . . . . . . . . . . 55
2.24. Grouping Dates by Months and Weeks . . . . . . . . . . . . . . . . . . . . . . 56
2.25. Grouping the Items in a Pivot Table Based on
an Existing Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
2.26. Grouping Dates Outside the Range . . . . . . . . . . . . . . . . . . . . . . . . . 56
2.27. Grouping Nonadjacent Items. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
6293ch00FM.qxd 2/1/06 5:46 PM Page viii
viii ■ CONTENTS
■ CHAPTER 3 Calculations in a Pivot Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.1. Using Summary Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.2. Using Summary Functions:Default Functions. . . . . . . . . . . . . . . . 63
3.3. Using Summary Functions:Counting Blank Cells . . . . . . . . . . . . . 64
3.4. Using Custom Calculations:Difference From. . . . . . . . . . . . . . . . . 65
3.5. Using Custom Calculations:% Of . . . . . . . . . . . . . . . . . . . . . . . . . . 66
3.6. Using Custom Calculations:% Difference From . . . . . . . . . . . . . . 68
3.7. Using Custom Calculations:Running Total . . . . . . . . . . . . . . . . . . . 69
3.8. Using Custom Calculations:% of Row. . . . . . . . . . . . . . . . . . . . . . . 71
3.9. Using Custom Calculations:% of Column. . . . . . . . . . . . . . . . . . . . 72
3.10. Using Custom Calculations:% of Total . . . . . . . . . . . . . . . . . . . . . . 72
3.11. Using Custom Calculations:Index . . . . . . . . . . . . . . . . . . . . . . . . . . 73
3.12. Using Formulas:Calculated Field vs.Calculated Item . . . . . . . . . 74
3.13. Using Formulas:Adding Items with a Calculated Item . . . . . . . . . 75
3.14. Using Formulas:Modifying a Calculated Item . . . . . . . . . . . . . . . . 76
3.15. Using Formulas:Temporarily Removing
a Calculated Item. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
3.16. Using Formulas:Permanently Removing
a Calculated Item. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
3.17. Using Formulas:Using Index Numbers in
a Calculated Item. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
3.18. Using Formulas:Using Relative Position
Numbers in a Calculated Item. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
3.19. Using Formulas:Modifying a Calculated Item
Formula in a Cell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
3.20. Using Formulas:Creating a Calculated Field . . . . . . . . . . . . . . . . . 80
3.21. Using Formulas:Modifying a Calculated Field . . . . . . . . . . . . . . . . 80
3.22. Using Formulas:Temporarily Removing
a Calculated Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3.23. Using Formulas:Permanently Removing
a Calculated Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3.24. Using Formulas:Determining the Type of Formula . . . . . . . . . . . . 82
3.25. Using Formulas:Adding a Calculated Item to a Field With
Grouped Items. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
3.26. Using Formulas:Calculating the Difference
Between Plan and Actual. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
3.27. Using Formulas:Correcting the Grand Total for
a Calculated Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
3.28. Using Formulas:Counting Unique Items in
a Calculated Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
6293ch00FM.qxd 2/1/06 5:46 PM Page ix
■ CONTENTS ix
3.29. Using Formulas:Correcting Results in
a Calculated Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
3.30. Using Formulas:Listing All Formulas. . . . . . . . . . . . . . . . . . . . . . . . 85
3.31. Using Formulas:Accidentally Creating
a Calculated Item. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
3.32. Using Formulas:Solve Order. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
■ CHAPTER 4 Formatting a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
4.1. Using AutoFormat:Applying a Predefined Format. . . . . . . . . . . . . 89
4.2. Using AutoFormat:Removing an AutoFormat . . . . . . . . . . . . . . . . 90
4.3. Using AutoFormat:Applying a Standard Table AutoFormat . . . . . 90
4.4. Using the Enable Selection Option. . . . . . . . . . . . . . . . . . . . . . . . . . 91
4.5. Losing Formatting When Refreshing the Pivot Table . . . . . . . . . . 92
4.6. Retaining the Source Data Formatting. . . . . . . . . . . . . . . . . . . . . . . 93
4.7. Hiding Data Errors on Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . 94
4.8. Hiding Errors When Printing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
4.9. Showing Zero in Empty Data Cells. . . . . . . . . . . . . . . . . . . . . . . . . . 95
4.10. Using Conditional Formatting in a Pivot Table . . . . . . . . . . . . . . . . 95
4.11. Creating Custom Number Formats in the Source Data. . . . . . . . . 96
4.12. Totaling Hours in a Time Field. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
4.13. Displaying Hundredths of Seconds in a Pivot Table. . . . . . . . . . . . 97
4.14. Centering Field Labels Vertically. . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
4.15. Applying an Indented AutoFormat. . . . . . . . . . . . . . . . . . . . . . . . . . . 98
4.16. Creating an Indented Format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
4.17. Applying a Tabular AutoFormat. . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
4.18. Displaying Subtotals at the Top of a Group. . . . . . . . . . . . . . . . . . 101
4.19. Separating Field Items with Blank Rows. . . . . . . . . . . . . . . . . . . . 102
4.20. Turning Off Subtotals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
4.21. Repeating Row Headings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.22. Retaining Formatting for Temporarily Removed Fields. . . . . . . . 104
4.23. Applying Formatting with the Format Painter. . . . . . . . . . . . . . . . 104
4.24. Grouping Dates Based on Source Data Formatting. . . . . . . . . . . 105
4.25. Changing Alignment for Merged Labels . . . . . . . . . . . . . . . . . . . . 106
4.26. Displaying Line Breaks in Pivot Table Cells. . . . . . . . . . . . . . . . . . 107
4.27. Showing Only the Top Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
4.28. Freezing Heading Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
4.29. Using the Always Display Items Option. . . . . . . . . . . . . . . . . . . . . 108
4.30. Applying Number Formatting to Page Fields. . . . . . . . . . . . . . . . . 108
4.31. Displaying Hyperlinks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
4.32. Changing Total Label Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109