Que.Pivot.Table.Data.Crunching.for.Microsoft.Office.Excel.2007.Jan.2007.pdf

Contents at a Glance

usiness solutions

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

1 Pivot Table Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

2 Creating a Basic Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

3 Customizing a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

4 Controlling the Way You View Your Pivot Data . . . . . . . . . . . . . . . . . .83

5 Performing Calculations Within Your Pivot Tables . . . . . . . . . . . . . .117

6 Using Pivot Charts and Other Visualizations . . . . . . . . . . . . . . . . . . .141

7 Analyzing Disparate Data Sources with Pivot Tables . . . . . . . . . . .167

8 Sharing Pivot Tables with Others . . . . . . . . . . . . . . . . . . . . . . . . . . . .189

9 Working with and Analyzing OLAP Data . . . . . . . . . . . . . . . . . . . . . .201

10 Enhancing Your Pivot Table Reports with Macros . . . . . . . . . . . . . .215

11 Using VBA to Create Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . .231

12 Common Pivot Table Issues and Questions . . . . . . . . . . . . . . . . . . . .291

A Finding Pivot Table Commands on the Ribbon . . . . . . . . . . . . . . . .315

Pivot Table Data

Crunching for

Microsoft ® Office

Excel ® 2007

Bill Jelen

Michael Alexander

800 E. 96th Street

Indianapolis, Indiana 46240

Pivot Table Data Crunching for Microsoft ® Office Excel ® 2007

retrieval system, or transmitted by any means, electronic, mechanical, photo-

copying, recording, or otherwise, without written permission from the pub-

lisher. No patent liability is assumed with respect to the use of the

information contained herein. Although every precaution has been taken in

the preparation of this book, the publisher and author assume no responsibil-

ity for errors or omissions. Nor is any liability assumed for damages resulting

from the use of the information contained herein.

International Standard Book Number: 0-7897-3601-2

Library of Congress Cataloging-in-Publication data is on file.

Printed in the United States of America

First Printing: December 2006

9876

Associate Publisher

Greg Wiegand

Acquisitions Editor

Loretta Yates

Development Editor

Kevin Howard

Managing Editor

Gina Kanouse

Project Editor

Andy Beaster

Copy Editor

Chuck Hutchinson

Indexer

Erika Millen

Proofreader

Heather Arle

Technical Editor

Juan Pablo González

Team Coordinator

Cindy Teeters

Interior Designer

Anne Jones

Cover Designer

Anne Jones

Compositor

Juli Cook

Trademarks

All terms mentioned in this book that are known to be trademarks or service

marks have been appropriately capitalized. Que Publishing cannot attest to

the accuracy of this information. Use of a term in this book should not be

regarded as affecting the validity of any trademark or service mark.

Microsoft is a registered trademark of Microsoft Corporation.

Excel is a registered trademark of Microsoft Corporation.

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate

as possible, but no warranty or fitness is implied. The information pro-

vided is on an “as is” basis. The authors and the publisher shall have nei-

ther liability nor responsibility to any person or entity with respect to any

loss or damages arising from the information contained in this book.

Bulk Sales

Que Publishing offers excellent discounts on this book when ordered in

quantity for bulk purchases or special sales. For more information, please

contact

U.S. Corporate and Government Sales

1-800-382-3419

corpsales@pearsontechgroup.com

For sales outside of the U.S., please contact

International Sales

international@pearsoned.com

4321

Table of Contents

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

What You Will Learn from This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

What Is New in Excel 2007’s Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

Skills Required to Use This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

Case Study: Life Before Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2

The Invention of the Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

Case Study: Life After Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7

Sample Files Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Conventions Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Referring to Ribbon Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Special Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10

1 Pivot Table Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

What Is a Pivot Table? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

Why Should You Use a Pivot Table? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12

When Should You Use a Pivot Table? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13

The Anatomy of a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14

Values Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14

Row Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Column Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15

Report Filter Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16

Pivot Tables Behind the Scenes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16

Limitations of Pivot Table Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17

A Word About Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18

Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19

2 Creating a Basic Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

Preparing Your Data for Pivot Table Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

Ensure Your Data Is in a Tabular Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

Avoid Storing Data in Section Headings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22

Avoid Repeating Groups as Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23

Eliminate Gaps and Blank Cells in Your Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23

Apply Appropriate Type Formatting to Your Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

Summary of Good Data Source Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

Case Study: Cleaning Up Data for Pivot Table Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25

Creating a Basic Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26

Adding Fields to the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29

Adding Layers to Your Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

Rearranging Your Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

Creating a Report Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33

Case Study: Analyzing Activity by Market . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35

Pivot Table Data Crunching for Microsoft Office Excel 2007

Keeping Up with Changes in Your Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39

Changes Have Been Made to Your Existing Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40

Your Data Source’s Range Has Been Expanded with the Addition of

Rows or Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40

Sharing the Pivot Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

Side Effects of Sharing a Pivot Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

Saving Time with New Pivot Table Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Deferring Layout Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Starting Over with One Click . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42

Relocating Your Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43

Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44

3 Customizing a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Making Common Cosmetic Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46

Applying a Table Style to Restore Gridlines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

Changing the Number Format to Add Thousands Separators . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

Replacing Blanks with Zeros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49

Changing a Field Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Making Layout Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52

Using the New Compact Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52

Using the Outline Form Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54

Using the Traditional Tabular Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54

Case Study: Converting a Pivot Table to Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56

Controlling Blank Lines, Grand Totals, Subtotals, and Other Settings . . . . . . . . . . . . . . . . . . . . . .59

Customizing the Pivot Table Appearance with Styles and Themes . . . . . . . . . . . . . . . . . . . . . . . . . . .61

Customizing a Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62

Choosing a Default Style for Future Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64

Modifying Styles with Document Themes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64

Changing Summary Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65

Understanding Why One Blank Cell Causes a Count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65

Using Functions Other Than Count or Sum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66

Adding and Removing Subtotals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68

Suppress Subtotals When You Have Many Row Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68

Adding Multiple Subtotals for One Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69

Using Running Total Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .70

Display Change from Year to Year with Difference From . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71

Compare One Year to a Prior Year with % Difference From . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72

Track YTD Numbers with Running Total In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72

Determine How Much Each Line of Business Contributes to the Total . . . . . . . . . . . . . . . . . . . . .72

Create Seasonality Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73

Measure Percentage for Two Fields with % of Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73

Compare One Line to Another Line Using % Of . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75

Track Relative Importance with the Index Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: