Mastering Microsoft Excel Formulas and Functions

Explore the universal order of operations, compare Pemdas and Bodmas, and apply parentheses, exponents, division, multiplication, addition, subtraction, and left-to-right rules in Excel formulas.

Explore how relative cell references adjust when copying a sum formula across rows, using four quarterly budgets to automatically total each department.

Master absolute cell references in Excel by locking the commission rate in a single cell, demonstrate with a 5% rate in D2, and ensure formulas copy correctly across rows.

Master mixed cell references by locking the column or row with dollar signs, copying formulas across cells, and applying relative and absolute references to a price, quantity, and discount scenario.

Learn how to quickly make cell references absolute in Excel using the F4 shortcut, cycling through absolute, column absolute, row absolute, and relative references to save time.

Explore the basics of excel function syntax, learn how to use the equals sign, function names, and arguments to sum ranges and perform pre-built calculations.

Learn how to quickly insert the sum formula with the Alt + = shortcut in Excel, ensuring the correct range of adjacent cells and adjusting ranges as needed.

Master rounding numbers with the ROUND() function, specifying the target value and digits to keep, then compare zero-digit rounds vs two-digit precision using practical examples.

Explore how to use the round function in Microsoft Excel to round numbers to the nearest ten or hundred, including using negative digits and practical examples.

Learn how to nest Excel functions by combining average, round, and mod in one formula, using a seven-day sales example to analyze rounded averages.

Learn to use the CONCAT function to join text values, such as first and last names, into a single string, and compare it with the older concatenate function.

Learn how to use the left, right, and mid functions to extract characters from product codes, producing the product prefix, suffix, and batch number.

Use the trim function in Excel to clean up a string by removing extra spaces and non-printable characters.

Learn how the proper function cleans up text by converting a string to proper case, capitalizing the first letter of each word to unify data entry.

Learn how to convert a text string to uppercase using the Excel upper function. Place =upper(B5) in a cell and drag down to apply the conversion across data.

Learn to nest text-based functions in Microsoft Excel to clean and standardize feedback by trimming spaces and applying proper case, using len to compare character counts.

Use the now function to return the current date and time in Excel. Recalculate on each refresh to stay current, unlike a static value.

Format the results of today and now using the text function to customize date and time formats through nesting, including day, abbreviated month, year, and time with seconds and am/pm.

Use the day function to extract the day from a complete date by feeding it a date’s serial number, then drag to apply to other dates.

Apply the month function in Excel to extract the month from a date or serial number, using a single argument just like the day function.

Learn how the weekday function converts a date to the day of the week in excel, with an optional return type 1–17 and a default sunday start for grouping date data.

Master the eomonth function to find the last day of a month from a start date, with optional month offsets, and format results using text for clear date displays.

Master how the Excel workday function adds workdays to a start date, excludes weekends, and can exclude holidays with absolute references for precise scheduling.

Master the art of nesting date-time functions in Excel by combining network days with today and eomonth to calculate the number of working days to the end of the month.

Explore the Excel 2019 iFS function, which replaces nested ifs with a single multi-test formula, handling up to 127 tests and returning the first true result.

Use the and function in Excel to test conditions (up to 255) and return true or false; nest it in an if to output yes or no for bonus eligibility.

Nest multiple if functions in Excel to assign letter grades from scores. Use thresholds 90, 80, 70, and 60, handling nested arguments.

Explore the Microsoft Excel istext() function to identify text values. Compare it with isnumber, test real examples like C4, and return true for text and false for non-text values.

Master nesting excel logical functions to categorize stock levels by text or numeric values, using is number, is text, and ifs to label overstock, low stock, or out of stock.

Master the hlookup function to perform horizontal lookups in Excel, using the first row of a table, absolute references, row index, and exact match to retrieve temperatures.

Explore the Excel index function and its array concept, learning to specify a row and optional column to retrieve intersection values, and how match enhances dynamic lookups.

Explore the Excel match function: search a value in a lookup array, return its position, and use exact match with zero; see how it pairs with index for dynamic lookups.

Use the indirect function to convert month labels into sheet references and pull data from multiple worksheets, building dynamic references across months.

Discover xlookup, a newer alternative to vlookup that uses separate lookup and return arrays, supports exact matching, and handles not found values, introduced in Excel 2019.

Explore the Microsoft Excel count function in the statistical functions category to count numeric values in a range, such as C4 to C7, including count, counta, and countifs.

Nest index, match, and max functions to dynamically return the salesperson who has the maximum value, demonstrating how to look up the exact row for the top value.

Compute subtotals for each category using the sumif function, with electronics, fashion, and appliances as examples. Use absolute references and copy the formula down to generate each subtotal.

Learn how to use the sumifs function to total sales by applying multiple criteria, such as electronics category and north region, summing the sales range that meets those criteria.

Explore the d average database function to compute conditional averages, using department criteria (such as IT) and years of experience, with rounding to whole years.

Demonstrate the excel dmin function to return the minimum price for electronics from a database using category criteria, mirroring dsum, daverage, and dcount, with multi-criteria options.