Contents

Introduction

What You Will Learn from This Book

What Is New in Excel 2016’s Pivot Tables

Skills Required to Use This Book

Invention of the Pivot Table

Sample Files Used in This Book

Conventions Used in This Book

Referring to Versions

Referring to Ribbon Commands

Special Elements

1 Pivot Table Fundamentals

Defining a Pivot Table

Why You Should Use a Pivot Table

Advantages of Using a Pivot Table

When to Use a Pivot Table

Anatomy of a Pivot Table

Values Area

Rows Area

Columns Area

Filters Area

Pivot Tables Behind the Scenes

Pivot Table Backward Compatibility

A Word About Compatibility

Next Steps

2 Creating a Basic Pivot Table

Preparing Data for Pivot Table Reporting

Ensuring That Data Is in a Tabular Layout

Avoiding Storing Data in Section Headings

Avoiding Repeating Groups as Columns

Eliminating Gaps and Blank Cells in the Data Source

Applying Appropriate Type Formatting to Fields

Summary of Good Data Source Design

How to Create a Basic Pivot Table

Adding Fields to a Report

Fundamentals of Laying Out a Pivot Table Report

Adding Layers to a Pivot Table

Rearranging a Pivot Table

Creating a Report Filter

Understanding the Recommended Pivot Table Feature

Using Slicers

Creating a Standard Slicer

Creating a Timeline Slicer

Keeping Up with Changes in the Data Source

Dealing with Changes Made to the Existing Data Source

Dealing with an Expanded Data Source Range Due to 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 a Pivot Table

Next Steps

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 Compact Layout

Using the Outline Layout

Using the Traditional Tabular Layout

Controlling Blank Lines, Grand Totals, and Other Settings

Customizing a Pivot Table’s Appearance with Styles and Themes

Customizing a Style

Modifying Styles with Document Themes

Changing Summary Calculations

Understanding Why One Blank Cell Causes a Count

Using Functions Other Than Count or Sum

Adding and Removing Subtotals

Suppressing Subtotals with Many Row Fields

Adding Multiple Subtotals for One Field

Changing the Calculation in a Value Field

Showing Percentage of Total

Using % Of to Compare One Line to Another Line

Showing Rank

Tracking Running Total and Percentage of Running Total

Displaying a Change from a Previous Field

Tracking the Percentage of a Parent Item

Tracking Relative Importance with the Index Option

Next Steps

4 Grouping, Sorting, and Filtering Pivot Data

Automatically Grouping Dates

Undoing Automatic Grouping

Understanding How Excel 2016 Decides What to Group

Grouping Date Fields Manually

Including Years When Grouping by Months

Grouping Date Fields by Week

Grouping Numeric Fields

Using the PivotTable Fields List

Docking and Undocking the PivotTable Fields List

Rearranging the PivotTable Fields List

Using the Areas Section Drop-Downs

Sorting in a Pivot Table

Sorting Customers into High-to-Low Sequence Based on Revenue

Using a Manual Sort Sequence

Using a Custom List for Sorting

Filtering a Pivot Table: An Overview

Using Filters for Row and Column Fields

Filtering Using the Check Boxes

Filtering Using the Search Box

Filtering Using the Label Filters Option

Filtering a Label Column Using Information in a Values Column

Creating a Top-Five Report Using the Top 10 Filter

Filtering Using the Date Filters in the Label Drop-down

Filtering Using the Filters Area

Adding Fields to the Filters Area

Choosing One Item from a Filter

Choosing Multiple Items from a Filter

Replicating a Pivot Table Report for Each Item in a Filter

Filtering Using Slicers and Timelines

Using Timelines to Filter by Date

Driving Multiple Pivot Tables from One Set of Slicers

Next Steps

5 Performing Calculations in Pivot Tables

Introducing Calculated Fields and Calculated Items

Method 1: Manually Add a Calculated Field to the Data Source

Method 2: Use a Formula Outside a Pivot Table to Create a Calculated Field

Method 3: Insert a Calculated Field Directly into a Pivot Table

Creating a Calculated Field

Creating a Calculated Item

Understanding the Rules and Shortcomings of Pivot Table Calculations

Remembering the Order of Operator Precedence

Using Cell References and Named Ranges

Using Worksheet Functions

Using Constants

Referencing Totals

Rules Specific to Calculated Fields

Rules Specific to Calculated Items

Managing and Maintaining Pivot Table Calculations

Editing and Deleting Pivot Table Calculations

Changing the Solve Order of Calculated Items

Documenting Formulas

Next Steps

6 Using Pivot Charts and Other Visualizations

What Is a Pivot Chart...Really?

Creating a Pivot Chart

Understanding Pivot Field Buttons

Keeping Pivot Chart Rules in Mind

Changes in the Underlying Pivot Table Affect a Pivot Chart

Placement of Data Fields in a Pivot Table Might Not Be Best Suited for a Pivot Chart

A Few Formatting Limitations Still Exist in Excel 2016

Examining Alternatives to Using Pivot Charts

Method 1: Turn the Pivot Table into Hard Values

Method 2: Delete the Underlying Pivot Table

Method 3: Distribute a Picture of the Pivot Chart

Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for the Chart

Using Conditional Formatting with Pivot Tables

An Example of Using Conditional Formatting

Preprogrammed Scenarios for Condition Levels

Creating Custom Conditional Formatting Rules

Next Steps

7 Analyzing Disparate Data Sources with Pivot Tables

Using the Internal Data Model

Building Out Your First Data Model

Managing Relationships in the Data Model

Adding a New Table to the Data Model

Removing a Table from the Data Model

Creating a New Pivot Table Using the Data Model

Limitations of the Internal Data Model

Building a Pivot Table Using External Data Sources

Building a Pivot Table with Microsoft Access Data

Building a Pivot Table with SQL Server Data

Leveraging Power Query to Extract and Transform Data

Power Query Basics

Understanding Query Steps

Refreshing Power Query Data

Managing Existing Queries

Understanding Column-Level Actions

Understanding Table Actions

Power Query Connection Types

Next Steps

8 Sharing Pivot Tables with Others

Designing a Workbook as an Interactive Web Page

Sharing a Link to a Web Workbook

Sharing with Power BI

Preparing Data for Power BI

Importing Data to Power BI

Building a Report in Power BI

Using Q&A to Query Data

Sharing Your Dashboard

Next Steps

9 Working with and Analyzing OLAP Data

Introduction to OLAP

Connecting to an OLAP Cube

Understanding the Structure of an OLAP Cube

Understanding the Limitations of OLAP Pivot Tables

Creating an Offline Cube

Breaking Out of the Pivot Table Mold with Cube Functions

Exploring Cube Functions

Adding Calculations to OLAP Pivot Tables

Creating Calculated Measures

Creating Calculated Members

Managing OLAP Calculations

Performing What-If Analysis with OLAP Data

Next Steps

10 Mashing Up Data with Power Pivot

Understanding the Benefits and Drawbacks of Power Pivot and the Data Model

Merging Data from Multiple Tables Without Using VLOOKUP

Importing 100 Million Rows into a Workbook

Creating Better Calculations Using the DAX Formula Language

Other Benefits of the Power Pivot Data Model in All Editions of Excel

Benefits of the Full Power Pivot Add-in with Excel Pro Plus

Understanding the Limitations of the Data Model

Joining Multiple Tables Using the Data Model in Regular Excel 2016

Preparing Data for Use in the Data Model

Adding the First Table to the Data Model

Adding the Second Table and Defining a Relationship

Tell Me Again—Why Is This Better Than Doing a VLOOKUP?

Creating a New Pivot Table from an Existing Data Model

Getting a Distinct Count

Using the Power Pivot Add-in Excel 2016 Pro Plus

Enabling Power Pivot

Importing a Text File Using Power Query

Adding Excel Data by Linking

Defining Relationships

Adding Calculated Columns Using DAX

Building a Pivot Table

Understanding Differences Between Power Pivot and Regular Pivot Tables

Using DAX Calculations

Using DAX Calculations for Calculated Columns

Using DAX to Create a Calculated Field in a Pivot Table

Filtering with DAX Calculated Fields

Defining a DAX Calculated Field

Using Time Intelligence

Next Steps

11 Dashboarding with Power View and 3D Map

Preparing Data for Power View

Creating a Power View Dashboard

Every New Dashboard Element Starts as a Table

Subtlety Should Be Power View’s Middle Name

Converting a Table to a Chart

Adding Drill-down to a Chart

Beginning a New Element by Dragging a Field to a Blank Spot on the Canvas

Filtering One Chart with Another One

Adding a Real Slicer

Understanding the Filters Pane

Using Tile Boxes to Filter a Chart or a Group of Charts

Replicating Charts Using Multiples

Showing Data on a Map

Using Images

Changing a Calculation

Animating a Scatter Chart over Time

Some Closing Tips on Power View

Analyzing Geographic Data with 3D Map

Preparing Data for 3D Map

Geocoding Data

Building a Column Chart in 3D Map

Navigating Through the Map

Labeling Individual Points

Building Pie or Bubble Charts on a Map

Using Heat Maps and Region Maps

Exploring 3D Map Settings

Fine-Tuning 3D Map

Animating Data over Time

Building a Tour

Creating a Video from 3D Map

Next Steps

12 Enhancing Pivot Table Reports with Macros

Why Use Macros with Pivot Table Reports

Recording a Macro

Creating a User Interface with Form Controls

Altering a Recorded Macro to Add Functionality

Inserting a Scrollbar Form Control

Next Steps

13 Using VBA to Create Pivot Tables

Enabling VBA in Your Copy of Excel

Using a File Format That Enables Macros

Visual Basic Editor

Visual Basic Tools

The Macro Recorder

Understanding Object-Oriented Code

Learning Tricks of the Trade

Writing Code to Handle a Data Range of Any Size

Using Super-Variables: Object Variables

Using With and End With to Shorten Code

Understanding Versions

Building a Pivot Table in Excel VBA

Adding Fields to the Data Area

Formatting the Pivot Table

Dealing with Limitations of Pivot Tables

Filling Blank Cells in the Data Area

Filling Blank Cells in the Row Area

Preventing Errors from Inserting or Deleting Cells

Controlling Totals

Converting a Pivot Table to Values

Pivot Table 201: Creating a Report Showing Revenue by Category

Ensuring That Tabular Layout Is Utilized

Rolling Daily Dates Up to Years

Eliminating Blank Cells

Controlling the Sort Order with AutoSort

Changing the Default Number Format

Suppressing Subtotals for Multiple Row Fields

Handling Final Formatting

Adding Subtotals to Get Page Breaks

Putting It All Together

Calculating with a Pivot Table

Addressing Issues with Two or More Data Fields

Using Calculations Other Than Sum

Using Calculated Data Fields

Using Calculated Items

Calculating Groups

Using Show Values As to Perform Other Calculations

Using Advanced Pivot Table Techniques

Using AutoShow to Produce Executive Overviews

Using ShowDetail to Filter a Recordset

Creating Reports for Each Region or Model

Manually Filtering Two or More Items in a Pivot Field

Using the Conceptual Filters

Using the Search Filter

Setting Up Slicers to Filter a Pivot Table

Using the Data Model in Excel 2016

Adding Both Tables to the Data Model

Creating a Relationship Between the Two Tables

Defining the Pivot Cache and Building the Pivot Table

Adding Model Fields to the Pivot Table

Adding Numeric Fields to the Values Area

Putting It All Together

Next Steps

14 Advanced Pivot Table Tips and Techniques

Tip 1: Force Pivot Tables to Refresh Automatically

Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time

Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending

Tip 4: Turn Pivot Tables into Hard Data

Tip 5: Fill the Empty Cells Left by Row Fields

Option 1: Implement the Repeat All Data Items Feature

Option 2: Use Excel’s Go To Special Functionality

Tip 6: Add a Rank Number Field to a Pivot Table

Tip 7: Reduce the Size of Pivot Table Reports

Delete the Source Data Worksheet

Tip 8: Create an Automatically Expanding Data Range

Tip 9: Compare Tables Using a Pivot Table

Tip 10: AutoFilter a Pivot Table

Tip 11: Force Two Number Formats in a Pivot Table

Tip 12: Create a Frequency Distribution with a Pivot Table

Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs

Tip 14: Apply Restrictions on Pivot Tables and Pivot Fields

Pivot Table Restrictions

Pivot Field Restrictions

Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks

Next Steps

15 Dr. Jekyll and Mr. GetPivotData

Avoiding the Evil GetPivotData Problem

Preventing GetPivotData by Typing the Formula

Simply Turning Off GetPivotData

Speculating on Why Microsoft Forced GetPivotData on Us

Using GetPivotData to Solve Pivot Table Annoyances

Building an Ugly Pivot Table

Building the Shell Report

Using GetPivotData to Populate the Shell Report

Updating the Report in Future Months

Conclusion

Index