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

✍ Scribed by Bill Jelen

• Publisher: Microsoft Press
• Year: 2021
• Tongue: English
• Leaves: 979
• Edition: 1
• Category: Library

✦ Synopsis

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 power―including 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

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