| |
| |
| About the Author | |
| |
| |
| About the Technical Reviewer | |
| |
| |
| Preface | |
| |
| |
| Acknowledgments | |
| |
| |
| Introduction: PivotTables Are Powerfull | |
| |
| |
| |
| What Are PivotTables? | |
| |
| |
| Other Ways to Analyze Data in Excel | |
| |
| |
| Good Uses for PivotTables | |
| |
| |
| Try It! Work with Data Analysis Features | |
| |
| |
| Try It! Analyze Data Manually | |
| |
| |
| Try It! Analyze Data with a List | |
| |
| |
| Try It! Analyze Data with Crosstab Tables | |
| |
| |
| Try It! Analyze Data with a PivotTable | |
| |
| |
| Next Steps | |
| |
| |
| |
| Creating PivotTables | |
| |
| |
| Using the PivotTable and PivotChart Wizard | |
| |
| |
| Selecting Existing Source Data | |
| |
| |
| Selecting an Excel List or Database | |
| |
| |
| Selecting External Source Data | |
| |
| |
| Selecting a Consolidation Range | |
| |
| |
| Selecting an Existing PivotTable or PivotChart | |
| |
| |
| Creating a New Data Source Connection | |
| |
| |
| Setting PivotTable Report Formatting and Data Presentation Options | |
| |
| |
| Format Options Area | |
| |
| |
| Data Options Area | |
| |
| |
| Try It! Create a PivotTable Using the Wizard | |
| |
| |
| Connecting to the Source Data | |
| |
| |
| Defining the PivotTable's Initial Layout | |
| |
| |
| Changing the Summary Function and Number Formatting | |
| |
| |
| Filtering the PivotTable's Data | |
| |
| |
| Next Steps | |
| |
| |
| |
| Working with PivotTable Components | |
| |
| |
| Understanding Row, Column, Data, Page, and Calculated Fields and Field Items | |
| |
| |
| Drop Areas | |
| |
| |
| Field List | |
| |
| |
| Field Buttons | |
| |
| |
| Inner and Outer Row and Column Fields | |
| |
| |
| Field Placement Restrictions | |
| |
| |
| Field Items | |
| |
| |
| Item Filters | |
| |
| |
| Calculated Fields and Items | |
| |
| |
| Working with the PivotTable Toolbar | |
| |
| |
| Understanding the PivotTable Field Dialog Box | |
| |
| |
| Working with the PivotTable Menu | |
| |
| |
| Try It! Create and Customize a PivotTable | |
| |
| |
| Connect to the Source Data | |
| |
| |
| Add Fields to the Layout | |
| |
| |
| Move a Field to a Different Drop Area | |
| |
| |
| Filter the PivotTable | |
| |
| |
| Create a Calculated Field | |
| |
| |
| Create a Calculated Item | |
| |
| |
| Try It! Use the PivotTable Field List to Customize a PivotTable | |
| |
| |
| Try It! Work with the PivotTable Toolbar | |
| |
| |
| Add Color to the PivotTable | |
| |
| |
| Hide and Show Detail | |
| |
| |
| Use the PivotTable Field Dialog Box | |
| |
| |
| Next Steps | |
| |
| |
| |
| Using PivotTables in the Real World | |
| |
| |
| |
| Fabrikam Interiors | |
| |
| |
| Create the PivotTable | |
| |
| |
| Add Fields to the PivotTable | |
| |
| |
| Change Field Orientations | |
| |
| |
| Display Data by Category as a Percentage of Quarterly Totals | |
| |
| |
| Grouping and Ungrouping Items | |
| |
| |
| Display Data by Category as a Quarterly Differential Percentage | |
| |
| |
| Creating a PivotTable Based on Another PivotTable's Source Data | |
| |
| |
| Create a Calculated Field | |
| |
| |
| Conclusions | |
| |
| |
| |
| Tailspin Toys | |
| |
| |
| Create the PivotTable | |
| |
| |
| Add Fields to the PivotTable and Customize Data | |
| |
| |
| Create Multiple Pages from Page Field Values | |
| |
| |
| Sort and Order Items | |
| |
| |
| AutoShow Inner Row Field Top Items | |
| |
| |
| Group Items | |
| |
| |
| Calculating Standard Deviation and Comparing to the Average | |
| |
| |
| Conclusions | |
| |
| |
| |
| Contoso Publishing Ltd | |
| |
| |
| Create the PivotTable | |
| |
| |
| Add Fields to the PivotTable | |
| |
| |
| Sort and Order Items | |
| |
| |
| Changing How the Data Format Is Displayed | |
| |
| |
| Comparison of Sales as Percent Difference from Base Data | |
| |
| |
| Conditional Formatting | |
| |
| |
| Group Items | |
| |
| |
| Conclusions | |
| |
| |
| Next Steps | |
| |
| |
| |
| Working with PivotCharts | |
| |
| |
| What Are PivotCharts? | |
| |
| |
| PivotChart Terms | |
| |
| |
| Creating PivotCharts | |
| |
| |
| Formatting PivotCharts | |
| |
| |
| Good Uses for PivotCharts | |
| |
| |
| Lost Formatting in PivotCharts | |
| |
| |
| Try It! Create and Customize a PivotChart | |
| |
| |
| Create the PivotChart | |
| |
| |
| Design the PivotChart by Modifying the PivotTable | |
| |
| |
| Design the PivotTable by Modifying the PivotChart | |
| |
| |
| Change the PivotChart Type | |
| |
| |
| Change the PivotChart's Formatting | |
| |
| |
| Next Steps | |
| |
| |
| |
| Analyzing Multidimensional Data with PivotTables | |
| |
| |
| What Is Multidimensional Data? | |
| |
| |
| Understanding Multidimensional Data Terminology | |
| |
| |
| Contrasting Multidimensional Data and Other Types of Data in PivotTables and PivotCharts | |
| |
| |
| Using PivotTables to Work with Multidimensional Data | |
| |
| |
| Creating Offline Cube Files | |
| |
| |
| Try It! Create a Multidimensional Data Source | |
| |
| |
| Try It! Analyze Multidimensional Data with a PivotTable and a PivotChart | |
| |
| |
| Create a PivotTable and PivotChart and Connect Them to the Cube File | |
| |
| |
| Add Dimensions and Measures to the PivotTable and PivotChart | |
| |
| |
| Rearrange Dimensions in the PivotTable and PivotChart | |
| |
| |
| Filter Data in the PivotTable and PivotChart by Selecting a Combination of Members | |
| |
| |
| Show and Hide Levels in the PivotTable and PivotChart | |
| |
| |
| Grouping Members in Row, Column, and Page Fields | |
| |
| |
| Sorting and Showing Top Members | |
| |
| |
| Next Steps | |
| |
| |
| |
| Programming PivotTables | |
| |
| |
| Before Getting Started | |
| |
| |
| Understanding the PivotTable Object Model | |
| |
| |
| Code Examples | |
| |
| |
| Create PivotTables | |
| |
| |
| Create a PivotChart | |
| |
| |
| Add Fields to the PivotTable Visual Layout | |
| |
| |
| Change Field Placement | |
| |
| |
| Set and Remove Field Filters | |
| |
| |
| Move a Field's Position | |
| |
| |
| Create a Calculated Field and a Calculated Item | |
| |
| |
| Show and Hide Field Items | |
| |
| |
| AutoSort and AutoShow Items | |
| |
| |
| Change a Field's Custom Calculation | |
| |
| |
| Change a Field's Display Format | |
| |
| |
| Next Steps | |
| |
| |
| |
| PivotTable Differences Between Excel 2000, 2002, and 2003 | |
| |
| |
| What's New for Excel 2000 for PivotTables and PivotCharts | |
| |
| |
| What's New for Excel 2002 and 2003 for PivotTables and PivotCharts | |
| |
| |
| Programmatic Changes for PivotTables and PivotCharts in Excel 2000 | |
| |
| |
| Programmatic Changes for PivotTables and PivotCharts in Excel 2002 and 2003 | |
| |
| |
| Index | |