book

Microsoft 365 Excel: The Only App That Matters

Name: Microsoft 365 Excel: The Only App That Matters
ISBN: 9781836645535

by MrExcel's Holy Macro! Books, Mike Girvin

September 2024

Intermediate to advanced

826 pages

30h 35m

English

Packt Publishing

Read now

Unlock full access

Microsoft 365 Excel: The Only App That Matters
DedicationsAbout the AuthorAcknowledgements
Introduction
Who This Book Is ForPC Versus Mac ExcelFollowing Along with Excel Files
Chapter 1: Why Excel 365?
The Worksheet Formula Calculation EngineArray FormulasNew Array Functions: UNIQUE and FILTERThe New XLOOKUP and LET FunctionsPower Query and the M Code Formula LanguagePower Pivot, the Data Model, and DAX FormulasPower BI Desktop for Creating Visualizations and ReportsKey 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 WorkbooksWorksheet NavigationExcel Data and Default Alignment in CellsThe Excel Command EnvironmentThe Quick Access ToolbarThe File Menu and Backstage ViewFile ManagementKey Concepts in Chapter 5Keyboard Shortcuts Learned in Chapter 5
Chapter 6: Keyboard Keys and Shortcuts
Keyboard ShortcutsKey Concepts in Chapter 6Keyboard Shortcuts Learned in Chapter 6
Chapter 7: Worksheet Efficiency Tricks
Using Worksheet Mouse CursorsEntering Content into Cells with Five Different Keyboard ShortcutsSelecting a Range of CellsJumping Around in a WorksheetKey Concepts in Chapter 7
Chapter 8: Worksheet Formulas, Formatting, and Setup to Solve Problems
Creating a Sample WorksheetUsing the Proper Dataset FormatAdding Style FormattingEntering the DataUsing Worksheet Formulas, Functions, Cell References, and Aggregate OperationsChecking Your Work and Verifying Your FormulasUsing Relative Cell ReferencesAdding Number FormattingUsing the Format PainterInserting Rows into a WorksheetCalculating PercentagesUnderstanding Absolute Cell ReferencesDocumenting Your WorkSpilling Dynamic Array Formulas and Array OperationsUsing the Excel Table FeatureSorting and FilteringSetting Up a Page for PrintingAdding New Data to a Dynamic Solution by Using a Text FileConverting a Solution to a PDF FileKey Concepts in Chapter 8Keyboard Shortcuts Learned in Chapter 8Practice Problems for Chapter 8

Chapter 9: A Golden Rule for Building Excel Models
How Violating the Golden Rule Can Get You into TroubleBenefits of Following the Golden RuleExample 1: Commission Pay Worksheet FormulaExample 2: Monthly Expense Formula with Hard-Coded Formula InputExample 3: COUNTIFS Worksheet Function to Count with a ConditionExample 4: UNIQUE Array Function and Data Validation ListExample 5: Conditional FormattingWhat-If Analysis and Excel ModelsKey Concepts in Chapter 9Keyboard Shortcuts Learned in Chapter 9Practice Problems for Chapter 9
Chapter 10: Knowing When to Use the ROUND Function
The ROUND Function RuleExample 1: Using the ROUND Function to Get Accurate Totals for Tax Deduction CalculationsExample 2: Rounding to the DollarExample 3: Rounding for a Sales InvoiceExample 4: Rounding a PercentageKey Concepts in Chapter 10Practice Problems for Chapter 10
Chapter 11: Date and Time Number Formatting and Formulas
Date Number Formatting, Date Serial Numbers, and Date FormulasThe Format Cells Dialog Box and Custom Date Number FormattingCalculating the Difference Between Two DatesCounting Workdays with NETWORKDAYS.INTLAdding Days to Dates and Subtracting Days from DatesTime Number Formatting, Time Serial Numbers, and Time FormulasExample 1: Calculating Hours WorkedExample 2: Calculating Hours Worked When There Is a Lunch BreakExample 3: Using the Time Number Format to Show Hours Greater Than 24 HoursExample 4: Calculating Hours Worked for a Night ShiftExample 5: Rounding a Time Value to Within 5 Minutes with the MROUND FunctionKeyboard Shortcuts and Custom Date and Time Number FormattingKey Concepts in Chapter 11Keyboard Shortcuts Learned in Chapter 11Practice Problems for Chapter 11
Chapter 12: Worksheet Formula Types and Formula Elements
Worksheet Formula TypesWorksheet Formula ElementsOrder of Operator Precedence in Worksheet FormulasMy Golden Rule, the ROUND Function, and Number Formatting As a FaçadeExample 1: Number Formula for Average Customer RatingsExample 2: Using a Text Formula to Join First and Last Names in a CellExample 3: Using a Logical Formula to Determine Whether Accounts Are in BalanceExamples 4 Through 6: Using a Cost of Goods Sold Formula with a Spilled Array, Relative Cell References, or Table Formula NomenclatureExample 7: Dynamically Extracting the Top N Values by Using the Comma Reference OperatorExample 8: Adding the Top Three Boomerang MTA Scores and Ranking with the RANK.EQ Worksheet FunctionExample 9: Looking Up the Price for Invoicing by Using the XLOOKUP Worksheet FunctionExample 10: Looking Up All the Rows in a Column by Using the XLOOKUP FunctionExample 11: Looking Up a List of Student Classes by Using the FILTER FunctionExample 12: Using the SUMIFS Worksheet Function to Create a Sales and Costs Report by ProductExample 13: Conducting a Customer Credit Analysis with the AND Function and Mixed Cell ReferencesExample 14: Knowing When Mixed Cell References Are RequiredExample 15: Conditionally Formatting a Row with a Logical Formula and Mixed Cell ReferencesExample 16: Extracting Records from an Excel Table with the FILTER Function and Defined NamesExample 17: Conditional Formatting for Dynamic Spilled Array FormulasExample 18: Building a Stock Value Table with Data Types and the Dot Operator for a Stock Data TypeExample 19: Building Formulas Between Worksheets by Using Worksheet ReferencesExamples 20 and 21: Building Formulas Between Workbooks by Using Workbook ReferencesExamples 22 and 23: The Space Operator for Intersecting Values and the Better Alternative, XLOOKUPExample 24: Using the Colon Reference Operator and 3D Cell References to Add Across Multiple WorksheetsExample 25: Using Table Formula Nomenclature Absolute and Mixed ReferencesKey Concepts in Chapter 12Keyboard Shortcuts Learned in Chapter 12Practice Problems for Chapter 12
Chapter 13: Logical Tests and Related Functions (AND, ISNUMBER, IF, IFS, FILTER, SUMIFS, and More)
Comparison OperatorsLogical TestsExample 1: Creating a Logical Test with a Single ConditionExample 2: Using Math Operations to Convert TRUE to 1 and FALSE to 0Example 3: Using IS Worksheet FunctionsTypes of Logical Tests: Single Condition, Contains, NOT, AND, BETWEEN, and ORLogical Functions That Deliver a Single Scalar Boolean Value: AND, OR, and NOTExample 4: Using an AND Function to Run an AND Logical TestExample 5: Using an OR Function to Run an OR Logical TestExample 6: Creating an AND Boolean Math Formula to Run an AND Logical TestExample 7: Creating an OR Boolean Math Formula to Run an OR logical TestExample 8: Nesting the OR Function Inside the AND Function to Run a Complex Logical TestExample 9: Using a None Are True Logical Test or a NOT NOT Logical TestExample 10: Using a BETWEEN Logical TestExample 11: Conditionally Formatting a Row with an AND Logical Test and Mixed Cell ReferencesExample 12: Creating a Test Where Any Nonzero Number Is Considered a TRUE ValueExample 13: Using the SEARCH and ISNUMBER Functions in a Contains Logical TestExample 14: Using Formula Wildcards to Create a Contains Logical Test Inside the COUNTIFS Worksheet FunctionLogical Worksheet Functions That Deliver Values: IF, IFS, IFNA, IFERROR, and FILTERExample 15: Using the IF Function to Deliver One of Two Number Values: 750 or 0Example 16: Using the IF Function to Deliver One of Two Text Values: Over or UnderExamples 17 and 18: Using the IF Function to Deliver a Formula or Show NothingExamples 19 and 20: Using the AND and OR Functions Inside the IF FunctionExample 21: Creating an AND Logical Test That Can Spill ResultsExample 22: Using the IFS Function When You Have Three or More Things to Put in a CellExamples 23 and 24: Using the IFNA Function to Replace the #N/A Error with Something DifferentExamples 25: Using the IF Function Instead of IFERRORExamples 26: Using the IF Function Inside an Aggregate Function to Make a Conditional CalculationExamples 27: Putting a Boolean AND Logical Test Inside the FILTER Function to Filter a DatasetExamples 28: Putting a Boolean OR Logical Test Inside the FILTER Function to Filter a DatasetExample 29: Comparing Two Lists and Extracting Results Using XMATCH and Other FunctionsUsing IFS Aggregate Functions (COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS) to Calculate Based on an AND Logical TestExample 30: Using IFS Aggregate Functions to Make Calculations with Three CriteriaExample 31: Taking a Closer Look at Using the SUMIFS Function to Run an AND Logical Test with Three CriteriaExample 32: Using the SUMIFS Function with Three Criteria in a Cross-Tabulated ReportExample 33: Using a BETWEEN Logical Test with SUMIFS and MAXIFS in a Monthly Sales ReportExample 34: Using the SUMIFS Function to Do an OR Logical Test on a Single ColumnExample 35: Carrying Out Aggregate Operations with an OR Logical Test on a Single ColumnExample 36: Conducting Aggregate Operations with an AND and OR Logical TestExample 37: Conducting Aggregate Operations with an OR Logical Test on Two ColumnsExample 38: Using Database Worksheet FunctionsKey Concepts in Chapter 13Practice Problems for Chapter 13
Chapter 14: Worksheet Lookup Functions and Formulas
Exact Match Versus Exact Match or Next Smaller Item LookupsThe XLOOKUP FunctionExample 1: Using the XLOOKUP Function to Look Up Product Price Based on an Exact Match Lookup and Data Validation ListExample 2: Using the XLOOKUP Function to Look Up Tax Rate Based on an Exact Match or Next Smaller Item LookupExample 3: Using the XLOOKUP Function for an Exact Match or to Find the Next Biggest ValueExample 4: Using the XLOOKUP Function to Do a Wildcard LookupExample 5: Using the XLOOKUP Function to Get the First Item When There Are DuplicatesExample 6: Using the XLOOKUP Function to Get the Last Item When There Are DuplicatesExample 7: Using the XLOOKUP Function to Look Up All the Rows in a ColumnExample 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 LookupExample 11: Using the XLOOKUP Function with Two Lookup ValuesExample 12: Using the XLOOKUP Function to Do a Lookup LeftExample 13: Using the XLOOKUP Function to Perform a Vertical or Horizontal Grade LookupExample 14: Using the XLOOKUP Function with a Lookup Cell ReferenceExample 15: Using the XLOOKUP Function to Look Up a PictureExample 16: Using the XMATCH Function to Deliver the Relative Position of an Item in a ListExample 17: Using the XMATCH Function to Add Conditional Formatting When Comparing Two ListsThe INDEX FunctionExample 18: Looking Up Random Data with the INDEX and RANDBETWEEN FunctionsExample 19: Performing a Lookup and Rearranging Columns with the INDEX, SEQUENCE, ROWS, and XMATCH FunctionsThe FILTER Array FunctionExample 20: Using the FILTER Function with One Lookup Value to Return Multiple RecordsExample 21: Using the FILTER Function with Two Lookup Values to Return Multiple RecordsLOOKUP: The Original Lookup Function for SpreadsheetsExample 22: Using the LOOKUP Function to Do Half Vertical/Half Horizontal LookupsExample 23: Using LOOKUP to Create Approximate Match Lookup Formulas More Quickly Than with XLOOKUPExample 24: Determining Whether to Use Multiple Lookup Tables and the SWITCH or FILTER FunctionThe SWITCH FunctionExample 25: Using SWITCH with Lookup FormulasExample 26: Combining Columns with the CHOOSE FunctionExample 27: Using Data Types and the Dot Operator to Perform a LookupKey Concepts in Chapter 14Keyboard Shortcuts Learned in Chapter 14Practice Problems for Chapter 14
Chapter 15: Worksheet Array Formulas: Just Enter!
What Is a Worksheet Array?Defining a Worksheet Array FormulaOther Important Array Formula DetailsArray Operation Configurations, the Evaluation Process, and the Size of the Resultant ArrayExamples 1 Through 3: Doing Financial Calculations Using the Direct Array Operation Column * Row * TableExample 4: Using Array Formulas to Build More Compact Excel SolutionsExamples 5 and 6: Using the SUMPRODUCT Function to Multiply Same-Size Arrays and Add the Resultant ArrayExamples 7 Through 12: Using the SUMPRODUCT Function for Weighted Average Test ScoresExamples 13 and 14: Using the MMULT Array Function for a Total Costs FormulaExample 15: Using the MMULT Array Function to Spill Row Totals for a TableExample 16: Using MMULT to Find the Standard Deviation of Expected Portfolio ReturnsExample 17: Using the SEQUENCE Array Function Inside MMULT to Get Dynamic Spilled Row TotalsArray FunctionsExamples 18 Through 23: Using the FILTER Array Function with Different Logical Tests in the include ArgumentExamples 24 Through 28: Using the UNIQUE Array Function in Different WaysExamples 29 Through 33: Using the SORT Array Function in Different WaysExample 34: Using the SORTBY Array FunctionSorting Mixed DataExample 35: Finding the Top Three Scores, Including Ties, with the FILTER, LARGE, and SORT FunctionsExamples 36 Through 39: Using the SEQUENCE Array FunctionFrequency DistributionsExample 40: Using SORT, UNIQUE, and COUNTIFS to Create a Qualitative Frequency DistributionExample 41: Using the FREQUENCY Array Function to Create a Quantitative Frequency DistributionExample 42: Removing the Last Category by Using the INDEX, SEQUENCE, and ROWS FunctionsExample 43: Using FREQUENCY in Formulas to Create Upper LimitsExample 44: Using Formulas to Create Logical Labels for Counting CategoriesExample 45: Using the COUNTIFS Function to Create Custom Counting Categories for a Quantitative Frequency DistributionKey Concepts in Chapter 15Practice Problems for Chapter 15
Chapter 16: The LET and LAMBDA Functions
The LET Worksheet FunctionExamples 1 Through 3: Using the LET Worksheet Function When There Are Repeating Formula ElementsExamples 4 Through 7: Using the LET Worksheet Function to Create Single-Cell ReportsThe LAMBDA FunctionExamples 8 and 9: Using the LAMBDA Worksheet Function to Create Reusable Functions for Common CalculationsExamples 10 and 11: Using the LAMBDA Worksheet Function to Simplify Complex Formula ReportsNew LAMBDA Helper Functions That Allow You to Use a LAMBDA Function Value in the WorksheetExample 12: Three Examples of LAMBDA Helper FunctionsKey Concepts in Chapter 16Practice Problems for Chapter 16
Chapter 17: Data Analysis Basics: Standard Pivot Tables, Sorting, Filtering, Visualizations, and More
The Standard PivotTable ToolExample 1: Using a Standard PivotTable to Create a Product Sales and Costs ReportExample 2: Creating a Frequency Distribution from Customer Survey Data with the Standard PivotTable ToolExample 3: Using the Sort Tool to Sort the Fastest Race Times to the Top for Each TrackExample 4: Sorting by ColorExample 5: Using the Filter Tool to Extract the Blue Quad Sales RecordsExample 6: Filtering to Extract Student Records Based on AND and OR Logical TestsMore About the Fast and Easy Standard PivotTable ToolExample 7: Changing the Functions in a Standard PivotTable by Using Summarize Values ByExample 8: Using the Standard PivotTable Group Feature to Create a Yearly Sales ReportExample 9: Filtering a Standard PivotTable Report Using a SlicerExample 10: Creating Seven Standard PivotTable Reports with a Single ClickExample 11: Creating a Cross-Tabulated Report and Visualization Using a Standard PivotTable and PivotChartVisualizing with Excel ChartsExample 12: Visualizing with a Clustered Column Chart or a Stacked Column ChartExample 13: Visualizing with a Clustered Bar Chart or a Stacked Bar ChartExample 14: Creating a Frequency Distribution and Histogram Chart with a PivotTable and a PivotChartExample 15: Visualizing with a Line Chart to Show Changes and Trends over TimeExamples 16 and 17: Using an X-Y Scatter Chart to Visualize the Relationship Between Two Quantitative VariablesExample 18: Analyzing Data Using Artificial Intelligence to Create a Beautiful X-Y Scatter ChartExample 19: Visualizing Tables of DataExample 20: Using PivotTable StylesExample 21: Using Conditional Formatting to Add a Heat Map or Color Scales to a Table ReportExample 22: Using Conditional Formatting for a PivotTable Report to Show the Top 5 ValuesExample 23: Using Data Bars Conditional Formatting to Make an In-Cell HistogramExamples 24 and 25: Using Worksheet Date Formulas to Create Date Attribute FieldsCleaning 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 FieldsExample 27: Using Flash Fill to Perform One-Time Cleaning Tasks Where the Pattern Is ConsistentExample 28: Using Dashboards to Gauge Activity as New Data ArrivesKey Concepts in Chapter 17Keyboard Shortcuts Learned in Chapter 17Practice 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 DashboardsMicrosoft’s Data Analysis ToolsImportant Data Analysis Definitions and ConceptsData Modeling BasicsThe Data Model Tool in Excel Power Pivot and Power BI DesktopThe Process of Turning Data into Information: The 10-Step Data Analysis ProcessData Analysis ExamplesExample 1: Using Worksheet Formulas, a Standard PivotTable, and an Excel Chart to Create a Product Sales Report and VisualizationExample 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 VisualizationComparing M Code, DAX Formulas, Worksheet Formulas, and Standard PivotTable CalculationsExample 3: Using the Power BI Desktop App to Create a Product Sales Report and Power BI Online for Sharing and CollaborationKey Concepts in Chapter 18Practice 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 MetricsProject 2: Importing SQL Big Data, Building DAX Formulas, and Creating an Interactive Power BI Desktop ReportProject 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 ReportProject 5: Using Power Query M Code to Fix Inconsistent Datasets from Multiple Excel Files So They Can Be CombinedKey Concepts in Chapter 19Keyboard Shortcuts Learned in Chapter 19Practice 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 FileShowing the Developer Tab in the Excel RibbonUsing the Macro Recorder and a Form Control Button to Help a User Refresh a QueryUsing the Macro Dialog Box and the VBA Editor Window to View VBA CodeUsing the Macro Recorder and the Use Relative References Button to Write VBA Code That You Can Use for Repetitive TasksUsing the Personal Macro Workbook to Create Universal VBA MacrosCopying VBA Code from the Internet and Pasting It into a VBA ModuleChapter 20 Key ConceptsKeyboard Shortcuts Learned in Chapter 20Practice Problems for Chapter 20
Chapter 21: Financial Worksheet Functions
Financial Terms and Variables Used in Financial Cash Flow AnalysisThe FV FunctionPV FunctionPMT FunctionNPER FunctionRATE FunctionNPV FunctionXNPV FunctionChapter 21 Key ConceptsPractice Problems for Chapter 21
Chapter 22: Simple Linear Regression Worksheet Functions
Simple Linear Regression Variables and DefinitionsExcel Worksheet Functions for Simple Linear RegressionChapter 22 Key ConceptsPractice Problems for Chapter 22
The End
Index

Content preview from Microsoft 365 Excel: The Only App That Matters

Index

Symbols

= (equal sign)

as assignment operator, 635

in formulas, 75

~ (tilde), as wildcard character, 269

* (asterisk), as wildcard character, 269

@ (at symbol), 21

: (colon) operator, 239

adding across worksheets example, 237–241

for lookup cell references, 335

, (comma) reference operator

top N values extraction example, 208–210

{} (curly brackets)

as row positional index operator, 867

. (dot) operator

lookups with, 352–354

stock value table example, 230–232

# (hashtag)

in M code, 624

as spilled range operator, 138

#N/A error, replacing, 277–278

() (parentheses), in formulas, 76

? (question mark), as wildcard character, 269

"" (quotation marks)

in M code, 624

[] (square brackets)

as field access operator, 740, 867

in function arguments, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel Inside Out (Office 2021 and Microsoft 365)

Publisher Resources

ISBN: 9781836645535

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft 365 Excel: The Only App That Matters

by MrExcel's Holy Macro! Books, Mike Girvin

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.