Excel 2013 Pivot Table Data Crunching

Excel 2013 Pivot Table Data Crunching

by Bill Jelen, Michael Alexander
Released January 2013
Publisher(s): Que
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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!

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

Table of contents

  1. Title Page
  2. Copyright Page
  3. Contents at a Glance
  4. Contents
  5. About the Authors
  6. Dedication
  7. Acknowledgments
  8. We Want to Hear from You!
  9. Reader Services
  10. Introduction
    1. What You Will Learn from This Book
    2. What Is New in Excel 2013’s Pivot Tables
    3. Skills Required to Use This Book
    4. Invention of the Pivot Table
    5. Sample Files Used in This Book
    6. Conventions Used in This Book
  11. 1. Pivot Table Fundamentals
    1. What Is a Pivot Table?
    2. Why Should You Use a Pivot Table?
    3. When Should You Use a Pivot Table?
    4. The Anatomy of a Pivot Table
    5. Pivot Tables Behind the Scenes
    6. Limitations of Pivot Table Reports
    7. Next Steps
  12. 2. Creating a Basic Pivot Table
    1. Preparing Your Data for Pivot Table Reporting
    2. Creating a Basic Pivot Table
    3. Understanding the Recommended PivotTables Feature
    4. Using Slicers
    5. Keeping Up with Changes in Your Data Source
    6. Sharing the Pivot Cache
    7. Saving Time with New Pivot Table Tools
    8. Next Steps
  13. 3. Customizing a Pivot Table
    1. Making Common Cosmetic Changes
    2. Making Report Layout Changes
    3. Customizing the Pivot Table Appearance with Styles and Themes
    4. Changing Summary Calculations
    5. Adding and Removing Subtotals
    6. Changing the Calculation in a Value Field
    7. Next Steps
  14. 4. Grouping, Sorting, and Filtering Pivot Data
    1. Grouping Pivot Fields
    2. Using the PivotTable Fields List
    3. Sorting in a Pivot Table
    4. Filtering the Pivot Table: An Overview
    5. Using Filters for Row and Column Fields
    6. Filtering Using the Filters Area
    7. Filtering Using Slicers and Timelines
    8. Next Steps
  15. 5. Performing Calculations Within Your Pivot Tables
    1. Introducing Calculated Fields and Calculated Items
    2. Creating Your First Calculated Field
    3. Creating Your First Calculated Item
    4. Understanding the Rules and Shortcomings of Pivot Table Calculations
    5. Managing and Maintaining Your Pivot Table Calculations
    6. What’s Next
  16. 6. Using Pivot Charts and Other Visualizations
    1. What Is a Pivot Chart...Really?
    2. Creating Your First Pivot Chart
    3. Keeping Pivot Chart Rules in Mind
    4. Examining Alternatives to Using Pivot Charts
    5. Using Conditional Formatting with Pivot Tables
    6. Creating Custom Conditional Formatting Rules
    7. What’s Next
  17. 7. Analyzing Disparate Data Sources with Pivot Tables
    1. Using Multiple Consolidation Ranges
    2. Using the Internal Data Model
    3. Building a Pivot Table Using External Data Sources
    4. What’s Next
  18. 8. Sharing Pivot Tables with Others
    1. Designing a Workbook as an Interactive Web Page
    2. Sharing Pivot Tables with Other Versions of Office
  19. 9. Working with and Analyzing OLAP Data
    1. What Is OLAP?
    2. Connecting to an OLAP Cube
    3. Understanding the Structure of an OLAP Cube
    4. Understanding the Limitations of OLAP Pivot Tables
    5. Creating Offline Cubes
    6. Breaking Out of the Pivot Table Mold with Cube Functions
    7. Adding Calculations to Your OLAP Pivot Tables
    8. Next Steps
  20. 10. Mashing Up Data with PowerPivot
    1. Understanding the Benefits and Drawbacks of PowerPivot and the Data Model
    2. Joining Multiple Tables Using the Data Model in Regular Excel 2013
    3. Using the PowerPivot Add-In from Excel 2013 Pro Plus
    4. Understanding Differences Between PowerPivot and Regular Pivot Tables
    5. Two Kinds of DAX Calculations
    6. Using a Calendar Table to Enable Time Intelligence Functions
    7. Using Key Performance Indicators
    8. Other Notes About PowerPivot
    9. Next Steps
  21. 11. Dashboarding with Power View
    1. Preparing Your Data for Power View
    2. Creating a Power View Dashboard
    3. Replicating Charts Using Multiples
    4. Showing Data on a Map
    5. Using Table or Card View with Images
    6. Changing the Calculation
    7. Animating a Scatter Chart Over Time
    8. Some Closing Tips on Power View
    9. Animating Pivot Table Data on a Map
    10. Next Steps
  22. 12. Enhancing Your Pivot Table Reports with Macros
    1. Why Use Macros with Your Pivot Table Reports?
    2. Recording Your First Macro
    3. Creating a User Interface with Form Controls
    4. Altering a Recorded Macro to Add Functionality
    5. What’s Next
  23. 13. Using VBA to Create Pivot Tables
    1. Enabling VBA in Your Copy of Excel
    2. Using a File Format That Enables Macros
    3. Visual Basic Editor
    4. Visual Basic Tools
    5. The Macro Recorder
    6. Understanding Object-Oriented Code
    7. Learning Tricks of the Trade
    8. Understanding Versions
    9. Building a Pivot Table in Excel VBA
    10. Dealing with Limitations of Pivot Tables
    11. Pivot Table 201: Creating a Report Showing Revenue by Category
    12. Calculating with a Pivot Table
    13. Using Advanced Pivot Table Techniques
    14. Using the Data Model in Excel 2013
    15. Next Steps
  24. 14. Advanced Pivot Table Tips and Techniques
    1. Tip 1: Force Pivot Tables to Refresh Automatically
    2. Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time
    3. Tip 3: Sort Data Items in a Unique Order Not Ascending or Descending
    4. Tip 4: Turn Pivot Tables into Hard Data
    5. Tip 5: Fill the Empty Cells Left by Row Fields
    6. Tip 6: Add a Rank Number Field to Your Pivot Table
    7. Tip 7: Reduce the Size of Your Pivot Table Reports
    8. Tip 8: Create an Automatically Expanding Data Range
    9. Tip 9: Compare Tables Using a Pivot Table
    10. Tip 10: AutoFilter a Pivot Table
    11. Tip 11: Transpose a Data Set with a Pivot Table
    12. Tip 12: Force Two Number Formats in a Pivot Table
    13. Tip 13: Create a Frequency Distribution with a Pivot Table
    14. Tip 14: Use a Pivot Table to Explode a Data Set to Different Tabs
    15. Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks
    16. What’s Next
  25. 15. Dr. Jekyll and Mr. GetPivotData
    1. Turning Off the Evil GetPivotData Problem
    2. Using GetPivotData to Solve Pivot Table Annoyances
  26. Index

Product information

  • Title: Excel 2013 Pivot Table Data Crunching
  • Author(s): Bill Jelen, Michael Alexander
  • Release date: January 2013
  • Publisher(s): Que
  • ISBN: None