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.
Table of contents
Preface/Forward/Intro 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 Chapter 2: Why the Spreadsheet Was Invented Key Concepts in Chapter Chapter 3: What Does Excel Do? Key Concepts in Chapter Chapter 4: Key Concepts Used to Create Excel Solutions Key Concepts in Chapter 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 Keyboard Shortcuts Learned in Chapter Chapter 6: Keyboard Keys and Shortcuts Keyboard Shortcuts Key Concepts in Chapter Keyboard Shortcuts Learned in Chapter 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 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 Practice Problems for Chapter 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 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 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 Practice Problems for Chapter 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 Practice Problems for Chapter 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 Practice Problems for Chapter 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 Practice Problems for Chapter 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 Keyboard Shortcuts Learned in Chapter Practice Problems for Chapter 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 Practice Problems for Chapter 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 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 The End Index