Excel 2013 Pivot Table Data Crunching Book

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

Using Functions Other Than Count or Sum

Adding and Removing Subtotals

Suppress Subtotals When You Have 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 Percent of Running Total

Display Change from a Previous Field

Tracking Percent of Parent Item

Track Relative Importance with the Index Option

Next Steps

Chapter 4 Grouping, Sorting, and Filtering Pivot Data

Grouping Pivot Fields

Grouping Date Fields

Including Years When Grouping by Months

Grouping Date Fields by Week

Ungrouping

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 the 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

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 Report 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

Chapter 5 Performing Calculations Within Your Pivot Tables

Introducing Calculated Fields and Calculated Items

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

Method 2: Use a Formula Outside Your Pivot Table to Create the Calculated Field5

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

Creating Your First Calculated Field

Creating Your First 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 Your Pivot Table Calculations

Editing and Deleting Your Pivot Table Calculations

Changing the Solve Order of Your Calculated Items

Documenting Your Formulas

What’s Next

Chapter 6 Using Pivot Charts and Other Visualizations

What Is a Pivot Chart...Really?

Creating Your First Pivot Chart

Keeping Pivot Chart Rules in Mind

Changes in the Underlying Pivot Table Affect Your Pivot Chart

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

A Few Formatting Limitations Still Exist in Excel 2013

Examining Alternatives to Using Pivot Charts

Method 1: Turn Your 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 Your Chart

Using Conditional Formatting with Pivot Tables

Creating Custom Conditional Formatting Rules

What’s Next

Chapter 7 Analyzing Disparate Data Sources with Pivot Tables

Using Multiple Consolidation Ranges

Creating a Multiple Consolidation Pivot Table

Analyzing the Anatomy of a Multiple Consolidation Ranges Pivot Table

The Row Field

The Column Field

The Value Field

The Page Fields

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

Create 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

What’s Next

Chapter 8 Sharing Pivot Tables with Others

Designing a Workbook as an Interactive Web Page

Sharing a Link to Your Web Workbook

Embedding Your Workbook in a Blog Post or Your Web Page

Sharing Pivot Tables with Other Versions of Office

Chapter 9 Working with and Analyzing OLAP Data

What Is OLAP?

Connecting to an OLAP Cube

Understanding the Structure of an OLAP Cube

Understanding the Limitations of OLAP Pivot Tables

Creating Offline Cubes

Breaking Out of the Pivot Table Mold with Cube Functions

Adding Calculations to Your OLAP Pivot Tables

Creating Calculated Measures

Creating Calculated Members

Managing Your OLAP Calculations

Performing What-If Analysis with OLAP Data

Next Steps

Chapter 10 Mashing Up Data with PowerPivot

Understanding the Benefits and Drawbacks of PowerPivot and the Data Model

Merge Data from Multiple Tables Without Using VLOOKUP

Import 100 Million Rows into Your Workbook

Create Better Calculations Using the DAX Formula Language

Other Benefits of the PowerPivot Data Model in All Editions of Excel

Benefits of the Full PowerPivot Add-In with Excel Pro Plus

Understanding the Limitations of the Data Model

Joining Multiple Tables Using the Data Model in Regular Excel 2013

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?

Using QuickExplore

Creating a New Pivot Table from an Existing Data Model

Getting a Distinct Count

Using the PowerPivot Add-In from Excel 2013 Pro Plus

Enabling PowerPivot

Import a Text File

Add Excel Data by Copying and Pasting

Add Excel Data by Linking

Define Relationships

Add Calculated Columns Using DAX

Build a Pivot Table

Understanding Differences Between PowerPivot and Regular Pivot Tables

Two Kinds of DAX Calculations

DAX Calculations for Calculated Columns

Using RELATED() to Base a Column Calculation on Another Table

Using DAX to Create a Calculated Field in the Pivot Table

DAX Calculated Fields Implicitly Respect the Filters

Define a DAX Calculated Field

Is Unfilter Even a Word?

CALCULATE Is a Super-Enhanced Version of SUMIFS

Adding Fields to the Values Area Generates DAX Calculated Fields

Using a Calendar Table to Enable Time Intelligence Functions

Adding the Data to PowerPivot and Formatting It

PowerPivot Doesn’t Automatically Sort by Custom Lists

Create a PivotTable and Marvel at the Results

This Is a Discussion About Time Intelligence

Using Key Performance Indicators

Setting up a KPI Compared to an Absolute Value

Setting Up a KPI Compared to a Calculated Target Value

Other Notes About PowerPivot

Combination Layouts

Getting Your Data into PowerPivot with SQL Server

Other Issues

Next Steps

Chapter 11 Dashboarding with Power View

Preparing Your 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

Convert the Table to a Chart

Add Drill-Down to a Chart

To Begin a New Element, Drag a Field to a Blank Spot on the Canvas

Every Chart Point Is a Filter for Every Other Element

Adding a Real Slicer

The Filter Pane Can Be Confusing

Use Tile Boxes to Filter One or a Group of Charts

Replicating Charts Using Multiples

Showing Data on a Map

Using Table or Card View with Images

Changing the Calculation

Animating a Scatter Chart Over Time

Some Closing Tips on Power View

Animating Pivot Table Data on a Map

Next Steps

Chapter 12 Enhancing Your Pivot Table Reports with Macros

Why Use Macros with Your Pivot Table Reports?

Recording Your First Macro

Creating a User Interface with Form Controls

Altering a Recorded Macro to Add Functionality

What’s Next

Chapter 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 Any Size Data Range

Using Super-Variables: Object Variables

Using With and End With to Shorten Code

Understanding Versions

Code for New Features Won’t Work in Previous 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

Learning Why You Cannot Affect a Pivot Table by Inserting or Deleting Cells

Controlling Totals

Determining the Size of a Finished Pivot Table to Convert It to Values

Pivot Table 201: Creating a Report Showing Revenue by Category

Ensuring Table 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

Copying a Finished Pivot Table as Values to a New Workbook

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

Calculated Data Fields

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 PivotField

Using the Conceptual Filters

Using the Search Filter

Setting up Slicers to Filter a Pivot Table

Using the Data Model in Excel 2013

Add Both Tables to the Data Model

Create a Relationship Between the Two Tables

Define the Pivot Cache and Build the Pivot Table

Add Model Fields to the Pivot Table

Add Numeric Fields to the Values Area

Putting It All Together

Next Steps

Chapter 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 Your Pivot Table

Tip 7: Reduce the Size of Your Pivot Table Reports

Delete Your Source Data Tab

Tip 8: Create an Automatically Expanding Data Range

Tip 9: Compare Tables Using a Pivot Table

Tip 10: AutoFilter a Pivot Table

Tip 11: Transpose a Data Set with a Pivot Table

Step 1: Combine All Non-Column-Oriented Fields into One Dimension Field

Step 2: Create a Multiple Consolidation Ranges Pivot Table

Step 3: Double-Click the Grand Total Intersection of Row and Column

Step 4: Parse Your Dimension Column into Separate Fields

Tip 12: Force Two Number Formats in a Pivot Table

Tip 13: Create a Frequency Distribution with a Pivot Table

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

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

What’s Next

Chapter 15 Dr. Jekyll and Mr. GetPivotData

Turning Off the Evil GetPivotData Problem

Preventing GetPivotData by Typing the Formula

GetPivotData Is Surely Evil–Turn It Off

Why Did Microsoft Force GetPivotData on Us?

Using GetPivotData to Solve Pivot Table Annoyances

Build an Ugly Pivot Table

Build the Shell Report

Using GetPivotData to Populate the Shell Report

Updating the Report in Future Months

9780789748751 TOC 12/18/2013