Microsoft Excel Formulas and Functions For Basic to Expert

Master Microsoft Excel formulas and functions from beginner basics to expert tactics, learning calculations from sums to statical analysis, automating tasks with lookup and reference functions, and troubleshooting errors.

Explore the Excel interface, including the ribbon, quick access toolbar, formula bar, worksheet area, and sheet tabs. Learn to navigate cells and use essential shortcuts.

Learn how relative references in Excel adjust when you copy formulas, and how absolute references locked with dollar signs keep values constant, with mixed references for partial locking.

Learn to detect and handle Excel errors with iferror, iserror, and error types, and apply not found and Vlookup error handling for clean, reliable data.

Learn how to use the concatenate function to join text from multiple cells, create full names and addresses, and format data with spaces, commas, and units.

Explore Excel’s workday and workday.int functions to calculate dates by adding or subtracting working days, accounting for weekends and optional holidays, with practical project deadlines and schedule planning.

Learn how to use excel logical operators if, and, or, not to make data-driven decisions with conditional evaluations; apply multi-criteria rules for pass/fail using scores, attendance, and extra credit.

Master powerful Excel calculations with sum, sumif, sumifs, and sumproduct functions to analyze data, filter by region north, multiply quantity by price, and compute total sales.

Explore Excel’s count functions to quickly tally data: count counts numbers, countA counts non-empty cells, countBlank finds empty cells, and countIf and countIfs apply single or multiple criteria.

Explore Microsoft Excel average functions—average, averagea, averageif, and averageifs—and learn to compute simple and conditional averages, handle text as zero, and apply multiple criteria to analyze data.

Use the min and max functions in Excel to find the smallest and largest values. Pair them with the if function to test if values equal the min or max.

Discover how to use small and large functions to find the nth smallest or largest values in a range, rank data without sorting, and combine with if for conditional results.

Explore four essential Excel functions: row, rows, column, and columns that identify and count data to navigate and manipulate spreadsheets.

Learn to create dates by combining year, month, and day with the date function, and to measure differences between dates using the datedif function in days, months, or years.

Explore how to use EDATE and EOMONTH to add or subtract months from a start date and to get the last day of a month, with deadlines and renewals.

Master excel formulas with choose and switch to handle index-based selections and complex conditions. Learn how to compare expressions, return specific values, and nest functions for flexible, efficient spreadsheets.

Explore left, mid, right, and length functions to extract and analyze text in Excel, using examples like product IDs, domains, and file extensions.

Standardize and clean text data in Excel using upper, lower, proper, and trim to keep spreadsheets neat, then nest functions for more complex data cleaning.

Explore year, month, day, hour, minute, and second functions to extract date and time components from timestamps, enabling time-based filtering, sorting, and custom labels in Excel.

Explore how to sort data quickly with Excel's sort and sortby functions, using single or multiple criteria, including by columns outside the data, to analyze sales more efficiently.

Explore the filter and unique dynamic array functions in Excel to extract data, show distinct values, and combine filter with unique for targeted data analysis.

Explore Excel's sequence function to generate dynamic row and column numbers with customizable start, stop, and step values, and apply it to dates, labels, or sums.

Explore how the frequency function counts values in a data set that fall within defined bins, using array formulas to reveal distributions and enable quick histogram creation.

Use the transpose function to rotate data between rows and columns with dynamic updates for analysis and presentations. Apply the array formula or paste-special transpose for flexible, linked results.

Discover how index, match, and XMATCH enable flexible lookups in Excel, returning values from ranges, locating positions, and combining them for dynamic, column-agnostic data retrieval in real-world scenarios.

Use the match function with a lookup value and lookup array to find the closest match in ascending order, then use index and the exact function to verify case-sensitive results.

Learn the Vlookup function to search vertically in the leftmost column and return data from a specified column. Understand its four components, including range lookup and exact versus approximate matches.

Master the HLOOKUP function to perform horizontal lookups across rows in Excel, returning values from a chosen row using the first-row value and showing exact or approximate matches.

Learn how to use Excel's Xlookup to replace Vlookup and Hlookup, search vertically or horizontally, and specify lookup value, lookup array, return array, and not found handling.

Combine vlookup with match to dynamically identify the price column, enabling flexible, exact lookups on large datasets using data validations and drop-down inputs.

Learn how to use VLOOKUP with approximate match to find the closest grade for a score, using a sorted lookup table and the true argument.

Master creating dropdown lists with data validation and applying filters in Excel to control data entry and quickly analyze large datasets.

Create a dependent dropdown list in Excel that updates options via data validation and named ranges, using an indirect formula to link fruits and vegetables.

Master the most common Excel shortcut keys to boost productivity by navigating workbooks faster, editing cells efficiently, formatting text, applying filters, and switching between worksheets.