Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)

Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)

by Bill Jelen
Released December 2021
Publisher(s): Microsoft Press
ISBN: 9780137521821

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

Renowned Excel expert Bill Jelen (MrExcel) helps you crunch data from any source with Excel pivot tables.

Use Excel pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical powerincluding valuable improvements in Excel and Excel in Office 365. Drawing on years of Excel experience, Bill Jelen offers practical “recipes” for solving real business problems, helps you avoid common mistakes, and presents tips and tricks you’ll find nowhere else.

By reading this book, you will:

  • Master easy, powerful ways to create, customize, change, and control pivot tables

  • Control all future pivot tables using new pivot table defaults

  • Transform huge data sets into clear summary reports

  • Instantly highlight your most profitable customers, products, or regions

  • Use Power Query to quickly import, clean, shape, and analyze disparate data sources

  • Build geographical pivot tables with 3D Map

  • Construct and share state-of-the-art dynamic dashboards

  • Revamp analyses on the fly by dragging and dropping fields

  • Build dynamic self-service reporting systems

  • Share your pivot tables with colleagues

  • Create data mashups using the full Power Pivot capabilities of Excel and Excel in Office 365

  • Automate pivot tables with macros and VBA

  • Save time by adapting reports with GetPivotData

  • Discover today’s most useful pivot table tips and shortcuts

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. About the Author
  9. Introduction
  10. Chapter 1 Pivot table fundamentals
    1. Why you should use a pivot table
    2. When to use a pivot table
    3. Anatomy of a pivot table
    4. Pivot tables behind the scenes
    5. Pivot table backward compatibility
    6. Next steps
  11. Chapter 2 Creating a basic pivot table
    1. How to create a basic pivot table
    2. Understanding the Recommended PivotTable and the Analyze Data features
    3. Using slicers to filter your report
    4. Keeping up with changes in the data source
    5. Sharing the pivot cache or creating a new cache
    6. Side effects of sharing a pivot cache
    7. Saving time with PivotTable tools
    8. Next steps
  12. Chapter 3 Customizing a pivot table
    1. Making common cosmetic changes
    2. Making report layout changes
    3. Customizing a pivot table’s appearance with styles and themes
    4. Changing summary calculations
    5. Changing the calculation in a value field
    6. Adding and removing subtotals
    7. Formatting one cell is new in Microsoft 365
    8. Next steps
  13. Chapter 4 Grouping, sorting, and filtering pivot data
    1. Using the PivotTable Fields list
    2. Sorting in a pivot table
    3. Filtering a pivot table: an overview
    4. Using filters for row and column fields
    5. Filtering using the Filters area
    6. Grouping and creating hierarchies in a pivot table
    7. Creating hierarchies
    8. Next steps
  14. Chapter 5 Performing calculations in pivot tables
    1. Introducing calculated fields and calculated items
    2. Creating a calculated field
    3. Creating a calculated item
    4. Understanding the rules and shortcomings of pivot table calculations
    5. Managing and maintaining pivot table calculations
    6. Next steps
  15. Chapter 6 Using pivot charts and other visualizations
    1. What is a pivot chart…really?
    2. Creating a 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. Next steps
  16. Chapter 7 Analyzing disparate data sources with pivot tables
    1. Using the Data Model
    2. Building a pivot table using external data sources
    3. Leveraging Power Query to extract and transform data
    4. Next steps
  17. Chapter 8 Sharing dashboards with Power BI
    1. Getting started with Power BI Desktop
    2. Building an interactive report with Power BI Desktop
    3. Publishing to Power BI
    4. Next steps
  18. Chapter 9 Using cube formulas with the Data Model or OLAP data
    1. Converting your pivot table to cube formulas
    2. Introduction to OLAP
    3. Connecting to an OLAP cube
    4. Understanding the structure of an OLAP cube
    5. Understanding the limitations of OLAP pivot tables
    6. Creating an offline cube
    7. Breaking out of the pivot table mold with cube functions
    8. Adding calculations to OLAP pivot tables
    9. Next steps
  19. Chapter 10 Unlocking features with the Data Model and Power Pivot
    1. Replacing VLOOKUP with the Data Model
    2. Unlocking hidden features with the Data Model
    3. Processing big data with Power Query
    4. Using advanced Power Pivot techniques
    5. Overcoming limitations of the Data Model
    6. Next steps
  20. Chapter 11 Analyzing geographic data with 3D Map
    1. Analyzing geographic data with 3D Map
    2. Next steps
  21. Chapter 12 Enhancing pivot table reports with macros
    1. Using macros with pivot table reports
    2. Recording a macro
    3. Creating a user interface with form controls
    4. Altering a recorded macro to add functionality
    5. Creating a macro using Power Query
    6. Next steps
  22. Chapter 13 Using VBA or TypeScript 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
    15. Using TypeScript in Excel Online to create pivot tables
    16. Next steps
  23. Chapter 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: Using (or prevent using) a custom list for sorting your pivot table
    5. Tip 5: Use pivot table defaults to change the behavior of all future pivot tables
    6. Tip 6: Turn pivot tables into hard data
    7. Tip 7: Fill the empty cells left by row fields
    8. Tip 8: Add a rank number field to a pivot table
    9. Tip 9: Reduce the size of pivot table reports
    10. Tip 10: Create an automatically expanding data range
    11. Tip 11: Compare tables using a pivot table
    12. Tip 12: AutoFilter a pivot table
    13. Tip 13: Force two number formats in a pivot table
    14. Tip 14: Format individual values in a pivot table
    15. Tip 15: Format sections of a pivot table
    16. Tip 16: Create a frequency distribution with a pivot table
    17. Tip 17: Use a pivot table to explode a data set to different tabs
    18. Tip 18: Apply restrictions on pivot tables and pivot fields
    19. Tip 19: Use a pivot table to explode a data set to different workbooks
    20. Tip 20: Use percentage change from previous for year-over-year
    21. Tip 21: Do a two-way VLOOKUP with Power Query
    22. Tip 22: Slicer to control data from two different data sets
    23. Tip 23: Format your slicers
    24. Next steps
  24. Chapter 15 Dr. Jekyll and Mr. GetPivotData
    1. Avoiding the evil GetPivotData problem
    2. Using GetPivotData to solve pivot table annoyances
    3. Next steps
  25. Chapter 16 Creating pivot tables in Excel Online
    1. How to sign in to Excel Online
    2. Creating a pivot table in Excel Online
    3. Changing pivot table options in Excel Online
    4. Where are the rest of the features?
    5. Next steps
  26. Chapter 17 Pivoting without a pivot table using dynamic arrays or Power Query
    1. Creating cross-tabs using an Advanced Filter and a Data Table
    2. Creating a cross-tab report using three dynamic array formulas
    3. Creating a cross-tab using Power Query
    4. Next steps
  27. Chapter 18 Unpivoting in Power Query
    1. Data in headings creates bad pivot tables
    2. Using Unpivot in Power Query to transform the data
    3. Unpivoting from two rows of headings
    4. Unpivoting from a delimited cell to new rows
    5. Conclusion
  28. Afterword
  29. Index
  30. Code Snippets

Product information

  • Title: Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)
  • Author(s): Bill Jelen
  • Release date: December 2021
  • Publisher(s): Microsoft Press
  • ISBN: 9780137521821