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

By Mike Girvin

This is a book about Microsoft 365 Excel, or Excel 365. No other version in history has as much power, as many features or more possibilities than does Excel 365. With a new formula calculations engine and many new built-in functions, creating formula solutions and business models in Excel 365 is dramatically easier than at any time in the history of spreadsheets. In addition, with the new data tools like Power Query, Power Pivot and Power BI, performing data analysis to make data driven decisions can be easily done on data with different structures, with different sources and on small and big data alike. With this exciting new Excel 365 version, we will learn three types for formulas: Worksheet, M Code and DAX, and we will learn three types of Reporting/Dashboarding tools: Standard PivotTables, Data Model PivotTables and Power BI Visualizations. This means that the New Excel 365 is the only app that matters in our age of analytics and data driven decisions.Who is this book/class for? Everyone. The book starts at the beginning and moves you to an advanced level by telling a logical story about how to use Excel to solve calculation-based problems and answer crucial questions.

• Language: English
• Publisher: Independent Publishers Group
• Release date: Apr 1, 2021
• ISBN: 9781615471560

Book preview

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 Ferrari’s 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

Microsoft 365 Excel: The Only App That Matters

Dedications

About the Author

Acknowledgements

Introduction

Who This Book Is For

PC Versus Mac Excel

Following Along with Excel Files

Chapter 1: Why Excel 365?

The Worksheet Formula Calculation Engine

Array Formulas

New Array Functions: UNIQUE and FILTER

The New XLOOKUP and LET Functions

Power Query and the M Code Formula Language

Power Pivot, the Data Model, and DAX Formulas

Power BI Desktop for Creating Visualizations and Reports

Key Concepts in Chapter 1

Chapter 2: Why the Spreadsheet Was Invented

Key Concepts in Chapter 2

Chapter 3: What Does Excel Do?

Key Concepts in Chapter 3

Chapter 4: Key Concepts Used to Create Excel Solutions

Key Concepts in Chapter 4

Chapter 5: The Structure of Excel Files and Worksheets

The Two-Way Grid: Cells, Worksheets, and Workbooks

Worksheet Navigation

Excel Data and Default Alignment in Cells

The Excel Command Environment

The Quick Access Toolbar

The File Menu and Backstage View

File Management

Key Concepts in Chapter 5

Keyboard Shortcuts Learned in Chapter 5

Chapter 6: Keyboard Keys and Shortcuts

Keyboard Shortcuts

Key Concepts in Chapter 6

Keyboard Shortcuts Learned in Chapter 6

Chapter 7: Worksheet Efficiency Tricks

Using Worksheet Mouse Cursors

Entering Content into Cells with Five Different Keyboard Shortcuts

Selecting a Range of Cells

Jumping Around in a Worksheet

Key Concepts in Chapter 7

Chapter 8: Worksheet Formulas, Formatting, and Setup to Solve Problems

Creating a Sample Worksheet

Using the Proper Dataset Format

Adding Style Formatting

Entering the Data

Using Worksheet Formulas, Functions, Cell References, and Aggregate Operations

Checking Your Work and Verifying Your Formulas

Using Relative Cell References

Adding Number Formatting

Using the Format Painter

Inserting Rows into a Worksheet

Calculating Percentages

Understanding Absolute Cell References

Documenting Your Work

Spilling Dynamic Array Formulas and Array Operations

Using the Excel Table Feature

Sorting and Filtering

Setting Up a Page for Printing

Adding New Data to a Dynamic Solution by Using a Text File

Converting a Solution to a PDF File

Key Concepts in Chapter 8

Keyboard Shortcuts Learned in Chapter 8

Practice Problems for Chapter 8

Chapter 9: A Golden Rule for Building Excel Models

How Violating the Golden Rule Can Get You into Trouble

Benefits of Following the Golden Rule

Example 1: Commission Pay Worksheet Formula

Example 2: Monthly Expense Formula with Hard-Coded Formula Input

Example 3: COUNTIFS Worksheet Function to Count with a Condition

Example 4: UNIQUE Array Function and Data Validation List

Example 5: Conditional Formatting

What-If Analysis and Excel Models

Key Concepts in Chapter 9

Keyboard Shortcuts Learned in Chapter 9

Practice Problems for Chapter 9

Chapter 10: Knowing When to Use the ROUND Function

The ROUND Function Rule

Example 1: Using the ROUND Function to Get Accurate Totals for Tax Deduction Calculations

Example 2: Rounding to the Dollar

Example 3: Rounding for a Sales Invoice

Example 4: Rounding a Percentage

Key Concepts in Chapter 10

Practice Problems for Chapter 10

Chapter 11: Date and Time Number Formatting and Formulas

Date Number Formatting, Date Serial Numbers, and Date Formulas

The Format Cells Dialog Box and Custom Date Number Formatting

Calculating the Difference Between Two Dates

Counting Workdays with NETWORKDAYS.INTL

Adding Days to Dates and Subtracting Days from Dates

Time Number Formatting, Time Serial Numbers, and Time Formulas

Example 1: Calculating Hours Worked

Example 2: Calculating Hours Worked When There Is a Lunch Break

Example 3: Using the Time Number Format to Show Hours Greater Than 24 Hours

Example 4: Calculating Hours Worked for a Night Shift

Example 5: Rounding a Time Value to Within 5 Minutes with the MROUND Function

Keyboard Shortcuts and Custom Date and Time Number Formatting

Key Concepts in Chapter 11

Keyboard Shortcuts Learned in Chapter 11

Practice Problems for Chapter 11

Chapter 12: Worksheet Formula Types and Formula Elements

Worksheet Formula Types

Worksheet Formula Elements

Order of Operator Precedence in Worksheet Formulas

My Golden Rule, the ROUND Function, and Number Formatting As a Façade

Example 1: Number Formula for Average Customer Ratings

Example 2: Using a Text Formula to Join First and Last Names in a Cell

Example 3: Using a Logical Formula to Determine Whether Accounts Are in Balance

Examples 4 Through 6: Using a Cost of Goods Sold Formula with a Spilled Array, Relative Cell References, or Table Formula Nomenclature

Example 7: Dynamically Extracting the Top N Values by Using the Comma Reference Operator

Example 8: Adding the Top Three Boomerang MTA Scores and Ranking with the RANK.EQ Worksheet Function

Example 9: Looking Up the Price for Invoicing by Using the XLOOKUP Worksheet Function

Example 10: Looking Up All the Rows in a Column by Using the XLOOKUP Function

Example 11: Looking Up a List of Student Classes by Using the FILTER Function

Example 12: Using the SUMIFS Worksheet Function to Create a Sales and Costs Report by Product

Example 13: Conducting a Customer Credit Analysis with the AND Function and Mixed Cell References

Example 14: Knowing When Mixed Cell References Are Required

Example 15: Conditionally Formatting a Row with a Logical Formula and Mixed Cell References

Example 16: Extracting Records from an Excel Table with the FILTER Function and Defined Names

Example 17: Conditional Formatting for Dynamic Spilled Array Formulas

Example 18: Building a Stock Value Table with Data Types and the Dot Operator for a Stock Data Type

Example 19: Building Formulas Between Worksheets by Using Worksheet References

Examples 20 and 21: Building Formulas Between Workbooks by Using Workbook References

Examples 22 and 23: The Space Operator for Intersecting Values and the Better Alternative, XLOOKUP

Example 24: Using the Colon Reference Operator and 3D Cell References to Add Across Multiple Worksheets

Example 25: Using Table Formula Nomenclature Absolute and Mixed References

Key Concepts in Chapter 12

Keyboard Shortcuts Learned in Chapter 12

Practice Problems for Chapter 12

Chapter 13: Logical Tests and Related Functions (AND, ISNUMBER, IF, IFS, FILTER, SUMIFS, and More)

Comparison Operators

Logical Tests

Example 1: Creating a Logical Test with a Single Condition

Example 2: Using Math Operations to Convert TRUE to 1 and FALSE to 0

Example 3: Using IS Worksheet Functions

Types of Logical Tests: Single Condition, Contains, NOT, AND, BETWEEN, and OR

Logical Functions That Deliver a Single Scalar Boolean Value: AND, OR, and NOT

Example 4: Using an AND Function to Run an AND Logical Test

Example 5: Using an OR Function to Run an OR Logical Test

Example 6: Creating an AND Boolean Math Formula to Run an AND Logical Test

Example 7: Creating an OR Boolean Math Formula to Run an OR logical Test

Example 8: Nesting the OR Function Inside the AND Function to Run a Complex Logical Test

Example 9: Using a None Are True Logical Test or a NOT NOT Logical Test

Example 10: Using a BETWEEN Logical Test

Example 11: Conditionally Formatting a Row with an AND Logical Test and Mixed Cell References

Example 12: Creating a Test Where Any Nonzero Number Is Considered a TRUE Value

Example 13: Using the SEARCH and ISNUMBER Functions in a Contains Logical Test

Example 14: Using Formula Wildcards to Create a Contains Logical Test Inside the COUNTIFS Worksheet Function

Logical Worksheet Functions That Deliver Values: IF, IFS, IFNA, IFERROR, and FILTER

Example 15: Using the IF Function to Deliver One of Two Number Values: 750 or 0

Example 16: Using the IF Function to Deliver One of Two Text Values: Over or Under

Examples 17 and 18: Using the IF Function to Deliver a Formula or Show Nothing

Examples 19 and 20: Using the AND and OR Functions Inside the IF Function

Example 21: Creating an AND Logical Test That Can Spill Results

Example 22: Using the IFS Function When You Have Three or More Things to Put in a Cell

Examples 23 and 24: Using the IFNA Function to Replace the #N/A Error with Something Different

Examples 25: Using the IF Function Instead of IFERROR

Examples 26: Using the IF Function Inside an Aggregate Function to Make a Conditional Calculation

Examples 27: Putting a Boolean AND Logical Test Inside the FILTER Function to Filter a Dataset

Examples 28: Putting a Boolean OR Logical Test Inside the FILTER Function to Filter a Dataset

Example 29: Comparing Two Lists and Extracting Results Using XMATCH and Other Functions

Using IFS Aggregate Functions (COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS) to Calculate Based on an AND Logical Test

Example 30: Using IFS Aggregate Functions to Make Calculations with Three Criteria

Example 31: Taking a Closer Look at Using the SUMIFS Function to Run an AND Logical Test with Three Criteria

Example 32: Using the SUMIFS Function with Three Criteria in a Cross-Tabulated Report

Example 33: Using a BETWEEN Logical Test with SUMIFS and MAXIFS in a Monthly Sales Report

Example 34: Using the SUMIFS Function to Do an OR Logical Test on a Single Column

Example 35: Carrying Out Aggregate Operations with an OR Logical Test on a Single Column

Example 36: Conducting Aggregate Operations with an AND and OR Logical Test

Example 37: Conducting Aggregate Operations with an OR Logical Test on Two Columns

Example 38: Using Database Worksheet Functions

Key Concepts in Chapter 13

Practice Problems for Chapter 13

Chapter 14: Worksheet Lookup Functions and Formulas

Exact Match Versus Exact Match or Next Smaller Item Lookups

The XLOOKUP Function

Example 1: Using the XLOOKUP Function to Look Up Product Price Based on an Exact Match Lookup and Data Validation List

Example 2: Using the XLOOKUP Function to Look Up Tax Rate Based on an Exact Match or Next Smaller Item Lookup

Example 3: Using the XLOOKUP Function for an Exact Match or to Find the Next Biggest Value

Example 4: Using the XLOOKUP Function to Do a Wildcard Lookup

Example 5: Using the XLOOKUP Function to Get the First Item When There Are Duplicates

Example 6: Using the XLOOKUP Function to Get the Last Item When There Are Duplicates

Example 7: Using the XLOOKUP Function to Look Up All the Rows in a Column

Example 8: Using the XLOOKUP Function to Look Up a Record (All Columns in a Row)

Examples 9 and 10: Using the XLOOKUP Function to Do a Two-Way Lookup

Example 11: Using the XLOOKUP Function with Two Lookup Values

Example 12: Using the XLOOKUP Function to Do a Lookup Left

Example 13: Using the XLOOKUP Function to Perform a Vertical or Horizontal Grade Lookup

Example 14: Using the XLOOKUP Function with a Lookup Cell Reference

Example 15: Using the XLOOKUP Function to Look Up a Picture

Example 16: Using the XMATCH Function to Deliver the Relative Position of an Item in a List

Example 17: Using the XMATCH Function to Add Conditional Formatting When Comparing Two Lists

The INDEX Function

Example 18: Looking Up Random Data with the INDEX and RANDBETWEEN Functions

Example 19: Performing a Lookup and Rearranging Columns with the INDEX, SEQUENCE, ROWS, and XMATCH Functions

The FILTER Array Function

Example 20: Using the FILTER Function with One Lookup Value to Return Multiple Records

Example 21: Using the FILTER Function with Two Lookup Values to Return Multiple Records

LOOKUP: The Original Lookup Function for Spreadsheets

Example 22: Using the LOOKUP Function to Do Half Vertical/Half Horizontal Lookups

Example 23: Using LOOKUP to Create Approximate Match Lookup Formulas More Quickly Than with XLOOKUP

Example 24: Determining Whether to Use Multiple Lookup Tables and the SWITCH or FILTER Function

The SWITCH Function

Example 25: Using SWITCH with Lookup Formulas

Example 26: Combining Columns with the CHOOSE Function

Example 27: Using Data Types and the Dot Operator to Perform a Lookup

Key Concepts in Chapter 14

Keyboard Shortcuts Learned in Chapter 14

Practice Problems for Chapter 14

Chapter 15: Worksheet Array Formulas: Just Enter!

What Is a Worksheet Array?

Defining a Worksheet Array Formula

Other Important Array Formula Details

Array Operation Configurations, the Evaluation Process, and the Size of the Resultant Array

Examples 1 Through 3: Doing Financial Calculations Using the Direct Array Operation Column * Row * Table

Example 4: Using Array Formulas to Build More Compact Excel Solutions

Examples 5 and 6: Using the SUMPRODUCT Function to Multiply Same-Size Arrays and Add the Resultant Array

Examples 7 Through 12: Using the SUMPRODUCT Function for Weighted Average Test Scores

Examples 13 and 14: Using the MMULT Array Function for a Total Costs Formula

Example 15: Using the MMULT Array Function to Spill Row Totals for a Table

Example 16: Using MMULT to Find the Standard Deviation of Expected Portfolio Returns

Example 17: Using the SEQUENCE Array Function Inside MMULT to Get Dynamic Spilled Row Totals

Array Functions

Examples 18 Through 23: Using the FILTER Array Function with Different Logical Tests in the include Argument

Examples 24 Through 28: Using the UNIQUE Array Function in Different Ways

Examples 29 Through 33: Using the SORT Array Function in Different Ways

Example 34: Using the SORTBY Array Function

Sorting Mixed Data

Example 35: Finding the Top Three Scores, Including Ties, with the FILTER, LARGE, and SORT Functions

Examples 36 Through 39: Using the SEQUENCE Array Function

Frequency Distributions

Example 40: Using SORT, UNIQUE, and COUNTIFS to Create a Qualitative Frequency Distribution

Example 41: Using the FREQUENCY Array Function to Create a Quantitative Frequency Distribution

Example 42: Removing the Last Category by Using the INDEX, SEQUENCE, and ROWS Functions

Example 43: Using FREQUENCY in Formulas to Create Upper Limits

Example 44: Using Formulas to Create Logical Labels for Counting Categories

Example 45: Using the COUNTIFS Function to Create Custom Counting Categories for a Quantitative Frequency Distribution

Key Concepts in Chapter 15

Practice Problems for Chapter 15

Chapter 16: The LET and LAMBDA Functions

The LET Worksheet Function

Examples 1 Through 3: Using the LET Worksheet Function When There Are Repeating Formula Elements

Examples 4 Through 7: Using the LET Worksheet Function to Create Single-Cell Reports

The LAMBDA Function

Examples 8 and 9: Using the LAMBDA Worksheet Function to Create Reusable Functions for Common Calculations

Examples 10 and 11: Using the LAMBDA Worksheet Function to Simplify Complex Formula Reports

New LAMBDA Helper Functions That Allow You to Use a LAMBDA Function Value in the Worksheet

Example 12: Three Examples of LAMBDA Helper Functions

Key Concepts in Chapter 16

Practice Problems for Chapter 16

Chapter 17: Data Analysis Basics: Standard Pivot Tables, Sorting, Filtering, Visualizations, and More

The Standard PivotTable Tool

Example 1: Using a Standard PivotTable to Create a Product Sales and Costs Report

Example 2: Creating a Frequency Distribution from Customer Survey Data with the Standard PivotTable Tool

Example 3: Using the Sort Tool to Sort the Fastest Race Times to the Top for Each Track

Example 4: Sorting by Color

Example 5: Using the Filter Tool to Extract the Blue Quad Sales Records

Example 6: Filtering to Extract Student Records Based on AND and OR Logical Tests

More About the Fast and Easy Standard PivotTable Tool

Example 7: Changing the Functions in a Standard PivotTable by Using Summarize Values By

Example 8: Using the Standard PivotTable Group Feature to Create a Yearly Sales Report

Example 9: Filtering a Standard PivotTable Report Using a Slicer

Example 10: Creating Seven Standard PivotTable Reports with a Single Click

Example 11: Creating a Cross-Tabulated Report and Visualization Using a Standard PivotTable and PivotChart

Visualizing with Excel Charts

Example 12: Visualizing with a Clustered Column Chart or a Stacked Column Chart

Example 13: Visualizing with a Clustered Bar Chart or a Stacked Bar Chart

Example 14: Creating a Frequency Distribution and Histogram Chart with a PivotTable and a PivotChart

Example 15: Visualizing with a Line Chart to Show Changes and Trends over Time

Examples 16 and 17: Using an X-Y Scatter Chart to Visualize the Relationship Between Two Quantitative Variables

Example 18: Analyzing Data Using Artificial Intelligence to Create a Beautiful X-Y Scatter Chart

Example 19: Visualizing Tables of Data

Example 20: Using PivotTable Styles

Example 21: Using Conditional Formatting to Add a Heat Map or Color Scales to a Table Report

Example 22: Using Conditional Formatting for a PivotTable Report to Show the Top 5 Values

Example 23: Using Data Bars Conditional Formatting to Make an In-Cell Histogram

Examples 24 and 25: Using Worksheet Date Formulas to Create Date Attribute Fields

Cleaning and Transforming Data by Recognizing Patterns in the Data: Worksheet Formulas, Flash Fill, Power Query, or DAX?

Example 26: Using Worksheet Text Formulas to Clean and Transform Data and Create Text Attribute Fields

Example 27: Using Flash Fill to Perform One-Time Cleaning Tasks Where the Pattern Is Consistent

Example 28: Using Dashboards to Gauge Activity as New Data Arrives

Key Concepts in Chapter 17

Keyboard Shortcuts Learned in Chapter 17

Practice Problems for Chapter 17

Chapter 18: Advanced Data Analysis with Power Query, Power Pivot, Power BI, M Code, and DAX

Useful Information: Summary Reports, Visualizations, and Dashboards

Microsoft’s Data Analysis Tools

Important Data Analysis Definitions and Concepts

Data Modeling Basics

The Data Model Tool in Excel Power Pivot and Power BI Desktop

The Process of Turning Data into Information: The 10-Step Data Analysis Process

Data Analysis Examples

Example 1: Using Worksheet Formulas, a Standard PivotTable, and an Excel Chart to Create a Product Sales Report and Visualization

Example 2: Using the Excel App with Power Query, a Power Pivot Data Model PivotTable, and an Excel Chart to Create a Product Sales Report and Visualization

Comparing M Code, DAX Formulas, Worksheet Formulas, and Standard PivotTable Calculations

Example 3: Using the Power BI Desktop App to Create a Product Sales Report and Power BI Online for Sharing and Collaboration

Key Concepts in Chapter 18

Practice Problems for Chapter 18

Chapter 19: Data Analysis Examples: Big Data and Complex Data Analysis

Project 1: Converting 12 CSV Files with 35,000 Rows of Data into a Report with Four Key Metrics

Project 2: Importing SQL Big Data, Building DAX Formulas, and Creating an Interactive Power BI Desktop Report

Project 3: Combining Data from Multiple Excel Files and Creating a Summary Report—All with Power Query!

Project 4: Combining All Excel Table Data in the Current Workbook File into a Standard PivotTable Report

Project 5: Using Power Query M Code to Fix Inconsistent Datasets from Multiple Excel Files So They Can Be Combined

Key Concepts in Chapter 19

Keyboard Shortcuts Learned in Chapter 19

Practice Problems for Chapter 19

Chapter 20: The Macro Recorder and the VBA Editor

Using the Excel Workbook .xlsm File Extension to Allow VBA Code in an Excel File

Showing the Developer Tab in the Excel Ribbon

Using the Macro Recorder and a Form Control Button to Help a User Refresh a Query

Using the Macro Dialog Box and the VBA Editor Window to View VBA Code

Using the Macro Recorder and the Use Relative References Button to Write VBA Code That You Can Use for Repetitive Tasks

Using the Personal Macro Workbook to Create Universal VBA Macros

Copying VBA Code from the Internet and Pasting It into a VBA Module

Chapter 20 Key Concepts

Keyboard Shortcuts Learned in Chapter 20

Practice Problems for Chapter 20

Chapter 21: Financial Worksheet Functions

Financial Terms and Variables Used in Financial Cash Flow Analysis

The FV Function

PV Function

PMT Function

NPER Function

RATE Function

NPV Function

XNPV Function

Chapter 21 Key Concepts

Practice Problems for Chapter 21

Chapter 22: Simple Linear Regression Worksheet Functions

Simple Linear Regression Variables and Definitions

Excel Worksheet Functions for Simple Linear Regression

Chapter 22 Key Concepts

Practice Problems for Chapter 22

The End

Index

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.

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 comprehensive 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!

Note: The title of this book was inspired by the 1977 promotional posters hung on telephone poles as part of the world tour of the punk rock band The Clash. The posters read Come and see The Only Band That Matters! along with the show date, time, and location. I saw The Clash that year at the Berkeley Community Center in Berkeley, California. You are going to have a rocking good time with this cutting-edge book, learning how to harness the power of the only app that matters to create effective and efficient solutions!

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.

Following Along with Excel Files

Before you read any further, you should download the Excel and data analysis files used throughout this book. That way, you can follow along as you read. Go to https://excelisfun.net/files/TheOnlyAppMatterBook.zip to download the zipped folder, which also contains some practice problems with solutions for selected chapters so that you can practice what you learn in the book and become an Excel master.

After you download the file and unzip it, you should see the structure shown in Figure I.1. Throughout the book, you will open and save files to this system of folders.

A screenshot of a cell phone Description automatically generated

Figure I.1 This system of folders contains all the files for the book.

I’ve color-coded the Excel files so you can easily tell what’s what:

Blue: The blue worksheets are for you to work on as you follow along with the examples in the book and the practice problems.

Red: The red worksheets contain finished examples that you can use to check your work when you are finished with the blue worksheets. Figure I.2 shows an example. Notice that the name of each red sheet contains the text an, which stands for answer.

Yellow: The yellow worksheets (not pictured) are informational only.

A close up of a sign Description automatically generated

Figure I.2 Blue worksheets allow you to follow along and practice, and red ones provide solutions for you to check your work.

For Chapters 18, 19, and 20, the Excel files contain so many elements that I’ve provided extra files. A file with the word Start in it is the one you should use to follow along and practice, and the file with the word Finished in it is the one that provides the solutions.

I’ve also used color-coding within worksheets (see in Figure I.3):

Column headers have a dark blue fill.

Cells containing raw data, like text and numbers, have no fill color.

Cells containing formulas have a green fill.

Formula input labels have a red fill.

Graphical user interface, application, table, Excel Description automatically generated

Figure I.3 Color formatting for column headers, raw data, formulas, and formula input labels.

Starting in Chapter 8, you will find extra worksheets at the end of each chapter’s Excel workbook file. You can use these worksheets as homework, to practice what you’ve learned in the chapter. The blue practice worksheets are for you to work with, and the red answer sheets contain solutions for you to check your work. The blue and red practice sheets are always listed after a blank sheet named Practice Problems >>, as shown in Figure I.4.

A picture containing chart Description automatically generated

Figure I.4 Some chapters include extra practice problems as well as solutions.

Chapter 1: Why Excel 365?

This chapter compares Excel 365 to older versions and showcases the major improvements that Excel 365 offers over past versions. In doing so, it provides an overview of the benefits of Excel 365. The details of the features and techniques introduced in this chapter are covered later in the book. If you are just learning Excel, you might want to skip this chapter for now or read it with the understanding that it describes many tools and features without first teaching you the fundamentals of Excel. (You will learn those fundamentals in subsequent chapters.)

Note: To follow along with the examples in this chapter, you can use the file named Ch01-Excel365-WhyExcel365.xlsx. If you haven’t yet downloaded the files that accompany this book, see the Introduction for details on how to do so.

Excel 365 is a huge improvement over earlier versions for many reasons, including these:

The worksheet formula calculation engine makes creating formulas easier than ever before.

Array formulas no longer require the special keystroke Ctrl+Shift+Enter.

New array functions like FILTER and UNIQUE make complicated formula tasks simple.

New functions like XLOOKUP and LET are available.

Power Query allows you to import and transform data with different structures, from different sources, and from small and large datasets alike. The M code formula language is built specifically as a data transformational formula language.

You can use the Power Pivot or Power BI Data Model and DAX formulas to store and make efficient calculations on big data.

You can use Power BI Desktop to create interactive and shareable reports, visualizations, and dashboards.

The following sections discuss these improvements in detail.

The Worksheet Formula Calculation Engine

In Excel 365, the worksheet formula calculation engine that works behind the scenes to calculate the answers for worksheet formulas has fundamentally changed. The engine now enables you to more quickly and easily create formulas. In all versions of Excel before Excel 365, when you create a formula, you enter it into a cell and then copy the formula to other cells. Figure 1.1 shows an example of that type of old-school formula. The formula in this case calculates the commission paid to each employee. This formula uses a relative cell reference and an absolute cell reference inside the ROUND function. Figure 1.2 shows the result you get after copying the formula down the column. With this older method, which involves using single cell references (in this case, C3 and $F$3), different types of cell references (relative and absolute) have to be considered, and the formula must be manually copied down the column.

Graphical user interface, application, table Description automatically generated

Figure 1.1 Old-school Excel worksheet formulas require relative and absolute cell references.

Table Description automatically generated

Figure 1.2 Old-school Excel worksheet formulas require a manual copy action.

Figure 1.3 shows a new-school Excel 365 formula that uses the whole column of sales values (C3:C7) and the cell reference F3 inside the ROUND function. When the formula is entered, as shown in Figure 1.4, the formula automatically spills down the column and delivers all the correct commission amounts. Because the range C3:C7 is used in the formula, the formula is forced to deliver an array of answers, one for each cell in the range C3:C7. This type of formula is called a spilled array formula because the answers automatically spill down the column. Using this new method takes significantly less effort than using the old-school approach.

Graphical user interface, application, table, Excel Description automatically generated

Figure 1.3 Excel 365 worksheet formulas often do not require that you use relative and absolute cell references.

Graphical user interface, table Description automatically generated

Figure 1.4 Excel 365 worksheet formulas can automatically spill down a column, without any manual copying.

With Excel 365, you do not have to decide about using relative or absolute cell references, and you do not have to copy a formula down a column. The calculation task requires significantly fewer steps and less effort. However, because people have been making Excel formula solutions in the old-school way for 40 years, Excel users have to make the conceptual jump and remember to select all the cells that are needed in a formula rather than select one cell at a time. Throughout this book you will see many spectacular examples of the efficiency of spilled array formulas.

Array Formulas

In versions of Excel prior to Excel 365, when you want to create an array formula, you have to enter the formula with a special keystroke to signal to Excel that the formula is an array formula. With Microsoft 365 Excel, you no longer have to do this. As shown in Figure 1.5, in older versions of Excel, if you want to use the TRANSPOSE array function, you have to highlight the correct number of cells, create the formula, and then use the keystrokes Ctrl+Shift+Enter to get the formula to calculate correctly. With Excel 365, all you have to do is create the formula in the top cell, and the results automatically spill to the cells below; you don’t need to use any special keystrokes.

Graphical user interface, application, table, Excel Description automatically generated

Figure 1.5 Array formulas can be entered without Ctrl+Shift+Enter.

New Array Functions: UNIQUE and FILTER

Excel 365 has numerous new built-in array functions that deliver arrays of results (that is, multiple answers) and replace complicated formulas that are otherwise used to solve certain tasks. New Excel 365 array functions, like FILTER and UNIQUE, will amaze long-time Excel users with their elegant simplicity. For newer Excel users, these functions make it easy to quickly solve tasks that once required advanced knowledge. The new array functions make it possible, for example, to create a unique list (the UNIQUE function) and look up an item to retrieve multiple records (the FILTER function).

The UNIQUE Function

Figure 1.6 shows a complex formula that is required in older versions of Excel to create a unique list of values. Figure 1.7 shows how to use the UNIQUE function to extract a unique list of values from a column in a much simpler way. In my second book, Ctrl+Shift+Enter: Mastering Excel Array Formulas, published in 2012, I had to spend chapters building up and describing the logic of a formula like the one in Figure 1.6. In Excel 365, such formulas are not necessary!

A screenshot of a cell phone Description automatically generated

Figure 1.6 In earlier versions of Excel, formulas for extracting unique lists of values are very difficult.

A screenshot of a cell phone Description automatically generated

Figure 1.7 The Excel 365 UNIQUE function easily extracts a unique list of values.

The FILTER Function

The FILTER function is another new Excel 365 function that greatly simplifies formula calculations, models, and analysis. This new Excel 365 function allows you to easily look up an item and returns multiple records, spilled down the column. Figure 1.8 shows the formula required to look up all the records for the Quad product in versions of Excel prior to Excel 365. This type of complex formula to look up a single item and return multiple records has been very common throughout Excel history. But as you can see in Figure 1.9, it will be banished to the history books by the FILTER function.

A screenshot of a cell phone Description automatically generated

Figure 1.8 Looking up the records for Quad in versions of Excel prior to Excel 365 requires a complex formula.

A screenshot of a cell phone Description automatically generated

Figure 1.9 Looking up the records for Quad with the Excel 365 FILTER function is a breeze.

Throughout this book, you will see many more examples of how the new array functions in Excel 365 can dramatically reduce formula complexity and increase your speed at building formula solutions.

The New XLOOKUP and LET Functions

There are many new functions in Excel 365, but two in particular stand out:

XLOOKUP is a new lookup function that addresses many of the problems with the older lookup function VLOOKUP.

LET is a new function that allows you to define variables that help formulas calculate more quickly, make complex formulas easier to understand, and condense a multistep formula into a single cell.

The XLOOKUP Function

The VLOOKUP function is one of the most widely used functions in Excel history. Figure 1.10 shows a classic VLOOKUP formula that looks up a product name and returns the price for each product. In this formula, VLOOKUP uses the lookup table in H3:I5 to match the product name in the first column of the lookup table (H3:H5) and return the price from the second column of the lookup table (I3:I5). VLOOKUP knows to return the price because there is a 2 in the function’s third argument, which tells VLOOKUP to get the value from the second column in the lookup table.

A screenshot of a cell phone Description automatically generated

Figure 1.10 A classic VLOOKUP formula can be used to look up the price of a product.

One of the problems with this classic VLOOKUP formula is that if you insert a new column into the lookup table—such as the new column for cost in Figure 1.11—the formula no longer returns the correct price. Because this VLOOKUP formula uses the number 2 to indicate that the second column in the lookup table contains the price, now the formula mistakenly returns cost rather than price. In the first row for the Quad product, rather than returning the correct price, $43, the formula returns the incorrect price $22.

A screenshot of a cell phone Description automatically generated

Figure 1.11 If you add a new Cost column, the VLOOKUP formula does not work correctly to retrieve the price for the product.

There is a workaround to this VLOOKUP problem in older versions of Excel, but it is not as easy as the solution provided by the new Excel 365 function XLOOKUP. As shown in Figure 1.12, XLOOKUP solves the problem of having to specify the column number of the items you want to return by using separate ranges for the products to match (with the second argument, lookup_array) and the values to retrieve (with the third argument, return_array). With two separate ranges in XLOOKUP, it doesn’t matter if you add new columns to the lookup table; the formula will always work.

A screenshot of a cell phone Description automatically generated

Figure 1.12 When new columns are added to the lookup table, XLOOKUP instantly updates to show the correct answer.

Another notorious problem plagues the VLOOKUP function: You have to give it a lookup table, and you can only match items from the first column of the lookup table. This means that if you ever have to look up an item to the left of the first column, VLOOKUP cannot do it without a crazy formula workaround. This problem, commonly referred to as the VLOOKUP lookup left curse, can easily be solved by using the XLOOKUP function. Because XLOOKUP uses a separate range for matched values (lookup_array) and values to retrieve (return_array), as shown in Figure 1.13, XLOOKUP can find a match in the Product column (on the right) and retrieve a value from the Product ID column (on the left).

A screenshot of a cell phone Description automatically generated

Figure 1.13 Lookup left is simple for the new XLOOKUP function.

There are other lookup situations that VLOOKUP cannot perform but that XLOOKUP can. For example, XLOOKUP can look up cell references, sort a lookup table in descending order (which is helpful when performing lookups for taxes or grades), and look up the last item when there are duplicates. You will see examples of the use of XLOOKUP in Chapter 14.

The LET Function

A second noteworthy new Excel 365 function is the LET function, which allows you to define a variable inside a formula. LET exists only in Excel 365 and Excel 2021, and there is no comparable worksheet option in any other version of Excel. Although it is not common to use LET when creating basic Excel solutions, when advanced solutions with complex formulas are required, the LET function comes to the rescue by allowing variables to be defined in a formula. There are several benefits to defining variables:

Formulas with repeating elements calculate more quickly.

Complex formulas are easier to read.

Formulas that span multiple columns can be condensed into a single cell.

Figure 1.14 shows an example of a LET formula that defines three variables and then delivers a sorted frequency distribution. This example shows how the LET function replaces a very complex set of formulas that span multiple columns and condenses them into a single cell. This frequency distribution report has three benefits: It requires you to manage only one cell, it replaces complex formulas, and it instantly updates and sorts correctly when the source data changes.

A screenshot of a cell phone Description automatically generated

Figure 1.14 The LET function allows you to define variables inside a formula.

Power Query and the M Code Formula Language

Many people argue that the PivotTable is the single greatest tool in Excel. And, indeed, it is the number-one tool in the world for creating summary reports and dashboards based on data. But the main problem with PivotTables has always been that the data required to use a PivotTable must be in the form of a proper dataset table. Much of the data that is used in Excel is not in this format, and it can be difficult to simultaneously import data, transform it into the correct format, and then load it to a PivotTable or an Excel worksheet. For decades, importing and converting all the sss data sources into a single table has been mostly a manual process. Sometimes VBA code can be used to automate the process, but that is almost always difficult as well. Power Query changes the game.

Power Query is the greatest Excel invention since the PivotTable. With this single tool, you can use Excel to connect to almost any data source; connect simultaneously to multiple data sources; clean and transform data into the required structure; and load data to a worksheet, directly to a PivotTable, or, if required, to the Data Model. Then, if one or more of the data sources changes, you can simply click the refresh button, and everything updates, including any summary reports or dashboards. For people who have had to deal with data and reporting, there is no other tool quite like Power Query.

Note: Technically, Power Query is a feature in Excel 2016 and 2019. In Excel 365, it has been improved and streamlined to make importing, cleaning, and transforming data for analysis much easier than in earlier versions.

To briefly illustrate the power of Power Query, let’s look at an example involving a single Excel file. Figure 1.15 shows an Excel workbook file with daily sales on sheets named T(1), T(2), and T(3). If the goal is to make a single summary PivotTable report based on all the tables, in Excel versions that do not have Power Query, you would have to combine the tables manually or by using VBA code and then create the report.

A close up of text on a white background Description automatically generated

Figure 1.15 Without Power Query, combining these three tables, which exist on different sheets, would be a manual process.

By using the Power Query function Excel.CurrentWorkbook(), shown in Figure 1.16, you can combine all the Excel Tables in the current Excel workbook file into a single table that you can use to create a summary PivotTable report (see Figure 1.17).

Note: A Power Query function is also known as an M code function.

Figure 1.16 The M code function shown here can grab all the Excel Tables from the current Excel workbook.

A screenshot of a cell phone Description automatically generated

Figure 1.17 With Power Query, it is easy to create summary reports from data that exists in multiple locations.

The Excel.CurrentWorkbook() function is very efficient. If new Excel Tables are created in the Excel file where this function is used, a simple refresh adds the new data to the summary PivotTable. Figures 1.18 shows a new Excel Table that is added to the Excel file, and Figure 1.19 shows the updated report.

A screenshot of a cell phone Description automatically generated

Figure 1.18 A new Excel Table with data is added to the Excel file.

A screenshot of a cell phone Description automatically generated

Figure 1.19 Clicking the Refresh button causes the Excel.CurrentWorkbook() function to include the new data in the report.

This example illustrates the power of Power Query to import from multiple data sources, transform the data into the correct form, and load it into a finished PivotTable report. However, you are not limited to just data coming from an Excel worksheet. You can also use Power Query to get data from a database and bring it into Excel. If you have text files or Excel files or Access files, or if you have data in all three types of files and you need it combined into one table with a summary report or dashboard, you can use Power Query to easily do that. Power Query can also connect to websites, online data sources, Microsoft Azure, Power BI, and many more sources.

As an example of Power Query’s vast ability to do feats that seem impossible, Figure 1.20 shows a scenario that you will encounter later in this book that involves getting data from seven different Excel CSV files, a database, and an Excel Table. Figure 1.21 shows the four final tables that Power Query helps you create, and Figure 1.22 shows the final dashboard Power Query creates to display the data. If any of the source data changes, or if a new table of data is required, you simply click the Refresh button in the final dashboard, and everything updates. Isn’t this breathtakingly awesome?

A screenshot of a cell phone Description automatically generated

Figure 1.20 A lot of data is needed for analysis in this case: seven different Excel CSV files, a database, and an Excel Table.

A screenshot of a cell phone Description automatically generated

Figure 1.21 Power Query transforms the data from various sources into the four tables needed for the dashboard.

A screenshot of a cell phone Description automatically generated

Figure 1.22 Without Power Query to gather and transform the data, creating dashboards like this is very difficult.

Excel 365 Power Query gives users exceptional capabilities in terms of importing, transforming, and loading data, and it makes data analysis simple. Throughout this book, you will see many more examples of how Power Query can connect to almost any data source and then clean, transform, and load the data and enable you to update your analysis with a simple click of the Refresh button. You will also see that Power Query can be used to create some efficient and useful worksheet solutions that do not involve importing data from an external source. In addition, you will learn about the Power BI app, which provides functionality very similar to that of Power Query.

Power Pivot, the Data Model, and DAX Formulas

Another amazing improvement in Excel 365 is Power Pivot. Although Power Pivot was available in a few older versions of Excel, Microsoft charged extra money for it. Excel 365 has Power Pivot included by default, for no additional fee.

Power Pivot is a game changer because it provides two capabilities that were not available in earlier versions:

Data Model: The Data Model allows you to import big data and use it for PivotTable reports.

DAX: The DAX formula language allows you to make reusable formulas for PivotTables that calculate quickly on big data. DAX provides a wider variety of business-related calculations than do the older Excel standard PivotTables.

The Data Model

Figure 1.23 provides an example of how the Data Model can store big data in Excel. On the right side of the figure, the Queries & Connections pane shows the fTransactions table, which has more than 7 million rows of data. This data is compressed to a very small size and stored in the Data Model, and it can be used to create Data Model PivotTable reports like the Product report shown on the left side of Figure 1.23.

A screenshot of a cell phone Description automatically generated

Figure 1.23 With the Data Model, you can quickly and easily create a report based on 7 million rows of data.

Creating reports based on big data was not possible before the Power Pivot Data Model was added to Excel. In this data-driven age, effortlessly dealing with big data is a key requirement for many projects and makes the Data Model a beneficial new feature in Excel 365.

The DAX Language

The second key feature in Power Pivot is the new formula language called DAX (short for Data Analysis eXpressions). DAX formulas can work on big data quickly, and they offer a wide array of functions and formula possibilities. For example, the Data Model in Figure 1.23 uses two DAX formulas: one for net sales ($) and one for cost of goods sold ($). These DAX formulas can work across 7 million rows of data to calculate the formula result for each cell in the report in just a fraction of a second. If the same calculations were created using Excel worksheet formulas, the worksheet would calculate so slowly that it would completely freeze up the computer.

In addition to being able to work quickly with big data, DAX formulas can handle many business-related calculations more easily than can worksheet formulas and the older standard PivotTables. For example, if you want to calculate the average daily sales for each product in each month, using DAX formulas would be much easier than using worksheet formulas or a standard PivotTable. Figure 1.24 shows how to make this calculation by using worksheet formulas with an intermediate formula table (columns F, G, H) and the final report (columns J, K, L). It requires a total of six different formulas. In addition, this report does not show any subtotals or a grand total, and if you included subtotals and a grand total, you would have to create three additional formulas.

A screenshot of a cell phone Description automatically generated

Figure 1.24 Formulas to calculate the average daily sales for each product in each month are complicated.

Another limitation to a formula report like this is that the formulas are bound to the conditions—in this case, month and product—and cannot be changed without significantly editing the formulas. Figure 1.25 shows a second attempt at creating a report of average daily sales for each product in each month; this attempt uses a combination of worksheet formulas and a standard PivotTable. In this solution, the subtotals would not calculate the correct amount! So you can see that worksheet formulas require a lot of effort and are bound to the conditions initially placed in the formulas, and a standard PivotTable does not always give the correct results. DAX formulas used in a Data Model PivotTable solve all these problems easily.

A screenshot of a cell phone Description automatically generated

Figure 1.25 A standard PivotTable requires intermediate formula steps, and subtotals do not calculate correctly.

Figure 1.26 shows a Data Model PivotTable with a DAX formula that correctly calculates the average daily sales in three different ways: as product by month (in the Rows area), by month (as a subtotal), and as daily averages in the Grand Total cell.

A screenshot of a cell phone Description automatically generated

Figure 1.26 A Data Model PivotTable with a DAX formula calculates the correct average daily sales in every cell.

Figure 1.27 shows the DAX formula used in the Data Model PivotTable in Figure 1.26. This DAX formula is significantly less complicated to create and is much more versatile than the previous two solutions.

Figure 1.27 This DAX formula (called a DAX measure) is simple compared to worksheet formulas.

To get the DAX formula to work correctly, however, you need to take the extra step of creating a data model with related tables, like the one shown in Figure 1.28. This extra step is well worth the effort when a big data report is required, though.

A screenshot of a cell phone Description automatically generated

Figure 1.28 This is what the data model looks like when you make an average daily sales calculation.

DAX formulas and Data Model PivotTables can be used to perform calculations that cannot be easily done with worksheet formulas and standard PivotTables. In addition, the Data Model and DAX formulas provide great versatility. For example, Figure 1.29 shows an example of the same DAX formula as before for average daily sales, but now the new column Sales Rep is added to the Rows area of the Data Model PivotTable, and all the calculations are correct! Worksheet formulas cannot accommodate the addition of new conditions to a report, but a Data Model PivotTable and DAX formula can easily accomplish such changes.

A screenshot of a cell phone Description automatically generated

Figure 1.29 DAX can do tasks that are nearly impossible with standard PivotTables and worksheet formulas.

The Excel 365 Data Model can hold more than 100 million rows of data, and there are

Reviews for Microsoft 365 Excel

[alicefp · Rating: 5 out of 5 stars]

An excelent book! Also with a great training material available freely on excelisfun site ! Thanks a lot A solid knowledge base with all new formulas an many tips and tricks for saving time!