Excel PivotTables and PivotCharts: Your visual blueprint for creating dynamic spreadsheets

Master two of the most powerful features of Excel Even if you use Excel all the time, you may not be up to speed on two of Excel's most useful features. PivotTable and PivotChart turn long lists of unreadable data into dynamic, easy-to-read tables and charts that highlight the information you need most; you can tweak results with a click or easily fuse data from several sources into one document. Now you can learn how to tap these powerful Excel tools with this practical guide. Using a series of step-by-step tutorials and easy-to-follow screenshots, this book shows you in a visual way how to create and customize PivotTables and PivotCharts, use them to analyze business data, and ultimately achieve more with less work. Explains the benefits PivotTables and PivotCharts, two powerful features of Excel that allow users to create dynamic spreadsheets Covers creating and customizing, analyzing business data, building custom calculations, linking to external data sources (including Access databases, Word tables, Web pages, XML data, SQL Server databases, and OLAP cubes), creating macros to automate tasks, and more Uses step-by-step tutorials and easy-to-follow screenshots, a "learn by seeing" approach for visual learners Practical, visual, and packed with content, this is the book you need to ramp up your Excel skills with PivotTables and PivotCharts.

Table of Contents:
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