Excel PivotTables and PivotCharts

Paul McFedries is a full-time technical writer. Paul has been authoring computer books since 1991 and he has more than 70 books to his credit. Paul’s books have sold more than three million copies worldwide. These books include the Wiley titles Teach Yourself VISUALLY Excel 2010; Excel 2010 Visual Quick Tips; Teach Yourself VISUALLY Windows 7; and Teach Yourself VISUALLY Office 2008 for Mac. Paul is also the proprietor of Word Spy (www.wordspy.com and twitter.com/wordspy), a Web site that tracks new words and phrases as they enter the language. Paul invites you to drop by his personal Web site at www.mcfedries.com or to follow him on Twitter at twitter.com/paulmcf.

How to Use This Book v

1 Understanding Pivottables and Pivotcharts 2

Understanding Data Analysis 2

Introducing the PivotTable 4

Learn PivotTable Benefits 6

Learn When to Use PivotTables 8

Explore PivotTable Features 10

Introducing the PivotChart 12

2 Building a Pivottable 14

Prepare Your Worksheet Data 14

Create a Table for a PivotTable Report 16

Build a PivotTable from an Excel Table 18

Build a PivotTable from an Excel Range 20

Recreate an Existing PivotTable 22

3 Manipulating Your Pivottable 24

Turn the PivotTable Field List On and Off 24

Customize the PivotTable Field List 25

Select PivotTable Items 26

Remove a PivotTable Field 28

Refresh PivotTable Data 30

Display the Details Behind PivotTable Data 32

Create a Chart from PivotTable Data 34

Enable the Classic PivotTable Layout 38

Add Multiple Fields to the Row or Column Area 40

Add Multiple Fields to the Data Area 42

Add Multiple Fields to the Report Filter 44

Publish a PivotTable to a Web Page 46

Convert a PivotTable to Regular Data 50

Delete a PivotTable 52

4 Changing the Pivottable View 54

Move a Field to a Different Area 54

Change the Order of Fields within an Area 56

Change the Report Layout 58

Sort PivotTable Data with AutoSort 60

Move Row and Column Items 62

Group Numeric Values 64

Group Date and Time Values 66

Group Text Values 68

Hide Group Details 70

Show Group Details 71

Ungroup Values 72

5 Filtering a Pivottable 74

Apply a Report Filter 74

Change the Report Filter Layout 76

Filter Row or Column Items 78

Filter PivotTable Values 80

Hide Items in a Row or Column Field 82

Use Search to Display Multiple Items 84

Show Hidden Items in a Row or Column Field 86

Filter a PivotTable with a Slicer 88

Connect a PivotTable to an Existing Slicer 90

Connect a Slicer to Multiple PivotTables 92

6 Customizing Pivottable Fields 94

Rename a PivotTable Field 94

Rename a PivotTable Item 96

Format a PivotTable Cell 98

Apply a Numeric Format to PivotTable Data 100

Apply a Date Format to PivotTable Data 102

Apply a Conditional Format to PivotTable Data 104

Show Items with No Data 106

Exclude Items from a Report Filter 108

Repeat Item Labels in Fields 110

7 Creating a Pivotchart 112

Understanding PivotChart Limitations 112

Create a PivotChart from a PivotTable 113

Create a PivotChart beside a PivotTable 114

Create a PivotChart from an Excel Table 116

Move a PivotChart to another Sheet 118

Filter a PivotChart 120

Change the PivotChart Type 122

Sort the PivotChart 124

Add PivotChart Titles 126

Move the PivotChart Legend 128

Display a Data Table with the PivotChart 130

8 Setting Pivottable Options 132

Apply a PivotTable Quick Style 132

Create a Custom PivotTable Quick Style 134

Preserve PivotTable Formatting 136

Rename the PivotTable 138

Turn Off Grand Totals 140

Merge Item Labels 142

Specify Characters for Errors and Empty Cells 144

Protect a PivotTable 146

9 Performing Pivottable Calculations 148

Change the PivotTable Summary Calculation 148

Create a Difference Summary Calculation 150

Create a Percentage Summary Calculation 152

Create a Running Total Summary Calculation 154

Create an Index Summary Calculation 156

Turn Off Subtotals for a Field 158

Display Multiple Subtotals for a Field 160

10 Creating Custom Pivottable Calculations 162

Introducing Custom Calculations 162

Understanding Custom Calculation Limitations 163

Insert a Custom Calculated Field 164

Insert a Custom Calculated Item 166

Edit a Custom Calculation 168

Change the Solve Order of Calculated Items 170

List Your Custom Calculations 172

Delete a Custom Calculation 173

11 Building Formulas for Pivottables 174

Introducing Formulas 174

Understanding Formula Types 176

Introducing Worksheet Functions 178

Understanding Function Types 180

Build a Function 182

Build a Formula 184

Work with Custom Numeric and Date Formats 186

12 Using Microsoft Query with Pivottables 188

Understanding Microsoft Query 188

Define a Data Source 190

Start Microsoft Query 194

Tour the Microsoft Query Window 195

Add a Table to the Query 196

Add Fields to the Query 198

Filter the Records with Query Criteria 200

Sort the Query Records 202

Return the Query Results 204

13 Importing Data for Pivottables 206

Understanding External Data 206

Import Data from a Data Source 208

Import Data from an Access Table 210

Import Data from a Word Table 212

Import Data from a Text File 214

Import Data from a Web Page 218

Import Data from an XML File 220

Create a PowerPivot Data Connection 222

Refresh Imported Data 224

14 Building More Advanced Pivottables 226

Create a PivotTable from Multiple Consolidation Ranges 226

Create a PivotTable from an Existing PivotTable 230

Create a PivotTable from External Data 232

Create a PivotTable Using PowerPivot 236

Automatically Refresh a PivotTable that Uses External Data 238

Save Your Password with an External Data Connection 240

Export an Access PivotTable Form to Excel 242

Reduce the Size of PivotTable Workbooks 244

Use a PivotTable Value in a Formula 245

15 Building a Pivottable From An Olap Cube 246

Understanding OLAP 246

Create an OLAP Cube Data Source 248

Create a PivotTable from an OLAP Cube 252

Show and Hide Details for Dimensions and Levels 254

Hide Levels 256

Display Selected Levels and Members 257

Display Multiple Report Filter Items 258

Include Hidden Items in PivotTable Totals 260

Performing What-if Analysis on the PivotTable 262

Create an Offline OLAP Cube 264

16 Learning Vba Basics for Pivottables 268

Open the VBA Editor 268

Add a Macro to a Module 270

Run a Macro 272

Set Macro Security 274

Assign a Shortcut Key to a Macro 278

Appendix: Glossary of Pivottable Terms 280