Excel Functions & Formulas Guide (500+ Examples & Templates)

Learn Excel faster with 500+ functions, formulas, and real examples. Master XLOOKUP, VLOOKUP, IF, SUMIF, and download free Excel templates.

Popular Excel functions

  • XLOOKUP: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • VLOOKUP: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
  • SUM: Adds all the numbers in a range of cells.
  • IF: Returns one value if a condition is TRUE and another value if it's FALSE.
  • SUMIF: Adds the cells specified by a given condition or criteria.
  • INDEX: Returns the value of an element in a table or array, selected by the row and column number indexes.
  • MATCH: Returns the relative position of an item in an array that matches a specified value.
  • COUNTIF: Counts the number of cells that meet a criterion.
  • AVERAGE: Returns the average (arithmetic mean) of the arguments.
  • CONCAT: Joins two or more text strings into one string.
  • LEFT: Returns the specified number of characters from the start of a text string.
  • PMT: Calculates the payment for a loan based on constant payments and a constant interest rate.
  • FV: Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
  • TODAY: Returns the current date. Updates automatically when the worksheet recalculates.
  • DATEDIF: Calculates the number of days, months, or years between two dates.
  • CONVERT: Converts a number from one measurement system to another.
  • DSUM: Adds the numbers in a field of records in a database that match criteria.
  • HLOOKUP: Searches for a value in the top row of a table and returns a value in the same column from a row you specify.
  • XMATCH: Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.
  • INDIRECT: Returns the reference specified by a text string. References are immediately evaluated to display their contents.
  • OFFSET: Returns a reference offset from a starting point by a specified number of rows and columns.
  • CHOOSE: Returns a value from a list of values based on an index number.
  • IFS: Checks multiple conditions and returns a value corresponding to the first TRUE condition.
  • SWITCH: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.

Formula categories

  • Lookup & Reference: Find and retrieve data from tables and ranges
  • Logical: Make decisions and test conditions
  • Math & Trig: Perform mathematical calculations
  • Statistical: Analyze data with statistical functions
  • Text: Manipulate and format text strings
  • Financial: Calculate loans, investments, and depreciation
  • Date & Time: Work with dates, times, and durations
  • Engineering: Complex numbers and engineering conversions
  • Information: Get information about cells, values, and the environment
  • Web: Retrieve data from web services and URLs
  • Database: Query data that matches criteria
  • Cube: Retrieve data from OLAP cubes and Power Pivot data models

Excel error help

  • #N/A: This error occurs when a value is not available to a function or formula. It's most common in lookup functions when the search value cannot be found.
  • #VALUE!: This error occurs when a formula has the wrong type of argument or operand. It typically indicates a mismatch between expected and provided data types.
  • #REF!: This error occurs when a formula refers to a cell that is not valid. Often happens when cells are deleted that were referenced by formulas.
  • #DIV/0!: This error occurs when a formula tries to divide a number by zero or an empty cell.
  • #NAME?: This error occurs when Excel doesn't recognize text in a formula. Usually caused by misspelled function names or missing quotation marks.