Complete Formulas & Functions Masterclass in MS Excel [2023]

Learn the fundamentals of Microsoft Excel formulas by exploring formula connections, tracking precedents and dependencies, and mastering relative, absolute, and next cell references while handling common errors.

Learn four core Excel formula rules: always start with =, enclose text in quotes, use an asterisk for multiplication, and balance all parentheses to avoid errors.

Trace cell connections in Excel using formula auditing to identify precedents and dependents, visualize relationships with arrows, and learn how to analyze complex formulas and references.

Explore relative, absolute, and mixed cell references in MS Excel, and learn how dragging formulas updates references and locks cells with dollar signs.

Explore six common Excel errors (#VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, ###) and learn practical fixes using formulas like sum, if, and iferror to avoid them.

Master the basic fundamentals of Microsoft Excel formulas, learn four key rules to handle common errors, track precedents and dependents, understand cell reference types, and explore statistical functions.

Discover the most used statistical functions in Excel, including sum, average, count, counta, max, mean, median, mode, and subtotal, with practical usage tips for beginners and advanced users.

Explore how to use the most common Excel formulas—sum, count, counta, and average—on a test data set, including selecting ranges, autofill, and adjusting formatting.

Learn to find the highest and lowest numbers in a selected range using max and min formulas by typing =, selecting the range, and pressing enter.

Apply formulas to compute mean (average), median, and mode in Excel using the corresponding formulas. See how selecting a range, entering the formulas, and interpreting the results works.

Explore the ABS function in MS Excel to compute the absolute value, turning negative numbers positive while leaving positives and zeros unchanged, using =ABS(number) and auto-fill for a dataset.

Explore how the subtotal function computes aggregates for visible rows, with options like count, sum, and average, and include or exclude hidden rows using function numbers.

Summarize values, compute mean and median, and count items; identify the highest and lowest values, include subtotal, and prepare for time, date, and if statements.

Explore core Excel functions starting with if statements, including if, iferror, and ifna, then wildcards, logical operators, and and/or conditions, with examples of count and average functions.

discover how the if function performs a logical test to determine pass or fail at a 60 threshold, and how the ifs function simplifies grading with multiple conditions.

Understand how to use iferror and ifna in Excel to replace errors with specified results, compare their behavior for lookup formulas, and apply absolute references to locked ranges.

Explore wild cards in excel formulas using asterisk, questionmark, and tilt to perform fuzzy text matching in functions like countif and lookup, noting they work only with text.

Learn how logical operators in Excel perform comparisons and shape formula criteria, and use them with if statements, including equal, greater than, not equal, and blank-cell handling.

Learn the and and or logical functions in Excel to test multiple conditions, return true or false, and assign grades from 60–70, 70–80, 80–90, and 90+ with nested ifs.

Master the COUNTIF function in Excel to count cells by criteria across dates, numbers, and text, using wildcards, paid status markers, and not blank cells.

Master the sumif function to sum cells meeting a criteria using ranges and a sum range, with examples for under 101 dollars, December invoices, and paid vs not paid statuses.

Learn to use the averageif function to average values in a range that meet criteria, including dates, numbers, or text, using wildcards and an optional average range.

Master COUNTIFS in Excel to count with multiple criteria across dates, amounts, and text, using date functions to filter invoices by December and January.

Master the sumifs function in Excel to sum invoice amounts by multiple criteria using range and criteria pairs, up to 127 pairs, with support for dates, numbers, text, and wildcards.

Master the averageifs function to compute the average of values that meet multiple criteria, including date ranges and amounts. Use criteria ranges and logical operators to filter December invoices.

Learn how the sumproduct function multiplies corresponding array elements, sums the results, supports up to 30 arrays, and uses the double unary to count matches and evaluate criteria.

Master Microsoft Excel if statements and related functions, including count, average, and/or logic, combined with if. Learn to handle errors with IFERROR and summarize arrays with the PRODUCT function.

Explore lookup formulas in Excel, including lookup and index match, essential for day-to-day data tasks and building mastery in the complete formulas & functions masterclass.

Explore the look up function in Excel to perform approximate matches, map scores to levels, and compare lookup and hlookup for smallest matching values.

Use vlookup to retrieve data from a vertical table, enabling exact or approximate matches and wild cards, with the lookup value in the first column and the needed column index.

Master dynamic two-way lookups in Excel by combining VLOOKUP with MATCH to derive a column index, enabling location-by-month sales queries and dashboard-ready analytics.

Use HLOOKUP to retrieve data from a horizontal table, choosing approximate or exact matching and handling errors with IFERROR. Compute commissions in percentage and dollars from sales data.

Explore how index and match work together to retrieve values from a two-dimensional data table, including dynamic left lookups and real-world examples with sales by location and month.

Master multi-criteria lookups in Excel using index and match with boolean logic. Learn to combine color, model, and maker filters for precise results.

Conclude the lookup functions section by applying complex formulas in Microsoft Excel through practice and play to impress your boss.

Explore text functions in Excel, including text, concatenate, left, mid, right, upper, lower, and proper, plus the stream function, to cleanly organize and polish spreadsheets.

Master text and concatenate functions in Excel to format dates, numbers, currency, with text codes and quotes, and learn to combine text with values using concatenate or the and function.

Discover how the TEXTJOIN function concatenates a range of cells with a delimiter, using the ignore empty option to skip blanks, with practical Excel 365/2019 examples.

Learn how the fixed function converts numbers to text with a fixed number of decimals and optional commas, and why you may prefer the text function for concatenation.

Explore left, right, mid, find, search, and len to extract text, locate positions, and combine functions for Excel data tasks.

Explore upper, lower, and proper text functions in Excel to standardize case without affecting numbers or punctuation, with practical examples using text and cell references.

Learn how the trim function removes extra spaces, leaves a single space between words, and strips leading or trailing spaces to clean data from external sources.

Master text functions in Excel, including concatenate, left, right, middle, find, search, upper, lower, and proper. Then transition to time and date functions in this complete formulas and functions masterclass.

Explore how time and date are organized in Microsoft Excel and master core time and date functions such as now, date, time, day, month, year, minutes, and seconds.

Excel's date system uses serial numbers starting at 1 to represent dates, enabling date arithmetic, and format switching between numbers and dates like January 1, 1900.

Learn how Excel's time system uses serial dates and fractional days, where 24 hours equal 1 and 12 a.m. equals 0, with hours resetting daily and numeric formatting for results.

Master today and now in Excel to return the current date or date and time, updated on worksheet changes, and used with arithmetic to display future or past dates.

Excel's date and time functions build dates from year, month, day and times from hour, minute, second, with serial-number handling and adjustments for months beyond twelve or days zero.

Learn to use day, month, and year functions in Excel to extract date components and build dynamic dates with the date function for dashboards.

Learn to calculate the number of days between two dates in Excel using the days function or by subtracting end date minus start date, with text dates recognized by Excel.

Learn how to extract time components with second, minute, and hour functions in Excel, and build dynamic time values using the time function with hour, minute, and second parameters.

Explore weekday and weeknum functions in Excel, using return types to start weeks on Sunday, Monday, or ISO Thursday-based weeks, and learn handling blank dates and optional return types.

Master time and date functions in Excel, including date, time, day, month, year, hours, minutes, and seconds, with practical tips you can apply at work to impress your boss.

Celebrate completing the course as you master statistical functions, if statements, text functions, and time/date functions in Excel, empowering you to advance your career and impress your boss.