Microsoft Excel Pivot Table Data Crunching Including Dynamic Arrays, Power Query, and Copilot

Microsoft Excel Pivot Table Data Crunching Including Dynamic Arrays, Power Query, and Copilot

by Bill Jelen
Released December 2024
Publisher(s): Microsoft Press
ISBN: 9780135408773

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

Learn to use pivot tables and pivot charts in Microsoft Excel to produce powerful, dynamic reports in minutes: take control of your data and your business! Even if youve never created a pivot table before, this book will help you leverage their flexibility and analytical powerand includes exploration of new array functions and Copilot capabilities in analysis creation. Drawing on more than 30 years of cutting-edge Excel experience, MVP Bill Jelen (MrExcel) shares practical recipes for solving real business problems, expert insights for avoiding mistakes, and advanced tips and tricks youll find nowhere else.

By reading this book, you will:

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

  • Use Copilot and Analyze Data features to create pivot tables

  • Transform huge datasets into clear summary reports

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

  • Pivot data with powerful dynamic arrays and Power Query

  • Use PIVOTBY and GROUPBY functions to create reports without needing to refresh

  • Build geographical pivot tables with 3D Maps

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

  • Revamp analyses on the fly by dragging and dropping fi elds

  • Build dynamic self-service reporting systems

  • Share your pivot tables with colleagues

  • Create data mashups using the full Power Pivot capabilities in modern Excel versions

  • Generate pivot tables using either VBA on the Desktop or Typescript in Excel Online or on the Desktop

  • Embrace GetPivotData for better looking reports and learn how to prevent it for quick reporting

  • Unpivot source data so its easier to use

About This Book

  • For everyone who wants to get more done with Microsoft Excel in less time

  • For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. About the author
  9. Introduction
    1. What you will learn from this book
    2. What is new in Microsoft Excel’s pivot tables
    3. Invention of the pivot table
    4. Creating a pivot table using Artificial Intelligence
    5. Who this book is for
    6. How this book is organized
    7. About the companion content
    8. System requirements
    9. Compact versus tabular layout
    10. Errata, updates, and book support
    11. Stay in touch
  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. Format your source data before creating a pivot table
    2. How to create a basic pivot table
    3. Understanding the Analyze Data, Copilot, and Recommended PivotTable features
    4. Using slicers to filter your report
    5. Keeping up with changes in the data source
    6. Sharing the pivot cache or creating a new 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. Next steps
  13. Chapter 4. Grouping, sorting, and filtering pivot data
    1. Using the PivotTable Fields pane
    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. 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 XLOOKUP 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. Unblock workbooks from the web
    4. Visual Basic Editor
    5. Visual Basic tools
    6. The macro recorder
    7. Understanding object-oriented code
    8. Learning tricks of the trade
    9. Understanding versions
    10. Building a pivot table in Excel VBA
    11. Dealing with the limitations of pivot tables
    12. Pivot table 201: Creating a report showing revenue by category
    13. Calculating with a pivot table
    14. Using advanced pivot table techniques
    15. Using the Data Model in Excel
    16. Using TypeScript in Excel Online to create pivot tables
    17. 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: Create a 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 aggravating 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 formulas, Python, or Power Query
    1. Creating cross-tabs using GROUPBY or PIVOTBY functions
    2. Creating a cross-tab using Power Query
    3. Creating a cross-tab using Python in Excel
    4. Next steps
  27. Chapter 18. Using Artificial Intelligence and Copilot for building pivot tables
    1. Using AI to add a column to your data before pivoting
    2. Using Analyze Data to find trends and ask questions
    3. Using Copilot in Excel to create pivot tables or pivot charts
    4. Using Copilot in Excel to generate formula columns
    5. Using Advanced Copilot in Excel with Python
    6. Using Copilot in OneDrive to analyze financial statements in Excel
    7. Using AI to find and summarize Excel YouTube videos
    8. Using Copilot to write VBA, Python, RegEx, or Power Query M Code
    9. Fun: Using Copilot to write Excel poetry or songs
    10. Next steps
  28. Chapter 19. 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
  29. Afterword
  30. Index
  31. Code Snippets

Product information

  • Title: Microsoft Excel Pivot Table Data Crunching Including Dynamic Arrays, Power Query, and Copilot
  • Author(s): Bill Jelen
  • Release date: December 2024
  • Publisher(s): Microsoft Press
  • ISBN: 9780135408773