MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Pivot Table Data Crunching Microsoft Excel 2013

January 2013

Pivot Table Data Crunching Microsoft Excel 2013

Crunch Any Data, From Any Source, Quickly and Easily, With Excel 2013 Pivot Tables!

Use Excel 2013 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control!

Order Pivot Table Data Crunching Microsoft Excel 2013 »

Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. In just the first seven chapters, you learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then, you go even further, discovering how to build a comprehensive, dynamic pivot table reporting system for any business task or function.

Learning advanced pivot table and pivot chart techniques for Excel 2013 or the newest Office 365 has never been easier. You’ll find simple, step-by-step instructions, real-world case studies, even complete, easy recipes for solving your most common business analysis problems.

  • Create, customize, and change your pivot tables and pivot charts
  • Transform gigantic data sets into crystal-clear summary reports
  • Summarize and analyze data even faster with new Excel 2013 recommended pivot tables
  • Instantly highlight your most (and least) profitable customers, products, or regions
  • Quickly filter pivot tables using slicers
  • Use dynamic dashboards using Power View to see exactly where your business stands right now
  • Revamp analyses on the fly by simply dragging and dropping fields
  • Build dynamic self-service reporting systems your entire team can use
  • Use PowerPivot or the Data Model to create pivot tables from multiple data sources and worksheets
  • Work with and analyze OLAP data, and much more

Covers all aspects of working with Excel 2013, from mastering the new interface to its breakthrough collaboration and online apps and business intelligence.

For everyone who wants to get the most out of Excel 2013, from casual users to corporate professionals.

Table of Contents


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

About the Authors

Bill Jelen, Excel MVP and the host of, has been using spreadsheets since 1985, and he launched the website in 1998. Bill was a regular guest on "Call for Help with Leo Laporte" and has produced more than 1,200 episodes of his daily video podcast, "Learn Excel from MrExcel." He is the author of 30 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. You will most frequently find Bill taking his show on the road, doing half-day Power Excel seminars wherever he can find a room full of accountants or Excellers. Before founding, Jelen spent 12 years in the trenches working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio with his wife, Mary Ellen, and his sons, Josh and Zeke.

Michael Alexander is a Microsoft Certified Application Developer (MCAD) with over 14 years experience developing business solutions with Microsoft Office, VBA, and .Net. He currently lives in Frisco, Texas, where he works as a senior program manager for a top technology firm. In his spare time, he runs a free tutorial site,, where he shares basic Access and Excel tips with intermediate users.

Order Pivot Table Data Crunching Microsoft Excel 2013 »