Microsoft Excel Formulas and Functions: Beginner to Advanced

Master the syntax of Excel functions by learning how function names, parentheses, and comma-separated arguments produce calculations such as sum, average, and the if function logic.

Explore relative and absolute cell references in Excel to create dynamic formulas. Lock cells with the dollar sign to keep references intact as you drag formulas.

Identify and fix the most common Excel errors—division by zero, hash value and reference errors, name errors, and not available errors—using practical examples and simple fixes.

Discover how to show formulas, evaluate complex calculations, and use error checking to identify and fix issues in Excel formulas, improving accuracy and debugging efficiency.

Boost productivity by mastering essential Excel shortcut keys for open, save, copy, paste, undo, and format cells with Ctrl 1, and create tables with Ctrl T.

Learn Excel function shortcuts that save time and boost productivity for all levels, from creating new worksheets to accessing help, print preview, spell check, and insert function dialog boxes.

Master the and and or functions in Excel to evaluate multiple criteria, including age and experience with and, or for masters degree or five years of experience.

Explore how the not function flips logical values and how to use the iferror function to handle errors in Excel formulas, with practical data-set examples and debugging tips.

Explore how to use Excel IF functions to perform logical tests on real-world data, assign pass or fail based on thresholds, and analyze grades with practical examples.

Learn to use Excel's small and large functions to find the kth smallest or largest values in a data set, with practical syntax, ranking, and error handling.

Explore rank, percentile rank, and percentile functions in Excel to analyze data and identify relative standings and percentiles in datasets.

Explore how rand and randbetween generate random decimals and integers, apply them to real-world data like marketing simulations and daily website traffic, and learn steps to paste results as values.

Master conditional aggregations in excel by using countifs, sumifs, and averageifs on a real sales dataset to analyze product, date, and price criteria.

Build a dynamic Excel dashboard that uses countifs and sumifs to analyze revenue by product and date, with interactive filters via data validation and charts.

Explore the differences between countif and sumproduct, using countif to count sales above 10,000 and sumproduct to total sales with multiple criteria like names starting with J.

Define named ranges to label specific cell blocks, enabling readable formulas, fewer errors, and quicker navigation of datasets in Excel or Google Sheets.

Learn to count rows and columns in Excel using the count function, navigate the Excel interface, and determine total row and column numbers for a given range.

Explore exact function in Excel to compare text strings for exact matches, return true or false, and learn case sensitivity, dataset preparation, and handling missing values for data analysis.

Explore how the dynamic lookup function in Excel retrieves data from large datasets, using lookup values, vectors, and exact or approximate matches to return names, departments, and salaries.

Learn to use vlookup to return multiple matches in large data sets by combining vlookup with countif, row, match, and iferror to display sales data.

Master how to use iferror to handle errors and vlookup to retrieve data from tables, using exact match and range lookup to improve formula reliability in Excel.

Master Vlookup with exact match in Excel by setting range lookup to false, understanding the syntax and table array, and retrieving prices from a product list.

Learn to perform Vlookup with approximate match in Excel, covering exact vs approximate options, range lookup, and nearest values, with examples and tips for dynamic reports.

Combine Vlookup and match to perform exact and approximate lookups across large datasets. Learn to use table array, column index, and range lookup for flexible, two-way data retrieval.

Master vlookup with duplicates by creating a helper column and using the row function to fetch each occurrence, then clean up results with iferror.

Learn the Xlookup function in Excel to replace Vlookup and Hlookup, master its syntax and return array, and apply it to real data to retrieve product, category, price, and quantity.

Learn the Excel choose function to select a value from a list by index, with syntax and weekend and days of the week examples to improve your spreadsheet skills.

Master the offset function in Excel to dynamically reference cells or ranges, enabling dynamic reports and dashboards with practical syntax and real-world examples.

Learn to manipulate text in Excel with the upper, lower, proper, and trim functions, converting text to uppercase or lowercase, applying proper case, and removing extra spaces in real datasets.

Combine order numbers with dates in Excel using concatenate and text functions to create labeled reports. Use spaces or characters between text and date, and autofill to apply the pattern.

Explore Excel text functions left, right, mid, len, and find to extract and manipulate strings. Use practical examples like first names and last digits to build proficiency.

Enter text values in Excel and format them with fonts, bold, italics, colors, and borders to create readable layouts; build tables, merge cells, and apply auto fill and currency formats.

Explore Excel's find, search, substitute, and replace functions to locate text, understand case sensitivity, and perform text replacements for efficient data manipulation.

Learn how to combine the right, length, and search functions in Excel to extract last names from full names and domain names from emails through practical, step-by-step examples.

Learn how the substitute function in Excel replaces specific substrings with new text, selects which occurrence to replace, and aids data cleaning by substituting characters and spaces.

Explore how now, today, year, month, day, hour, minute, and second functions shape time in Excel, helping you plan, track, and time actions with precision.

Learn how to use the EOMONTH function to calculate the end or start of a month, adjust for previous or future months, and format dates for dynamic Excel spreadsheets.

Calculate ages in Excel using the erfc function with birth dates and today's date, leveraging start and end dates, today function, and number formatting to display whole ages.

Explore weekday, workday, and networkdays to manage dates in Excel. Compute day of week, end dates from working days, and working days between dates while excluding weekends and holidays.

Use the mod function with row numbers to identify odd rows, then apply conditional formatting to highlight alternating rows in Excel.

Use conditional formatting in excel to format cells based on another cell’s value, creating rules with formulas and visual cues like color changes.

Format cells with text functions and logical operators to apply conditional formatting that highlights salespersons whose names start with j and end with n, across the data table.

Explore how to use the sort and sort by functions in Excel to organize data by range, array, and custom orders, including ascending and descending options.

Master the Excel filter function, a dynamic array that extracts data from a table by criteria, with syntax, data validations, no results handling, and examples using product, region, and date.

Learn how the Excel unique function extracts unique values from a range or array, removes duplicates, and supports returning unique columns or rows.

Explore how to combine sort, unique, and count in Excel to quickly analyze and organize data, with real-life examples using zip codes and fruit lists.

Create dynamic drop-down lists in Excel using sort, unique, and no blanks to streamline data validation and improve user friendliness.

Discover how to build unique drop down lists that automatically update with new values in Excel, using the unique formula, data validation, and a table as the source.

Explore advanced conditional formatting in Excel, including custom rules with formulas, data bars, icon sets, and top-n highlighting, plus dynamic dropdown lists and unique data validation.

Master the sequence function in Excel or Google Sheets to generate numbers or dates, with configurable rows, columns, start, and step; explore serial numbers, dates, and optional transformations like transpose.

Learn to insert and manipulate excel dynamic arrays with vba, including using the sequence function, start, step, and range, and run it via the developer tab.

Explore the randarray function in Excel and Google Sheets to generate random numbers with configurable rows and columns, using a minimum and maximum range for decimals or integers.

Explore how the frequency function counts values within specified bins to build histograms and frequency distributions. Learn the data array and bin array inputs and see practical examples.

Learn how to use the transpose function to switch rows and columns, convert data with arrays and formulas, and paste special options for dynamic, cross-layout data.

Learn to create an interactive top N report in Excel using filter, large, sortindex, and sequence to display top sales data and automatically update as the N value changes.