Microsoft 365 Excel: The Only App That Matters

Book Preface

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, dash-boarding, 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.
• Chapter 14 includes examples of 27 different types of lookup formulas.
• Chapter 15 includes 45 examples of different array formulas. It also includes long lists of the different structures and syntactical elements that can be used in array formulas.
• Chapter 17 describes many of the standard PivotTable scenarios that people use and includes a section on the fundamentals of visualizing data.
• Chapter 18 describes the fundamentals of data analysis and business intelligence and provides compre-hensive information on Power Query, Power Pivot, Power BI Desktop, Power BI Online, DAX formulas, and M code formulas. This chapter also includes a table that compares worksheet formulas, standard PivotTables, DAX, and M code.

• Chapter 19 teaches you how to work with big data and complex data analysis projects. This chapter covers advanced topics related to building DAX formulas and M code formulas.
• Chapter 21 teaches you all fundamentals of financial cash flow analysis.

At the end of each chapter, you will find practice problems that you can work through to test what you have learned in each chapter. These problems help make this book a perfect textbook for Excel, analytics, and data analysis classes.
You can read this book straight through or use it as a reference. However, I recommend that you start by reading the whole thing, so you get the story I tell from beginning Excel to the upper levels of Excel. Doing so will help you get the most from the book. Then you can use the book as a reference. You are likely to come back to some of the chapters—like Chapter 14 for lookup formulas, Chapter 15 for array formulas, and Chapter 18 for a comprehensive intro to all the power tools—again and again.
Excel 365 is an app that provides more than ever before in Excel history. You can use this single app to make calculations, build models, and perform data analysis. Never has so much been possible with just one app. This is why I say Excel 365 is the only app that matters!

Who This Book Is For
This book is for almost everyone. If you are interested in reading a story that intertwines worksheet formulas, DAX formulas, M code formulas, standard PivotTables, Power BI, Power Pivot, worksheet modeling, data modeling, dashboarding, and data analysis all into one tale of Excel awesomeness, this book is for you. If you want to be the best in Excel, this book is for you. If you are a business student, this book is a 100% must book for you as it will help you become a hired, promoted, and respected Excel model builder in the working world. If you want a reference book with a lot of useful Excel stuff, this book is for you. If you are a teacher who wants to teach the true power of Excel to make calculations, build models, and perform data analysis, this book is perfect for you! And of course, if you are an Excel person who just wants to have fun with worksheet and data analysis models, this is the book for you!
PC Versus Mac Excel
The differences between Mac Excel and Windows PC–based Excel go way back. I fell in love with Excel in the 1990s, using a Mac computer. Even back then, there were fundamental differences. For example, the average function on a Mac was named AVE, and the average function on a Windows computer was AVERAGE. When I got my first accounting job, I was told to switch to a Windows-based computer. That was smart advice. Even in the 1990s, job security meant learning Excel on a Windows PC–based computer. Three decades later, the gap between PC and Mac has widened: Important tools like Power Pivot, Power BI, and the full version of Power Query are not available on the Mac. As a result, this book teaches you to use Microsoft Excel 365 on a Windows PC.