Microsoft 365 Excel: The Only App That Matters

Name: 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
Brand: Holy Macro! Books
Price: 24.49 EUR
Availability: OnlineOnly

Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight

Mike Girvin(Autor*in)

Holy Macro! Books (Verlag)

Erschienen am 1. April 2021

826 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-1-61547-156-0 (ISBN)

24,49 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

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

Systemvoraussetzungen

Als PDF speichern Als Link merken