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

Bill Jelen

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

Published with the authorization of Microsoft Corporation by:

Pearson Education, Inc.

Copyright 2022 by Pearson Education, Inc.

All rights reserved. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearson.com/permissions.

No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.

ISBN-13: 978-0-13-752183-8

ISBN-10: 0-13-752183-9

Library of Congress Control Number: 2021948294

ScoutAutomatedPrintCode

Trademarks

Microsoft and the trademarks listed at http://www.microsoft.com on the Trademarks webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners.

Warning and Disclaimer

Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an as is basis. The author, the publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the programs accompanying it.

Special Sales

For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at or (800) 382-3419.

For government sales inquiries, please contact .

For questions about sales outside the U.S., please contact .

EDITOR-IN-CHIEF

Brett Bartow

EXECUTIVE EDITOR

Loretta Yates

SPONSORING EDITOR

Charvi Arora

DEVELOPMENT EDITOR

Rick Kughen

MANAGING EDITOR

Sandra Schroeder

TECHNICAL EDITOR

Bob Umlas

SENIOR PROJECT EDITOR

Tracey Croom

COPY EDITOR

Rick Kughen

INDEXER

Timothy Wright

PROOFREADER

Donna Mulder

EDITORIAL ASSISTANT

Cindy Teeters

COVER DESIGNER

Twist Creative, Seattle

COMPOSITOR

codeMantra

Dedication

To Howie Dickerman at Microsoft. Have a great retirement!

Bill Jelen

Contents

Acknowledgments

A t Microsoft, thanks to the Excel team for always being willing to answer questions about various features. At MrExcel.com, thanks to an entire community of people who are passionate about Excel. Thanks to Bob Umlas for his tech editing of this book and to the Kughens for their project management. Finally, thanks to my wife, Mary Ellen, for her support during the writing process.

Bill Jelen

About the Author

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 2,400 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 64 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. Before founding MrExcel.com, Bill spent 12 years in the trenches, working as a financial analyst for the finance, marketing, accounting, and operations departments of a $500 million public company. He lives in Merritt Island, Florida, with his wife, Mary Ellen.

Introduction

T he pivot table is the single most powerful tool in all of Excel. Pivot tables came along during the 1990s, when Microsoft and Lotus were locked in a bitter battle for dominance of the spreadsheet market. The race to continually add enhanced features to their respective products during the mid-1990s led to many incredible features, but none as powerful as the pivot table.

With a pivot table, you can transform one million rows of transactional data into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables enable you to change your analysis on the fly by simply moving fields from one area of a report to another.

No other tool in Excel gives you the flexibility and analytical power of a pivot table. The Power Query tools that debuted between Excel 2013 and Excel 2016 come close to the power of a pivot table. You will see some Power Query examples in .

What you will learn from this book

It is widely agreed that close to 60 percent of Excel customers leave 80 percent of Excel untouchedthat is, most people do not tap into the full potential of Excels built-in utilities. Of these utilities, the most prolific by far is the pivot table. Despite the fact that pivot tables have been a cornerstone of Excel for almost 30 years, they remain one of the most underutilized tools in the entire Microsoft Office suite.