What You Will Learn from This Book
What Is New in Excel 2016’s Pivot Tables
Skills Required to Use This Book
Sample Files Used in This Book
Referring to Versions
Why You Should Use a Pivot Table
Advantages of Using a Pivot Table
Pivot Tables Behind the Scenes
Pivot Table Backward Compatibility
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
Fundamentals of Laying Out a Pivot Table Report
Adding Layers to a Pivot Table
Understanding the Recommended Pivot Table Feature
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
Saving Time with New Pivot Table Tools
Making Common Cosmetic Changes
Applying a Table Style to Restore Gridlines
Changing the Number Format to Add Thousands Separators
Using the Traditional Tabular Layout
Controlling Blank Lines, Grand Totals, and Other Settings
Customizing a Pivot Table’s Appearance with Styles and Themes
Modifying Styles with Document Themes
Understanding Why One Blank Cell Causes a Count
Using Functions Other Than Count or Sum
Suppressing Subtotals with Many Row Fields
Adding Multiple Subtotals for One Field
Changing the Calculation in a Value Field
Using % Of to Compare One Line to Another Line
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
4 Grouping, Sorting, and Filtering Pivot Data
Understanding How Excel 2016 Decides What to Group
Including Years When Grouping by Months
Using the PivotTable Fields List
Docking and Undocking the PivotTable Fields List
Rearranging the PivotTable Fields List
Using the Areas Section Drop-Downs
Sorting Customers into High-to-Low Sequence Based on Revenue
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
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
Understanding the Rules and Shortcomings of Pivot Table Calculations
Remembering the Order of Operator Precedence
Using Cell References and Named Ranges
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
6 Using Pivot Charts and Other Visualizations
What Is a Pivot Chart...Really?
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
7 Analyzing Disparate Data Sources with Pivot Tables
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
Understanding Column-Level Actions
8 Sharing Pivot Tables with Others
Designing a Workbook as an Interactive Web Page
Sharing a Link to a Web Workbook
9 Working with and Analyzing OLAP Data
Understanding the Structure of an OLAP Cube
Understanding the Limitations of OLAP Pivot Tables
Breaking Out of the Pivot Table Mold with Cube Functions
Adding Calculations to OLAP Pivot Tables
Performing What-If Analysis with OLAP Data
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
Using the Power Pivot Add-in Excel 2016 Pro Plus
Importing a Text File Using Power Query
Adding Calculated Columns Using DAX
Understanding Differences Between Power Pivot and Regular Pivot Tables
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
11 Dashboarding with Power View and 3D Map
Creating a Power View Dashboard
Every New Dashboard Element Starts as a Table
Subtlety Should Be Power View’s Middle Name
Beginning a New Element by Dragging a Field to a Blank Spot on the Canvas
Filtering One Chart with Another One
Understanding the Filters Pane
Using Tile Boxes to Filter a Chart or a Group of Charts
Replicating Charts Using Multiples
Animating a Scatter Chart over Time
Some Closing Tips on Power View
Analyzing Geographic Data with 3D Map
Building a Column Chart in 3D Map
Building Pie or Bubble Charts on a Map
Using Heat Maps and Region Maps
12 Enhancing Pivot Table Reports with Macros
Why Use Macros with Pivot Table Reports
Creating a User Interface with Form Controls
Altering a Recorded Macro to Add Functionality
Inserting a Scrollbar Form Control
13 Using VBA to Create Pivot Tables
Enabling VBA in Your Copy of Excel
Using a File Format That Enables Macros
Understanding Object-Oriented Code
Writing Code to Handle a Data Range of Any Size
Using Super-Variables: Object Variables
Using With
and End With
to Shorten Code
Building a Pivot Table in Excel VBA
Adding Fields to the Data Area
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
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
Controlling the Sort Order with AutoSort
Changing the Default Number Format
Suppressing Subtotals for Multiple Row Fields
Adding Subtotals to Get Page Breaks
Calculating with a Pivot Table
Addressing Issues with Two or More Data Fields
Using Calculations Other Than Sum
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
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
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
Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks
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
Using GetPivotData to Populate the Shell Report
3.144.25.14