book

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

Name: Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)
Author: Bill Jelen
ISBN: 9780137521821

by Bill Jelen

December 2021

Beginner to intermediate

544 pages

14h 37m

English

Microsoft Press

Read now

Unlock full access

Cover Page
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 tableWhen to use a pivot tableAnatomy of a pivot tablePivot tables behind the scenesPivot table backward compatibilityNext steps

Chapter 2 Creating a basic pivot table
How to create a basic pivot tableUnderstanding the Recommended PivotTable and the Analyze Data featuresUsing slicers to filter your reportKeeping up with changes in the data sourceSharing the pivot cache or creating a new cacheSide effects of sharing a pivot cacheSaving time with PivotTable toolsNext steps
Chapter 3 Customizing a pivot table
Making common cosmetic changesMaking report layout changesCustomizing a pivot table’s appearance with styles and themesChanging summary calculationsChanging the calculation in a value fieldAdding and removing subtotalsFormatting one cell is new in Microsoft 365Next steps
Chapter 4 Grouping, sorting, and filtering pivot data
Using the PivotTable Fields listSorting in a pivot tableFiltering a pivot table: an overviewUsing filters for row and column fieldsFiltering using the Filters areaGrouping and creating hierarchies in a pivot tableCreating hierarchiesNext steps
Chapter 5 Performing calculations in pivot tables
Introducing calculated fields and calculated itemsCreating a calculated fieldCreating a calculated itemUnderstanding the rules and shortcomings of pivot table calculationsManaging and maintaining pivot table calculationsNext steps
Chapter 6 Using pivot charts and other visualizations
What is a pivot chart…really?Creating a pivot chartKeeping pivot chart rules in mindExamining alternatives to using pivot chartsUsing conditional formatting with pivot tablesCreating custom conditional formatting rulesNext steps
Chapter 7 Analyzing disparate data sources with pivot tables
Using the Data ModelBuilding a pivot table using external data sourcesLeveraging Power Query to extract and transform dataNext steps
Chapter 8 Sharing dashboards with Power BI
Getting started with Power BI DesktopBuilding an interactive report with Power BI DesktopPublishing to Power BINext steps
Chapter 9 Using cube formulas with the Data Model or OLAP data
Converting your pivot table to cube formulasIntroduction to OLAPConnecting to an OLAP cubeUnderstanding the structure of an OLAP cubeUnderstanding the limitations of OLAP pivot tablesCreating an offline cubeBreaking out of the pivot table mold with cube functionsAdding calculations to OLAP pivot tablesNext steps
Chapter 10 Unlocking features with the Data Model and Power Pivot
Replacing VLOOKUP with the Data ModelUnlocking hidden features with the Data ModelProcessing big data with Power QueryUsing advanced Power Pivot techniquesOvercoming limitations of the Data ModelNext steps
Chapter 11 Analyzing geographic data with 3D Map
Analyzing geographic data with 3D MapNext steps
Chapter 12 Enhancing pivot table reports with macros
Using macros with pivot table reportsRecording a macroCreating a user interface with form controlsAltering a recorded macro to add functionalityCreating a macro using Power QueryNext steps
Chapter 13 Using VBA or TypeScript to create pivot tables
Enabling VBA in your copy of ExcelUsing a file format that enables macrosVisual Basic EditorVisual Basic toolsThe macro recorderUnderstanding object-oriented codeLearning tricks of the tradeUnderstanding versionsBuilding a pivot table in Excel VBADealing with limitations of pivot tablesPivot table 201: Creating a report showing revenue by categoryCalculating with a pivot tableUsing advanced pivot table techniquesUsing the Data Model in ExcelUsing TypeScript in Excel Online to create pivot tablesNext steps
Chapter 14 Advanced pivot table tips and techniques
Tip 1: Force pivot tables to refresh automaticallyTip 2: Refresh all pivot tables in a workbook at the same timeTip 3: Sort data items in a unique order, not ascending or descendingTip 4: Using (or prevent using) a custom list for sorting your pivot tableTip 5: Use pivot table defaults to change the behavior of all future pivot tablesTip 6: Turn pivot tables into hard dataTip 7: Fill the empty cells left by row fieldsTip 8: Add a rank number field to a pivot tableTip 9: Reduce the size of pivot table reportsTip 10: Create an automatically expanding data rangeTip 11: Compare tables using a pivot tableTip 12: AutoFilter a pivot tableTip 13: Force two number formats in a pivot tableTip 14: Format individual values in a pivot tableTip 15: Format sections of a pivot tableTip 16: Create a frequency distribution with a pivot tableTip 17: Use a pivot table to explode a data set to different tabsTip 18: Apply restrictions on pivot tables and pivot fieldsTip 19: Use a pivot table to explode a data set to different workbooksTip 20: Use percentage change from previous for year-over-yearTip 21: Do a two-way VLOOKUP with Power QueryTip 22: Slicer to control data from two different data setsTip 23: Format your slicersNext steps
Chapter 15 Dr. Jekyll and Mr. GetPivotData
Avoiding the evil GetPivotData problemUsing GetPivotData to solve pivot table annoyancesNext steps
Chapter 16 Creating pivot tables in Excel Online
How to sign in to Excel OnlineCreating a pivot table in Excel OnlineChanging pivot table options in Excel OnlineWhere 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 TableCreating a cross-tab report using three dynamic array formulasCreating a cross-tab using Power QueryNext steps
Chapter 18 Unpivoting in Power Query
Data in headings creates bad pivot tablesUsing Unpivot in Power Query to transform the dataUnpivoting from two rows of headingsUnpivoting from a delimited cell to new rowsConclusion
Afterword
Index
Code Snippets

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

CHAPTER 17. Pivoting without a pivot table using dynamic arrays or Power Query

In this chapter, you will learn how to create cross-tabular reports without using a pivot table:

Use Advanced Filter and a Data Table
Use Dynamic Arrays
Use the Pivot command in Power Query

Before Microsoft called them pivot tables, the type of report with headings down the side and across the top was called a “cross-tabular report” or simply a “cross-tab report.”

Cross-tab reports existed before pivot tables. I regularly created them as part of my job in finance and accounting. After a couple of Advanced Filters, a transpose, a DSUM, and a Data Table, you were done. Pivot tables made all that simpler.

But today, 30 years after the invention of the pivot table, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel Inside Out (Office 2021 and Microsoft 365)

Publisher Resources

ISBN: 9780137521821

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Bill Jelen

CHAPTER 17. Pivoting without a pivot table using dynamic arrays or Power Query

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.