Excel Formulas & Functions Masterclass

Explore how arrays group cells and apply a formula across multiple cells at once, including spill errors and Excel 365 behaviors, illustrated by a 3% budget increase.

Learn to create and use array constants in Excel, applying semicolon and comma syntax to build rows and columns, and practice integrating fixed values in formulas like PMT.

Consolidate multi-sheet data by category when layouts vary but category labels align, and practice consolidating Division A, Division B, and Division C using left and top headers.

Learn to use dialog box controls on a worksheet, like lists, spin boxes, and checkboxes, linked to cells to affect calculations. Enable the developer tab to access these form controls.

Insert and link scroll bars and spin boxes in Excel to control and increment values as form controls, then practice linking them to cells in the Creating Advanced Formulas workbook.

Learn to troubleshoot non-text errors in Excel by interpreting suggested fixes, resolving missing parentheses, and validating formulas with evaluate formula and recalculate, while watching operator precedence and circular references.

Learn how to use the iferror function to hide errors by returning a blank, and display the correct value when no error occurs, including nesting with other formulas.

Explore Excel's auditing tools to fix errors by tracing precedents and dependents. Use trace precedents and dependents on the auditing worksheet to map how cells relate and verify formulas.

Learn how Excel functions live in the formulas ribbon and library, and master their argument structure with required and optional inputs like sum, PMT, FV.

Explore text functions in Microsoft Excel to format, extract substrings, join and manipulate text strings with formulas, while learning character handling and anc codes.

Explore the code function, inverse of chas, which returns the ANSI code value for a character. Practice on the code worksheet in the teaching file and use the answer key.

Explore how the text function formats numbers and dates into text, referencing a cell like B4 and applying a custom format with comma separators, then practice on the text worksheet.

Master the textjoin function in Excel to pull together title, first name, middle initial, and last name into a single unified sentence, using a space delimiter and ignoring empty cells.

Learn how the rept function pads a cell with dots to align text in a table of contents. Apply examples with words like advertising, rent, supplies, salaries, and utilities.

Learn to search for substrings with find and search, handle case sensitivity, and combine left, right, mid, and length to extract first and last names.

Learn to remove two different characters from a string using double nested substitute functions in Excel, removing periods and spaces with practical practice on the account numbers worksheet.

Learn to remove line feeds in Excel by using the substitute function to replace character code ten with a space, yielding cleaner text than clean and preventing jammed lines.

Explore using logical and informational functions in Excel, building nested ifs with and/or and arrays, and interrogating workbooks for sheet counts and Excel version.

Introduce the if function as a core logical test that compares values using greater than or equal to, returning a phrase when true and showing false when not.

Learn to use the if function to handle false results by providing a false argument, with greater than or equal to 1000 returning it's big or not big.

Apply if logic to avoid division by zero in Excel, nesting the calculation so if sales are zero it returns sales are zero instead of an error.

Explore performing multiple logical tests with nested ifs and the IFS function, and using and/or operators to enhance worksheet logic through hands-on practice.

Use nested IFS to implement a tiered bonus system in Excel, awarding $1,000 for growth under 10% and $10,000 for growth above 10%, with no bonus for negative growth.

Learn to use the and function to require multiple conditions, like sales greater than target and units sold over 100, to determine bonus eligibility with practical examples.

Learn to perform conditional sums in Excel by nesting an if inside sum, using fixed references, to sum values in column C when the year in column B matches criterion.

Count occurrences in a range by nesting an if inside a sum, summing ones wherever the value equals the range, illustrating that the item appears twice.

Use if logic and nested and conditions to categorize accounts receivable aging into overdue invoice ranges (1–30, 31–60, 61–90, 91–120, over 120) and return corresponding values.

Explore information functions in Excel, contrast them with logical functions, and learn how is functions and the cell function return information when criteria are met.

Use the error.type function to identify the specific error in a cell, enabling differentiated handling in nested formulas and returning the error type for further processing.

Explore the sheet and sheets functions to identify current sheet numbers, total sheets in the workbook, and the sheet number for a named sheet or cell.

Explore Excel's is functions, including is blank, is error, is even, is number, is text, and more. Learn how these tests interact with logical formulas and how to nest them.

Explore lookup functions in Excel formulas, including vlookup, lookup, and xlookup, and learn to build lookup tables, use index match, and the choose function for advanced data retrieval.

Explore how lookup tables work, from two-column designs to single-column lookups, retrieving corresponding values from rows, columns, and arrays.

Master how to integrate the choose function with worksheet option buttons to dynamically calculate shipping costs from a linked weight input.

Explore lookup functions in Excel by comparing legacy lookups with VLOOKUP and HLOOKUP, and then harness XLOOKUP for flexible value retrieval across ranges or tables.

Master Vlookup for exact matches by setting the last argument to false, using account numbers to return the corresponding account names, and practice this in the customer accounts worksheet.

Explore exact-match lookups with hlookup and in-cell drop-down lists created through data validation, comparing horizontal lookups to vlookup and using false for exact results.

Apply index with form controls to pull item names from a linked cell in a list or combo box, using array and row concepts for lookup.

learn how index match enables using any column as the lookup column, unlike Vlookup, to retrieve quantities from arbitrary columns with exact match lookup.

Discover how XLOOKUP replaces VLOOKUP and HLOOKUP with a modern, versatile, dynamic lookup that works horizontally or vertically, auto-adjusts to inserted columns or rows, and simplifies exact-match searches.

Explore using xlookup for exact-match lookups with in-cell drop-downs, and see how xlookup automatically adjusts the return array for May totals when rows are added, unlike hlookup.

Use Xlookup to search for a part number in column H and return the quantity from column C, demonstrating simpler syntax than index match and enabling any column lookup.

Demonstrates creating multiple-column lookups with nested XLOOKUPs in Excel, using B1 as the lookup value to retrieve Nancy Dell'olio's title from column C.

Learn how Excel stores dates as serial numbers and times as day fractions, format them, and adjust two-digit year interpretation in Windows settings.

Master Excel date functions by using the year, month, and day functions to extract year, month, and day from today or worksheet dates, including nesting today inside these calls.

Master the EOMONTH function to find end-of-month dates, add or subtract months from any date, and apply this flexible tool to accounting tasks.

Calculate the nth occurrence of a weekday in a month with Excel by using the date function, compare weekdays, adjust to the first occurrence, and add weeks times seven.

Calculate the number of days between two dates with the days function, using text inputs and the date function to yield positive or negative results based on date order.

Apply the net work days function to count workdays between dates, optionally excluding holidays and weekends, with example from December 1, 2024 to January 10, 2025 yielding 28 working days.

Master the year frac function in Excel to compute what fraction of the year has elapsed between start and end dates, with the optional basis for prorating benefits.

Extract hour, minute, and second components from time in Excel using the hour, minute, and second functions, and practice with the now function for seconds as fractions of the day.

Master essential Excel math functions for business analysis, including rounding functions, sum functions, mod, rand, and advanced let and lambda in Microsoft 365, with practical case studies.

Master the round function and other rounding functions in Excel, including round up, round down, ceiling, floor, and trunc, with practical examples for decimals, integers, and negative digits.

Master the sumifs function to add sales across multiple criteria and criteria ranges, such as region and product type, with clear syntax and practical Excel examples.

Learn to create cumulative totals in Excel with a sum function using a fixed first reference and a relative second. Track interest and principal over time in an amortization worksheet.

Master the mod function by calculating remainders after division, with practical examples like 13 mod 2 equals 1 and 5 mod 3 equals 2.

Use the mod function with sum and if to add every nth row in Excel based on row numbers. See a practical example for summing every third row.

Explore ledger shading in Excel with conditional formatting using a mod(row,2) formula to shade every other row, inside a table or the worksheet range.

Generate random n-digit numbers in Excel by nesting rand inside integer, using ten to the n and ten to the n minus one to control digits, with an eight-digit example.

Explore how the rand array function creates an array of random numbers in Excel, with control over rows, columns, and integer outputs between a floor and ceiling.

Explore Excel's formula language and the let function, which lets you define and reuse variables inside formulas, enabling calculations like the first day of month and the nth weekday.

Create custom Excel functions with lambda, bypassing Visual Basic. Define a hypotenuse length function using height and base and the Pythagorean theorem, then apply it in Excel.

Explore how to use Excel's lambda with reduce to process arrays, counting values over 300,000 and calculating 5% of those amounts, using an accumulator.

Explore how to use a lambda to process an array with the by call function, applying the lambda to each column to compute the max (and min) values.

Explore using a lambda with by row to process a sales data array and return each row's maximum value, showcasing lambda and by row in Excel 365.

Master pricing formulas in Excel by calculating cost price, markup, selling price, markup rate, discount strategies, and break-even analysis to keep products profitable.

Analyze how to compute net price, discount amount, and discount rate from list price using Excel formulas for your business.

Learn to calculate cost of goods sold (COGS) in Excel using starting inventory plus direct costs minus ending inventory, and distinguish COGS from indirect costs.

Explore fixed asset ratios to assess business health: fixed asset turnover ratio, return on fixed asset, and fixed assets to short term debt, with Excel examples.

Calculate the liquidity index from accounts receivable and inventory, using AR duration and days to liquidate, to show how quickly assets convert to cash; a lower index means faster conversion.