Advanced Excel Formulas : Unleashing Brilliance with Excel Formulas

Enhance and upgrade your Excel knowledge with this comprehensive guide to formulas in Excel. Over 150 of the most useful Excel functions are covered with numerous practical examples of their use. This book is fully updated and includes examples of the most recently released functions in 2022. It is written in a manner that you can read it cover-to-cover or pick it up and learn something new in just 5 minutes. The book begins with a primer on Excel formulas and functions. Starting from a basic level, but diving into intricate detail and building a solid understanding of the fundamentals. It may not always be sexy, but learning why , in addition to how, is important to truly understanding Excel formulas. Having strong fundamentals is the most advanced skill. The book covers all the most popular functions in Excel including VLOOKUP, SUMPRODUCT, COUNTIFS, MATCH, SORT and so much more. You will learn how to return multiple results with a single formula. Harness the power of the dynamic array engine and functions such as SEQUENCE, SORTBY, UNIQUE, XLOOKUP and FILTER to create top N Lists, models and reports that would seem impossible without array formulas. In the final chapter, we discover the amazing LAMBDA function in Excel. Create your own Excel functions to simplify future Excel formulas and share them with others. In this book, the formulas are not only written to return values to the grid, but also for use with other Excel features such as charts and Conditional Formatting, to take them to another level. Practice files are provided to follow all examples shown in the book. What Will You Learn Gain intimate knowledge of Excel formulas Understand efficient and practical use of Defined Names and Tables Master the most popular functions of Excel – VLOOKUP, COUNTIFS, MATCH, SUMPRODUCT, and so much more Learn to return multiple results from a single formula with the magical array formulas. Know the best lookup functions for dynamic spreadsheets Know true power of classic Excel functions such as IF, SUM, and INDEX Use new Excel functions including XLOOKUP, VSTACK, LET, and LAMBDA Who Is This Book For Excel users who are familiar with formulas but want to improve their current skill level Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Chapter 1: Excel Formulas: A Quick Primer Anatomy of an Excel Formula Breakdown of a Function Table of Formula Characters Order of Calculation Order of Range Operations Order of Mathematical Operations The Function Wizard Cell Referencing Absolute References Relative References Explained Making a Reference Absolute Mixed References Sheet References Workbook References Calculating Percentages Percentage of a Value Percentage of a Total Increase a Value by a Percentage Decrease a Value by a Percentage Calculate Percentage Change When Formulas Go Wrong Formula Errors Automatic Error Checking Excel Formula Is Not Calculating Formula Calculations Are Set to Manual Show Formulas Is Switched On Formula Is Stored As Text Cell Formatted As Text Summary Chapter 2: Logical Functions What Is TRUE and FALSE? The IF Function Example 1: Testing Numeric Values Example 2: Testing Text Values Example 3: Exact Text Match Example 4: Partial Text Match Example 5: Testing Date Values Example 6: Testing If a Cell Is Empty or Blank Example 7: Return a Symbol or Emoji Nested IF Functions IFS Function AND, OR, XOR, and NOT Functions AND Function OR Function XOR Function NOT Function The SWITCH Function Example 1: Match Against a List of Text Values Example 2: Evaluate to TRUE Example 3: Match a Formula Result to the List of Values Example 4: Pick Formula from a Drop-Down List Handling Errors IFERROR Function IFNA Function Logical Functions with Excel Features Format Values That Meet a Goal Conditional Formatting Rule with Multiple Conditions Data Validation Rule Based on Another Cell Value Conditional Formatting with Charts Dynamic Chart Data with SWITCH Summary Chapter 3: Defined Names Named Ranges Why Use Named Ranges? Define a Named Range with the Name Box Using Named Ranges in Formulas Rules for Defining Names Tips for Defining Names Named Ranges with Multiple Cells Named Range for a Drop-Down List Define a Dynamic Named Range Define Names with Create from Selection Scope of a Defined Name Define a Named Constant Finding Named Ranges Relative Named Ranges Named Formulas Managing Names Edit a Name Filtering Names Print a List of the Names Apply Names to Existing Formulas Summary Chapter 4: You Need to Start Using Tables Why Use Tables? Format a Range As a Table Creating a Table Changing or Removing Table Styles Naming the Table Working with Tables Table References in a Formula The Magic of @ Referencing Table Elements Make Table Column and Cell References Absolute Tables with Other Excel Features Dynamic Lists Dynamic Charts Summary Chapter 5: Manipulating Text Extract Characters from a Text String LEFT and RIGHT Extract Characters from the Middle of a String FIND and SEARCH for Irregular Strings Example 1: Extract Characters Before a Delimiter Example 2: Extract Characters After a Delimiter Example 3: Combining LEFT and RIGHT Example 4: Extract Text Between Two Characters Example 5: Extract Number from a Text String Change the Case of Text The LEN Function Convert Text to a VALUE The NUMBERVALUE Function Remove Unwanted Characters The TRIM Function The CLEAN Function SUBSTITUTE and REPLACE Functions The REPLACE Function The SUBSTITUTE Function Example 1: Simple SUBSTITUTE Example Example 2: Count the Number of Words in a Cell Example 3: Use the Instance Number to Extract Text Example 4: Return Text After the Last Delimiter TEXTBEFORE and TEXTAFTER Functions Example 1: Text Before or After the First Delimiter Example 2: Specify an Instance Number Example 3: Extract Text Between Two Characters Example 4: Using Match End and If Not Found The TEXTSPLIT Function Example 1: Simple TEXTSPLIT Example 2: Using Multiple Column Delimiters Example 3: Splitting Across Rows (and Columns) Example 4: Handling Missing Data Combine Text into One Cell The CONCATENATE Function The Ampersand Character The CONCAT Function The TEXTJOIN Function TEXTJOIN and Delimiters TEXTJOIN and Empty Values TEXTJOIN and Arrays The TEXT Function Example 1: Currency Formats Example 2: Positive and Negative Value Formats Example 3: Date Formats Repeating a Character – REPT Function Text Functions with Other Excel Features Conditional Formatting Rules – Last Character Equals Data Validation Rules – Forcing Correct Case Dynamic Labels for Charts Summary Chapter 6: Working with Dates and Time Understanding Dates and Time Dates and the Calendar System in Excel Calculate the Difference Between Two Dates Time in Excel Calculate the Difference in Two Times The TODAY and NOW Functions Convert Text to Date The DATEVALUE Function The VALUE Function The DATE Function Extract Date from a Date-Time Stamp The INT Function The TRUNC Function Extract Time from a Date-Time Stamp The MOD Function The YEAR, MONTH, and DAY Functions The NETWORKDAYS Function Calculate the Working Days Difference Between Two Dates The NETWORKDAYS.INTL Function Example 1: Customized NETWORKDAYS.INTL Function Example 2: Conditional Holiday Ranges Example 3: Number of Fridays Between Two Dates The YEARFRAC Function The DATEDIF Function The WORKDAY Function Calculate a Due Date from a Starting Date The WORKDAY.INTL Function Calculate First Working Day of a Month EDATE and EOMONTH Calculate Contract End Dates Last Day of a Month Number of Days in a Month Last Working Day of a Month First Working Day of Next Month Returning Week Numbers The WEEKNUM Function What Is ISOWEEKNUM? Return the Week Number for Any Given Date Week Number in a Month The WEEKDAY Function Rate of Pay Determined by Day of Week Calculating Financial Years and Quarters Fiscal Years Indian Financial Year Australian Financial Year UK Financial Year Fiscal Quarters Working with Time Convert Text to Time The TIMEVALUE Function The TIME Function Rounding Time MROUND, CEILING.MATH, and FLOOR.MATH Round Time to the Nearest Hour Round Time to the Nearest 15 Minutes Time Difference Past Midnight Sum Hours over 24 Hours Handling Negative Time Date and Time Functions with Other Excel Features Highlight Approaching Due Dates Highlight Dates Due Within the Next Month Data Validation Rules – Prevent Weekend Entries Summary Chapter 7: The Infamous VLOOKUP Function Introduction to VLOOKUP VLOOKUP for an Exact Match VLOOKUP for a Range Lookup Column Index Number Tricks Trick 1: The COLUMN Function Trick 2: The COLUMNS Function Trick 3: Using an Index Row Reasons Why VLOOKUP Is Not Working Lookup Column Must Be the First Column Data Type of the Values Must Match Exact Match Has Not Been Specified Ranges Must Be in Ascending Order Handling Fake Errors with VLOOKUP The HLOOKUP Function Get VLOOKUP to Look to the Left Partial Match Lookup Case-Sensitive VLOOKUP Multiple Column VLOOKUP Return the Nth Match VLOOKUP with Other Excel Features Conditional Formatting and VLOOKUP Dynamic Chart Range Summary Chapter 8: The Awesome SUMIFS, COUNTIFS, and Friends The SUMIFS Function SUMIF or SUMIFS Using Text Criteria Working with Multiple Criteria Using Numeric Criteria Sum Values Between Two Dates OR Logic with SUMIFS Using Wildcards with SUMIFS The Versatile COUNTIFS Function Comparing Two Lists Generating Unique Rankings Creating Conditional Rankings Creating a Frequency Distribution The AVERAGEIFS Function TRIMMEAN Function Conditional MEDIAN and MODE Functions Calculating the Median Calculating the Mode MAXIFS and MINIFS Functions Using the Functions with Other Excel Features Identifying Duplicates with Conditional Formatting Identifying Unmatched Values Between Two Lists Highlight Values Larger Than the Median Preventing Duplicate Entries Summary Chapter 9: Next-Level Aggregation Functions SUMPRODUCT Function Classic SUMPRODUCT Sum Values That Meet Multiple Criteria Count Values That Meet Multiple Criteria Aggregating Multiple Columns Distinct Count Formula Weighted Average Sum Every Nth Cell Sum Values for a Specific Weekday Sum the Top Five Values Only AGGREGATE Function Ignoring Hidden Rows Ignoring Errors in a Range Adding Criteria to AGGREGATE Function Specified by a Cell Value Summary Chapter 10: Dynamic Array Formulas Getting to Know Dynamic Array Formulas Arrays vs. Dynamic Arrays Dynamic Arrays with Tables Dynamic Array Formula Example Welcome the # Operator The #SPILL! Error DA Formulas Cannot Be Used Within Tables UNIQUE Function Returning Distinct and Unique Values UNIQUE with Multiple Columns UNIQUE to Compare Columns Distinct Labels for SUMIFS Distinct Count Formula SORT Function Simple SORT Example Sort the Distinct Values Sort by Specified Column Sort by Multiple Columns SORT Function Returning Specific Columns from a Table SORTBY Function Simple SORTBY Example Sort by Multiple Columns with SORTBY Sort by Column Outside of the Returned Array Sort Product Name by Sales Totals SEQUENCE Function Stepping into the SEQUENCE Function Changing the Row/Column Order Sum the Top N Values Sum the Last N Days Sum the Last N Months Last N Months by Country – Pivot Style Report Sum Values from Alternate Rows/Columns RANDARRAY Function RAND and RANDBETWEEN Introduction to RANDARRAY Pick a Name from a List at Random Shuffle a List of Names FREQUENCY and TRANSPOSE Functions FREQUENCY Function TRANSPOSE Function PivotTable Style Report Using Formulas SUM v2.0 – The DA Version Sum Based on Complex Criteria Sum with Arrays Sum Based on Multiple Columns Dynamic Array Formulas with Other Excel Features DA Formulas with Conditional Formatting DA Formulas with Data Validation DA Formulas with Charts Summary Chapter 11: Advanced Lookup Functions MATCH Function Compare Two Lists Dynamic Column Index Number in VLOOKUP Sort a Range by Drop-Down List Value XMATCH Function XMATCH Defaults to an Exact Match Returning the Position of the Last Match CHOOSE Function Generating Random Sample Data Return a Range Based on a Cell Value Choosing a Function from a List Using CHOOSE with Form Controls INDIRECT Function Indirect Table Reference in VLOOKUP Referencing Table Columns with INDIRECT Referencing Other Worksheets Pivot Style Report with Dynamic Row Labels R1C1 Reference with INDIRECT OFFSET Function Simple Examples Sum the Values in the Nth Column Return the Last X Columns OFFSET with COUNTIFS INDEX Function Introduction to the INDEX Function Basics of the INDEX Function INDEX and MATCH/XMATCH for Versatile Lookups Two-Way Lookup with INDEX and MATCH/XMATCH Using Wildcards with INDEX and MATCH/XMATCH Last Occurrence of a Value with XMATCH Last Value in a Row/Column Sum Values in the Last Row/Column Last Value in a Row/Column for a Specific Match Most and Least Frequently Occurring Values Returning a Range with INDEX Moving Average Formula Working with Arrays and Spilled Ranges Creating a Top N List Extracting Headers from a Table/Range Returning Rows/Columns from a Spill Range Returning Non-adjacent Columns from a Table with INDEX-MATCH Returning a Reference from a List CHOOSECOLS and CHOOSEROWS Functions CHOOSECOLS to Return Specific Columns CHOOSECOLS with MATCH CHOOSE for Absolute Column Selection Reordering Columns with CHOOSECOLS Returning Every Nth Column CHOOSEROWS to Return Every Nth Row Returning the Top and Bottom N Values TAKE and DROP Functions VSTACK and HSTACK Functions Stacking Multiple Tables with VSTACK Adding Calculated Columns to an Array with HSTACK Lookup Functions with Other Excel Features Dynamic Data Validation List with INDEX or OFFSET Dependent Data Validation List with INDIRECT Format Last X Number of Rows Interactive Chart with INDIRECT Interactive Chart with CHOOSEROWS Rolling Chart with INDEX Summary Chapter 12: XLOOKUP: The New Kid on the Block Introduction to XLOOKUP XLOOKUP for an Exact Match XLOOKUP to Look Up a Value in Ranges XLOOKUP with Wildcards XLOOKUP for the Last Match Multiple Column XLOOKUP Two-Way Lookup with XLOOKUP Three-Way Lookup Three-Way Lookup with Mixed Match Modes Returning Multiple Values with XLOOKUP XLOOKUP with SUMIFS for a Dynamic Sum Range XLOOKUP with Other Excel Features Dependent Drop-Down List with XLOOKUP Return a Range for a Chart Summary Chapter 13: FILTER Function: The Game Changer Introduction to the FILTER Function Simple FILTER Example FILTER with Multiple Conditions How the FILTER Function Logic Works Further Examples Sort the FILTER Results #CALC! Error with FILTER Adding a Calculated Column to the Returned Array FILTER and Return Non-adjacent Columns UNIQUE Ignoring Blanks FILTER with NETWORKDAYS.INTL FILTER and Combine Results in One Cell Compare Lists with FILTER Names That Occur in Both Lists Names That Occur in One List Only Names That Occur in Either List but Not Both Return All Names from Both Lists Aggregate FILTER Results FILTER with Other Excel Features Shrinking Drop-Down List Dependent Drop-Down List Interactive Chart Summary Chapter 14: Formulas with Data Types in Excel Introduction to Data Types in Excel Accessing the Data Type Fields with Formulas Aggregating the Values in a Data Type Field FIELDVALUE Function Using Functions with Data Type Data Creating a Custom Data Type with Power Query XLOOKUP with Data Types Conditional SUM Function with Data Types FILTER Function with Data Types Currency Conversion Using Data Types Historical Stock Data with STOCKHISTORY Simple STOCKHISTORY Example Adding Properties to STOCKHISTORY Inserting a Stock Chart Using STOCKHISTORY Data Returning Historical Exchange Rates with STOCKHISTORY Summary Chapter 15: LET, LAMBDA, and the Helper Functions LET Function: Speed Up and Simplify Your Formulas Using LET in Your Formulas LAMBDA: Create Your Own Functions Writing the LAMBDA Function Testing Your LAMBDA Function Defining a Name for Your LAMBDA Second LAMBDA Example Using LET with LAMBDA The LAMBDA Helper Functions ISOMITTED: Optional Arguments in LAMBDA Functions BYROW BYCOL Complex Example: One Formula Report MAP Converting Values from Drop-Down Value Mapping Text Values Using BYROW with MAP Multiple Arrays with MAP SCAN REDUCE MAKEARRAY Advanced Formula Environment Getting the AFE Add-in Managing Your Named Formulas Sharing Your LAMBDA Functions Importing LAMBDA Functions from a GitHub Gist Creating Named Formulas in the AFE Summary Index Enhance and upgrade your Excel knowledge with this comprehensive guide to formulas in Excel. Over 150 of the most useful Excel functions are covered with numerous practical examples of their use. This book is fully updated and includes examples of the most recently released functions in 2022. It is written in a manner that you can read it cover-to-cover or pick it up and learn something new in just 5 minutes. The book begins with a primer on Excel formulas and functions. Starting from a basic level, but diving into intricate detail and building a solid understanding of the fundamentals. It may not always be sexy, but learning why, in addition to how, is important to truly understanding Excel formulas. Having strong fundamentals is the most advanced skill. The book covers all the most popular functions in Excel including VLOOKUP, SUMPRODUCT, COUNTIFS, MATCH, SORT and so much more. You will learn how to return multiple results with a single formula. Harness the power of the dynamic array engine and functions such as SEQUENCE, SORTBY, UNIQUE, XLOOKUP and FILTER to create top N Lists, models and reports that would seem impossible without array formulas. In the final chapter, we discover the amazing LAMBDA function in Excel. Create your own Excel functions to simplify future Excel formulas and share them with others. In this book, the formulas are not only written to return values to the grid, but also for use with other Excel features such as charts and Conditional Formatting, to take them to another level. Practice files are provided to follow all examples shown in the book. What Will You Learn Gain intimate knowledge of Excel formulas Understand efficient and practical use of Defined Names and Tables Master the most popular functions of Excel - VLOOKUP, COUNTIFS, MATCH, SUMPRODUCT, and so much more Learn to return multiple results from a single formula with the magical array formulas. Know the best lookup functions for dynamic spreadsheets Know true power of classic Excel functions such as IF, SUM, and INDEX Use new Excel functions including XLOOKUP, VSTACK, LET, and LAMBDA Who Is This Book For Excel users who are familiar with formulas but want to improve their current skill level. -- Provided by publisher Advanced Excel Formulas is a comprehensive book containing more than 500 Excel formula examples. It is also fully updated to include the new functions released in recent years including more than ten functions released in 2022.This book will benefit anyone who works with Excel regularly. The focus is on users who understand formulas but want to push their skills to the next level and fill any gaps in their knowledge.