9780471784890

With more than 300 million users, Excel is the most widely used spreadsheet program in the world Readers need extensive and in-depth information to learn how to master PivotTables and PivotCharts, and this book provides exactly that Covers such topics as working with PivotTable tools and components, using multidimensional data, and getting hard data from a PivotTable Includes an appendix that explains the differences between Excel 2000, 2002, and 2003

Paul McFedries is the president of Logophilia Limited, a technical writing company. While now primarily a writer, Paul has worked as a programmer, consultant, and Web site developer. Paul has written over 40 books that have sold over three million copies worldwide. These books include the Wiley titles Teach Yourself VISUALLY Windows XP, Second Edition, Top 100 Simplified Tips and Tricks for Windows XP, Second Edition, and Teach Yourself VISUALLY Computers, Fourth Edition.

How to Use this Book

xii

Understanding PivotTables

2

(12)

Understanding Data Analysis

2

(2)

Introducing the PivotTable

4

(2)

Learn PivotTable Benefits

6

(2)

Survey PivotTable Uses

8

(2)

Explore PivotTable Features

10

(2)

Introducing the PivotChart

12

(2)

Building a PivotTable

14

(12)

Prepare Your Worksheet Data

14

(2)

Create a List for a PivotTable Report

16

(2)

Build a Basic PivotTable from an Excel List

18

(4)

Add Fields Using the PivotTable Wizard

22

(2)

Re-create an Existing PivotTable

24

(2)

Manipulating Your PivotTable

26

(30)

Turn the PivotTable Toolbar On and Off

26

(1)

Customize the PivotTable Toolbar

27

(1)

Select PivotTable Items

28

(2)

Remove a PivotTable Field

30

(2)

Refresh PivotTable Data

32

(2)

Display the Details Behind PivotTable Data

34

(2)

Create a Chart from PivotTable Data

36

(4)

Add Multiple Fields to the Row or Column Area

40

(2)

Add Multiple Fields to the Data Area

42

(2)

Add Multiple Fields to the Page Area

44

(2)

Publish a PivotTable to a Web Page

46

(4)

Convert a PivotTable to Regular Data

50

(2)

Print a PivotTable

52

(2)

Delete a PivotTable

54

(2)

Changing the PivotTable View

56

(30)

Move a Field to a Different Area

56

(2)

Display a Different Page

58

(2)

Change the Order of Fields Within an Area

60

(2)

Sort PivotTable Data with AutoSort

62

(2)

Move Row and Column Items

64

(2)

Show Only the Top 10 Items

66

(2)

Hide Items in a Row or Column Field

68

(2)

Show Hidden Items in a Row or Column Field

70

(2)

Show Pages as Worksheets

72

(2)

Group Numeric Values

74

(2)

Group Date and Time Values

76

(2)

Group Text Values

78

(2)

Hide Group Details

80

(1)

Show Group Details

81

(1)

Ungroup Values

82

(2)

Change the Page Area Layout

84

(2)

Customizing PivotTable Fields

86

(16)

Rename a PivotTable Field

86

(2)

Rename a PivotTable Item

88

(2)

Format a PivotTable Cell

90

(2)

Apply a Numeric Format to PivotTable Data

92

(2)

Apply a Date Format to PivotTable Data

94

(2)

Apply a Conditional Format to PivotTable Data

96

(2)

Show Items with No Data

98

(2)

Exclude Items from a Page Field

100

(2)

Setting PivotTable Options

102

(14)

Apply an AutoFormat

102

(2)

Preserve PivotTable Formatting

104

(2)

Rename the PivotTable

106

(2)

Turn Off Grand Totals

108

(2)

Merge Item Labels

110

(2)

Specify Characters for Errors and Empty Cells

112

(2)

Protect a PivotTable

114

(2)

Performing PivotTable Calculations

116

(16)

Change the PivotTable Summary Calculation

116

(2)

Create a Difference Summary Calculation

118

(2)

Create a Percentage Summary Calculation

120

(2)

Create a Running Total Summary Calculation

122

(2)

Create an Index Summary Calculation

124

(2)

Turn Off Subtotals for a Field

126

(2)

Display Multiple Subtotals for a Field

128

(2)

Include Hidden Pages in PivotTable Results

130

(2)

Creating Custom PivotTable Calculations

132

(12)

Introducing Custom Calculations

132

(1)

Understanding Custom Calculation Limitations

133

(1)

Insert a Custom Calculated Field

134

(2)

Insert a Custom Calculated Item

136

(2)

Edit a Custom Calculation

138

(2)

Change the Solve Order of Calculated Items

140

(2)

List Your Custom Calculations

142

(1)

Delete a Custom Calculation

143

(1)

Creating a Pivotchart

144

(26)

Understanding PivotChart Limitations

144

(1)

Create a PivotChart from a PivotTable

145

(1)

Create a PivotChart from an Excel List

146

(4)

Create a PivotChart Beside a PivotTable

150

(2)

Move a PivotChart to Another Sheet

152

(2)

Hide or Show PivotChart Field Buttons

154

(2)

Change the PivotChart Type

156

(2)

Change the PivotChart Series Order

158

(2)

Add PivotChart Titles

160

(2)

Move the PivotChart Legend

162

(2)

Display a Data Table with the PivotChart

164

(2)

Print a PivotChart

166

(2)

Delete a PivotChart

168

(2)

Building More Advanced PivotTables

170

(18)

Create a PivotTable from Multiple Consolidation Ranges

170

(4)

Create a PivotTable from an Existing PivotTable

174

(2)

Create a PivotTable from External Data

176

(4)

Set Up a Server-Based Page Field

180

(2)

Set External Data Options

182

(2)

Export PivotTable Data from a Web Page to Excel

184

(2)

Reduce the Size of PivotTable Workbooks

186

(1)

Use a PivotTable Value in a Formula

187

(1)

Building a PivotTable From an Olap Cube

188

(18)

Understanding OLAP

188

(2)

Create an OLAP Cube Data Source

190

(4)

Create a PivotTable from an OLAP Cube

194

(2)

Show and Hide Details for Dimensions and Levels

196

(2)

Hide Levels

198

(1)

Display Selected Levels and Members

199

(1)

Display Multiple Page Field Items

200

(2)

Create an Offline OLAP Cube

202

(4)

APPENDIX A: BUILDING FORMULAS FOR PIVOTTABLES

206

(14)

Introducing Formulas

206

(2)

Understanding Formula Types

208

(2)

Introducing Worksheet Functions

210

(2)

Understanding Function Types

212

(2)

Build a Function

214

(2)

Build a Formula

216

(2)

Work with Custom Numeric and Date Formats

218

(2)

APPENDIX B: USING MICROSOFT QUERY WITH EXCEL PIVOTTABLES

220

(18)

Understanding Microsoft Query

220

(2)

Define a Data Source

222

(4)

Start Microsoft Query

226

(1)

Tour the Microsoft Query Window

227

(1)

Add a Table to the Query

228

(2)

Add Fields to the Query

230

(2)

Filter the Records with Query Criteria

232

(2)

Sort the Query Records

234

(2)

Return the Query Results

236

(2)

APPENDIX C: IMPORTING DATA FOR PIVOTTABLES

238

(18)

Understanding External Data

238

(2)

Import Data from a Data Source

240

(2)

Import Data from an Access Table

242

(2)

Import Data from a Word Table

244

(2)

Import Data from a Text File

246

(4)

Import Data from a Web Page

250

(2)

Import Data from an XML File

252

(2)

Refresh Imported Data

254

(2)

APPENDIX D: LEARNING VBA BASICS FOR PIVOTTABLES

256

(18)

Record a Macro

256

(2)

Open the Visual Basic Editor

258

(2)

Explore the PivotTable Object Model

260

(2)

Add a Macro to a Module

262

(2)

Run a Macro

264

(2)

Set Macro Security

266

(4)

Assign a Macro to a Toolbar Button

270

(2)

Assign a Macro to a Menu Command

272

(2)

APPENDIX E: GLOSSARY OF PIVOTTABLE TERMS

274

(6)

Glossary of PivotTable Terms

274

(6)

Index

280

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.