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.

Format:

Book

Author/Creator:

Girvin, Mike.

Language:

English

Subjects (All):

Electronic spreadsheets.

Microsoft Excel (Computer file).

Physical Description:

1 online resource (826 pages)

Edition:

1st ed.

Place of Publication:

Chicago : Holy Macro! Books, 2021.

Summary:

This is a book about Microsoft 365 Excel, or 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 to an advanced level by telling a logical story about how to use Excel to solve calculation-based problems and answer crucial questions.

Contents:

Intro

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 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.

Notes:

Description based upon print version of record.

Description based on publisher supplied metadata and other sources.

Other Format:

Print version: Girvin, Mike Microsoft 365 Excel: The Only App That Matters

ISBN:

9781615471560

OCLC:

1333084296