Mike Girvin - Microsoft 365 Excel: The Only App That Matters: Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight

This is the inside of the front cover.

It is intentionally blank.

Microsoft 365 Excel

The Only App That Matters

Excel Worksheet, Power Query,
Power Pivot, Power BI

Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight!

The Definitive Guide to Building Excel Solutions
with the Excel Worksheet, Power Query,
Power Pivot, and Power BI Desktop
Using Worksheet Formulas, DAX Formulas,
M Code Formulas, and Standard PivotTables

by

Mike "excelisfun" Girvin

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

Microsoft 365 Excel: The Only App That Matters

2022 Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an as is basis. The authors and the publisher 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.

Authors: Mike Girvin

Layout: Bronkella Publishing

Copyediting: Kitty Wilson

Cover Design: Shannon Travise

Indexing: Cheryl Lenser

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32953, USA

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: July 2022

Version: 20220621

ISBN: 978-1-61547-070-9 Print, 978-1-61547-156-0 e-Book

Library of Congress Control Number: 2022934005

Dedications

Dedicated to:

My family: Hien Luong Mama Girvin, Isaac Iceman Girvin, and Dennis Big D Ho

My lifelong Oakland friends Dean Washington and Kenny Noline for always inspiring me to be rad

My cool bosses at Highline College, Emily Lardner, Raegan Copeland, and Shawna Freeman, and my awesome Highline Excel teammate, Mary Kiando

About the Author

Mike Girvin has been a Microsoft Excel MVP since 2013, a Highline College business instructor since 2002, and the creator and mastermind of the excelisfun YouTube channel since 2008. The excelisfun channel has been the internet leader in bringing free Excel education to the world since 2008, with more than 3,500 Excel videos and 100 playlists of Excel video topics, including 10 free Excel YouTube courses covering topics such as Excel basics, advanced Excel, data analysis, analytics, statistics, math, and much more. Mike has also authored a number of Excel books and DVDs and has won numerous awards for teaching Excel. Before joining academia in 2002, Mike (nicknamed Gel) ran the boomerang manufacturing company Gel Boomerangs, in Oakland, California, from 1984 to 2002 and won numerous boomerang design and competition awards. It was while Mike was running Gel Boomerangs in the 1990s that Steve Kavanaugh showed him Excel for the first time. From that point forward, the power and fun of Excel was inescapable, and Mike has gone on to be an Excel teacher for the world. Currently, when Mike is not creating Excel solutions, you can find him racing and parking BMX bikes with fellow rad old guys.

Acknowledgements

My number-one Excel guy in the world is Bill MrExcel Jelen. He is the first Excel guy to make Excel videos, and he has inspired me and many other Excel people to make and share videos. MrExcel also started the MrExcel Message Board, where I learned many of my advanced Excel skills. In addition, MrExcel has written more than 60 Excel books! I also want to thank the smartest Power Query guy I know, Bill Power Query Poet Szysz. He has kindly and graciously helped me to learn Power Query and M code. In addition, without Marco Russo and Alberto Ferraris DAX books and live classes, I could not have learned DAX. Still further, without the editing superhero skills of Kitty Wilson, I never could have created this book. With so many great edits, it is really Superhero Kitty who wrote this book! Thanks to Cheryl Lenser for creating a rad index and Tricia Bronkella for layout. Finally, I want to thank the more than 750,000 subscribers to my excelisfun YouTube channel because in the comments below every one of the videos that I post, I get to learn new things about making efficient Excel solutions and having fun doing it!

Table of Contents

Introduction

There has never been a book like this in Excel history. This book covers worksheet formulas, standard PivotTables, array formulas, Power Query, M code formulas, Power Pivot, DAX formulas, Power BI Desktop, Power BI Online, worksheet model theory, data analysis theory, rules for visualizing data, dashboarding, financial cash analysis, simple linear regression, and even some history of Excel, data analysis, finance, and math. Microsoft 365 Excel (and the free download Power BI Desktop) really does offer all this and more.

Note: The official name of the app discussed throughout this book is Microsoft 365 Excel, but I often call it Excel 365 for short.

This is a crazy book. It is too much. It is for the hard core who want to know it all: the hows and the whys! But this book is also for anyone who uses Excel to create solutions to make working life easier.

Excel has expanded its capabilities outward to a new level with the addition of:

• Dynamic spilled array formulas that make all worksheet formulas array formulas
• Power BI and Power Pivot DAX formulas that can handle big data and perform magic calculations, iterating over tables within tables at any grain
• Power Query M code formulas that behave more like SQL than Excel formulas and can transform data with such ease that no other tool inside or outside Excel and Power BI can compete
• Power BI Desktop and its interactive and sharable visualizations (Yes, I consider this free download part of Excel.)

Microsoft 365 Excel is amazing and powerful. It can make calculations and data analysis much easier than any other version of Excel in history.

The first 16 chapters of this book (about 400 pages) are all about using worksheet formulas to build worksheet models. Chapter 17 (about 100 pages) is all about standard PivotTables, Excel charts, visualizing data, dashboarding, and other data analysis features. Chapter 18 (about 150 pages) includes a full description of data analysis theory and terminology, as well as an introduction to data modeling, reporting, and dashboarding with three main tools: worksheets and standard PivotTables, PowerPivot with Power Query and DAX formulas, and Power BI Desktop and Power BI Online with Power Query and DAX formulas. Chapter 19 (about 200 pages) covers big data, complex data modeling projects, and advanced DAX and M code formulas. Chapter 20 takes a brief look at recorded macros. Chapter 21 covers financial cash flow worksheet functions and the basics of financial model building. Chapter 22 looks at building simple linear regression models.

This book includes some detail that exists in no other book. For example:

• Chapter 12 includes 25 examples of the different types of worksheet formulas you can create. This chapter also lists all the types of formula input that are possible.
• Chapter 13 includes 38 examples of the different type of logical tests and formulas available in Excel. This is particularly important in a book of everything because many of the formulas people build in Excel models do not add or count columns of values; rather, many formulas are based on logical tests that add, count, filter, or do other calculations based on conditions and criteria. Chapter 13 even includes a big table that shows every possible D function and its IFS aggregate function (SUMIFS, COUNTIFS, etc.) equivalent.