Excel Formulas & Functions: Master 25 Essential Skills

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.

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.

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

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

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