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 v1 Understanding Pivottables and Pivotcharts 2Understanding Data Analysis 2Introducing the PivotTable 4Learn PivotTable Benefits 6Learn When to Use PivotTables 8Explore PivotTable Features 10Introducing the PivotChart 122 Building a Pivottable 14Prepare Your Worksheet Data 14Create a Table for a PivotTable Report 16Build a PivotTable from an Excel Table 18Build a PivotTable from an Excel Range 20Recreate an Existing PivotTable 223 Manipulating Your Pivottable 24Turn the PivotTable Field List On and Off 24Customize the PivotTable Field List 25Select PivotTable Items 26Remove a PivotTable Field 28Refresh PivotTable Data 30Display the Details Behind PivotTable Data 32Create a Chart from PivotTable Data 34Enable the Classic PivotTable Layout 38Add Multiple Fields to the Row or Column Area 40Add Multiple Fields to the Data Area 42Add Multiple Fields to the Report Filter 44Publish a PivotTable to a Web Page 46Convert a PivotTable to Regular Data 50Delete a PivotTable 524 Changing the Pivottable View 54Move a Field to a Different Area 54Change the Order of Fields within an Area 56Change the Report Layout 58Sort PivotTable Data with AutoSort 60Move Row and Column Items 62Group Numeric Values 64Group Date and Time Values 66Group Text Values 68Hide Group Details 70Show Group Details 71Ungroup Values 725 Filtering a Pivottable 74Apply a Report Filter 74Change the Report Filter Layout 76Filter Row or Column Items 78Filter PivotTable Values 80Hide Items in a Row or Column Field 82Use Search to Display Multiple Items 84Show Hidden Items in a Row or Column Field 86Filter a PivotTable with a Slicer 88Connect a PivotTable to an Existing Slicer 90Connect a Slicer to Multiple PivotTables 926 Customizing Pivottable Fields 94Rename a PivotTable Field 94Rename a PivotTable Item 96Format a PivotTable Cell 98Apply a Numeric Format to PivotTable Data 100Apply a Date Format to PivotTable Data 102Apply a Conditional Format to PivotTable Data 104Show Items with No Data 106Exclude Items from a Report Filter 108Repeat Item Labels in Fields 1107 Creating a Pivotchart 112Understanding PivotChart Limitations 112Create a PivotChart from a PivotTable 113Create a PivotChart beside a PivotTable 114Create a PivotChart from an Excel Table 116Move a PivotChart to another Sheet 118Filter a PivotChart 120Change the PivotChart Type 122Sort the PivotChart 124Add PivotChart Titles 126Move the PivotChart Legend 128Display a Data Table with the PivotChart 1308 Setting Pivottable Options 132Apply a PivotTable Quick Style 132Create a Custom PivotTable Quick Style 134Preserve PivotTable Formatting 136Rename the PivotTable 138Turn Off Grand Totals 140Merge Item Labels 142Specify Characters for Errors and Empty Cells 144Protect a PivotTable 1469 Performing Pivottable Calculations 148Change the PivotTable Summary Calculation 148Create a Difference Summary Calculation 150Create a Percentage Summary Calculation 152Create a Running Total Summary Calculation 154Create an Index Summary Calculation 156Turn Off Subtotals for a Field 158Display Multiple Subtotals for a Field 16010 Creating Custom Pivottable Calculations 162Introducing Custom Calculations 162Understanding Custom Calculation Limitations 163Insert a Custom Calculated Field 164Insert a Custom Calculated Item 166Edit a Custom Calculation 168Change the Solve Order of Calculated Items 170List Your Custom Calculations 172Delete a Custom Calculation 17311 Building Formulas for Pivottables 174Introducing Formulas 174Understanding Formula Types 176Introducing Worksheet Functions 178Understanding Function Types 180Build a Function 182Build a Formula 184Work with Custom Numeric and Date Formats 18612 Using Microsoft Query with Pivottables 188Understanding Microsoft Query 188Define a Data Source 190Start Microsoft Query 194Tour the Microsoft Query Window 195Add a Table to the Query 196Add Fields to the Query 198Filter the Records with Query Criteria 200Sort the Query Records 202Return the Query Results 20413 Importing Data for Pivottables 206Understanding External Data 206Import Data from a Data Source 208Import Data from an Access Table 210Import Data from a Word Table 212Import Data from a Text File 214Import Data from a Web Page 218Import Data from an XML File 220Create a PowerPivot Data Connection 222Refresh Imported Data 22414 Building More Advanced Pivottables 226Create a PivotTable from Multiple Consolidation Ranges 226Create a PivotTable from an Existing PivotTable 230Create a PivotTable from External Data 232Create a PivotTable Using PowerPivot 236Automatically Refresh a PivotTable that Uses External Data 238Save Your Password with an External Data Connection 240Export an Access PivotTable Form to Excel 242Reduce the Size of PivotTable Workbooks 244Use a PivotTable Value in a Formula 24515 Building a Pivottable From An Olap Cube 246Understanding OLAP 246Create an OLAP Cube Data Source 248Create a PivotTable from an OLAP Cube 252Show and Hide Details for Dimensions and Levels 254Hide Levels 256Display Selected Levels and Members 257Display Multiple Report Filter Items 258Include Hidden Items in PivotTable Totals 260Performing What-if Analysis on the PivotTable 262Create an Offline OLAP Cube 26416 Learning Vba Basics for Pivottables 268Open the VBA Editor 268Add a Macro to a Module 270Run a Macro 272Set Macro Security 274Assign a Shortcut Key to a Macro 278Appendix: Glossary of Pivottable Terms 280