338 Chapters

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

Microsoft 365 Excel: The Only App That Matters: Master Excel 365: Unlock the Power of the Ultimate Spreadsheet Tool

What do you get with eBook?

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with eBook?

Product Details

Frequently bought together

Table of Contents

Recommendations for you

About the authors

FAQs

Microsoft 365 Excel: The Only App That Matters: Master Excel 365: Unlock the Power of the Ultimate Spreadsheet Tool

What do you get with eBook?

Contact Details

Billing Address

Key benefits

Description

Who is this book for?

What you will learn

Product Details

What do you get with eBook?

Contact Details

Billing Address

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

About the authors

FAQs