Excel Formulas & Functions: Master 25 Essential Skills

Learn arithmetic operators in Excel, perform addition, subtraction, multiplication, and division, master autofill with relative and absolute references, and use the sum and product functions for efficient calculations.

Learn to use Excel statistical functions such as max, min, and average (mean), and explore mode and standard deviation, with practical steps to enter and navigate formulas.

Master Excel formulas and functions to calculate mean, median, mode, and standard deviation of data like total sales, using quick keyboard actions and cell highlights.

Learn to use excel's large and small functions to find largest and smallest values in a range, using an array and k to return first, second, and third results.

Learn how to use Excel count functions, including count, countA, and countBlank, to count numbers, alphabets, and blank cells. Practice applying ranges, autofill, and intellisense to analyze data.

Learn to use total, subtotal, and aggregate in Excel to perform sums, handle errors, and manage hidden rows for accurate, robust calculations.

Master the IF function and logical tests in Excel, using true/false outcomes to determine eligibility with value if true or value if false, and autofill to apply logic across data.

Learn to use the if and and functions in Excel to require multiple conditions, such as customer rating ≥70 and attendance ≥4.5, returning a bonus or zero based on test.

Learn to build an if with or in Excel to award a bonus when a condition is met, or use and for logic tests, otherwise return zero.

Master the iferror function to trap errors in Excel formulas and return a result, such as not found, by placing the formula inside iferror and supplying a value if error.

Master the IFS function in Excel to handle multiple logical tests, assign bonuses by score thresholds, and apply relative referencing alongside sumif and sumifs.

Learn to use sumif and sumifs to total sales by single or multiple criteria, defining range, criteria, and sum range for items like yogurt and Canada body wash.

Master how to use average if and averageifs in Excel to compute the mean sales by criteria, such as Austria and Apple, using range and average range.

Learn to apply data validation in Excel by creating dropdown lists from a source, and configuring input messages and error alerts for lookups with vlookup, xlookup, index and match.

Master vlookup to search large data ranges and return names, countries, or departments using a lookup value, table array, and column index number; note the left-to-right constraint and range lookup.

Use vlookup to retrieve price and quantity for multiple products, set up data validation for the product list, and apply array-style column indexes for two results.

Master index and match to retrieve exact values by row and column, then combine them to locate quantities and products, even from right to left, with exact matches.

Master nested index and match lookups to retrieve values by row and column, using a result array and exact-match logic.

Learn how to use Hlookup for horizontal lookups in Excel, compare it with Vlookup, and retrieve products or prices by specifying the table array and row index.

Master xlookup like vlookup to return an employee's name, country, and department by matching the employee id with a lookup array and a return array.

Master advanced xlookup techniques to replace vlookup, build nested xlookup formulas using data validation lists, and answer two questions in one formula for product and continent data.

Apply xlookup to join tables by employee id and retrieve country, department, and age, showing why xlookup outperforms vlookup for linking datasets.

Learn to use the Len function to determine text length, enter the formula, and verify character counts in sample strings.

Master the left, right, and mid functions in Excel to extract characters from text by specifying the number of characters and the start number.

Learn how to split and join text in Excel using textsplit and textjoin, including choosing a delimiter like a space and using ignore_empty to combine text efficiently.

Explore text before and text after in Excel to extract text around a delimiter, using quotes for the string and handling underscores and other characters.

Learn how to use the concat function in Excel to join text strings with proper punctuation and spaces, replacing the old concatenate approach.

Master the today and now functions in Excel formulas, enter with tab and enter, format as long date, and display current date and time.

Learn to use the sort function in Excel to reorder data in ascending or descending order by a chosen sort index, with examples using column A or B.

Learn to use the filter function in Excel to display specific products or categories, set criteria such as greater than or equals to 150, and view results like apples.

Learn to generate a sequence in Excel using the sequence function, choosing a start value and step, and specifying the number of rows to create a custom list.

Apply the unique function to an array to extract distinct products from a list, returning each item once despite duplicates. The next lecture covers the distinct concept.

Discover that there is no distinct function in Excel; use the unique function to return items that appear exactly once, while unique lists each item that appeared at least once.