Home > Store

Excel 2013 Pivot Table Data Crunching

eBook (Watermarked)

  • Your Price: $25.59
  • List Price: $31.99
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

Also available in other formats.

Register your product to gain access to bonus material or receive a coupon.


  • Copyright 2013
  • Dimensions: 7" x 9-1/8"
  • Pages: 432
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-325940-4
  • ISBN-13: 978-0-13-325940-7


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!

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

About MrExcel Library:  Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these
books will

•   Dramatically increase your productivity—saving you 50 hours a year or more

•   Present proven, creative strategies for solving real-world problems

•   Show you how to get great results, no matter how much data you have

•   Help you avoid critical mistakes that even experienced users make

 CATEGORY: Spreadsheets

COVERS: Microsoft Office Excel 2013

Sample Content

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


    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


Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020