Table of Contents

Introduction

Chapter 1 Pivot Table Fundamentals

What Is a Pivot Table?

Why Should You Use a Pivot Table?

When Should You Use a Pivot Table?

The Anatomy of a Pivot Table

    Values Area

    Rows Area

    Columns Area

    Filters Area

Pivot Tables Behind the Scenes

Limitations of Pivot Table Reports

    A Word About Compatibility

Next Steps

Chapter 2 Creating a Basic Pivot Table

    Ensure Your Data Is in a Tabular Layout

    Avoid Storing Data in Section Headings

    Avoid Repeating Groups as Columns

    Eliminate Gaps and Blank Cells in Your Data Source

    Apply Appropriate Type Formatting to Your Fields

    Summary of Good Data Source Design

Creating a Basic Pivot Table

    Adding Fields to the Report

    Adding Layers to Your Pivot Table

    Rearranging Your Pivot Table

    Creating a Report Filter

Understanding the Recommended PivotTables Feature

Using Slicers

    Creating a Standard Slicer

    Creating a Timeline Slicer

Keeping Up with Changes in Your Data Source

    Changes Have Been Made to Your Existing Data Source

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

Sharing the Pivot Cache

Saving Time with New Pivot Table Tools

    Deferring Layout Updates

    Starting Over with One Click

    Relocating Your Pivot Table

Next Steps

Chapter 3 Customizing a Pivot Table

Making Common Cosmetic Changes

    Applying a Table Style to Restore Gridlines

    Changing the Number Format to Add Thousands Separators

    Replacing Blanks with Zeros

    Changing a Field Name

Making Report Layout Changes

    Using the New Compact Layout

    Using the Outline Form Layout

    Using the Traditional Tabular Layout

    Controlling Blank Lines, Grand Totals, and Other Settings

Customizing the Pivot Table Appearance with Styles and Themes

    Customizing a Style

    Modifying Styles with Document Themes

Changing Summary Calculations

    Understanding Why One Blank Cell Causes a Count

About the Author

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 38 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine and his Excel tips appear regularly in CFO Excel Pro newsletter and CFO Magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches, working as a financial analyst for the finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio with his wife, Mary Ellen.

Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years of experience consulting and developing Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares basic Access and Excel tips to the Office community.