08/02/2025

List of Excel Functions

Exhaustive list of Excel functions tailored for Office 365/2024/2021* focusing on modern, high-value functions and excluding outdated/legacy ones (e.g., RANK replaced by RANK.EQ, VLOOKUP flagged as legacy). Newer dynamic array functions (Excel 365+) are emphasized:

1. Math & Trigonometry

  • SUM ➔ Sum values.
  • SUMIF(S) ➔ Sum with single/multiple conditions.
  • SUMPRODUCT ➔ Multiply arrays and sum.
  • LET ➔ Assign variables in formulas (improves readability).
  • LAMBDA ➔ Create custom functions.
  • SEQUENCE ➔ Generate dynamic number sequences.
  • RANDARRAY ➔ Array of random numbers.
  • FLOOR.MATH/CEILING.MATH ➔ Modern rounding with flexibility.
  • AGGREGATE ➔ Apply functions with error-ignoring options.

2. Statistical

  • AVERAGEIF(S) ➔ Average with conditions.
  • COUNTIF(S) ➔ Count with conditions.
  • MAXIFS/MINIFS ➔ Max/min with criteria.
  • UNIQUE ➔ Extract distinct values (dynamic array).
  • FILTER ➔ Filter data dynamically.
  • SORT/SORTBY ➔ Sort ranges dynamically.
  • XLOOKUP ➔ Modern replacement for V/HLOOKUP.
  • PERCENTILE.INC/PERCENTILE.EXC ➔ Inclusive/exclusive percentiles.
  • STDEV.P/STDEV.S ➔ Population/sample standard deviation.
  • FORECAST.ETS ➔ Time-series forecasting.

3. Lookup & Reference

  • XLOOKUP ➔ Flexible lookup (replaces V/HLOOKUP).
  • FILTER ➔ Return filtered data based on criteria.
  • INDEX/MATCH ➔ Dynamic row/column lookups.
  • CHOOSECOLS/CHOOSEROWS ➔ Select specific columns/rows (Excel 365+).
  • TOCOL/TOROW ➔ Convert ranges to single columns/rows.
  • VSTACK/HSTACK ➔ Stack arrays vertically/horizontally.
  • DROP ➔ Remove rows/columns from a range.
  • TAKE ➔ Extract first/last N rows/columns.
  • WRAPROWS/WRAPCOLS ➔ Wrap arrays into rows/columns.

4. Text

  • TEXTJOIN ➔ Join text with delimiter (replaces CONCATENATE).
  • TEXTSPLIT ➔ Split text into rows/columns.
  • TEXTBEFORE/TEXTAFTER ➔ Extract text relative to a delimiter.
  • VALUE ➔ Convert text to number.
  • UNICODE ➔ Unicode code of a character.
  • ARABIC/ROMAN ➔ Convert Roman numerals to Arabic and vice versa.
  • ARRAYTOTEXT ➔ Convert arrays to text strings.

5. Date & Time

  • TODAY/NOW ➔ Current date/time.
  • EDATE/EOMONTH ➔ Add months or get month-end.
  • WORKDAY.INTL ➔ Custom workday calculations (include holidays).
  • SEQUENCE ➔ Generate dynamic date ranges.
  • DATEDIF ➔ Date difference (use cautiously; no official docs).

6. Logical

  • IFS ➔ Multiple nested IFs simplified.
  • SWITCH ➔ Compare a value against a list.
  • AND/OR/XOR ➔ Logical operators.
  • IFERROR/IFNA ➔ Handle errors gracefully.
  • LET ➔ Simplify complex formulas with variables.

7. Financial

  • XIRR/XNPV ➔ Irregular cash flow analysis.
  • PMT/RATE/NPER ➔ Loan payment calculations.
  • FV/PV ➔ Future/present value.
  • PDURATION ➔ Periods to reach investment goal.
  • RRI ➔ Equivalent interest rate for growth.

8. Engineering

  • CONVERT ➔ Unit conversions (e.g., miles to km).
  • BITAND/BITOR/BITXOR ➔ Bitwise operations.
  • DEC2BIN/HEX2DEC ➔ Base conversions.

9. Dynamic Arrays (Excel 365+)

  • FILTER ➔ Filter data with criteria.
  • SORT/SORTBY ➔ Sort ranges.
  • UNIQUE ➔ Extract unique values.
  • SEQUENCE ➔ Generate number sequences.
  • RANDARRAY ➔ Array of random numbers.
  • XMATCH ➔ Advanced MATCH with search modes.
  • BYROW/BYCOL ➔ Apply functions row/column-wise.
  • MAKEARRAY ➔ Create custom arrays.
  • SCAN/REDUCE ➔ Array scanning/accumulation.

10. Database & Cube

  • CUBEVALUE ➔ Retrieve data from Power Pivot.
  • CUBEMEMBER ➔ Fetch hierarchy members.
  • GROUPBY ➔ Group and aggregate data (Excel 365+).
  • PIVOTBY ➔ Create pivot-like summaries (Excel 365+).

11. Web & Custom

  • WEBSERVICE ➔ Fetch data from APIs (Excel 2013+).
  • ENCODEURL ➔ URL-encode strings.
  • LAMBDA ➔ Create reusable custom functions.

12. New in Excel 365/2024

  • PERCENTOF ➔ Calculate percentage of total.
  • ARRAYTOTEXT ➔ Convert arrays to readable text.
  • REGEX functions (if available in your build) ➔ Pattern-based text extraction.
  • PY ➔ Integrate Python directly (rolling out in 2024).

Legacy Functions to Avoid

  • VLOOKUP/HLOOKUP ➔ Use XLOOKUP instead.
  • RANK ➔ Use RANK.EQ/RANK.AVG.
  • CONCATENATE ➔ Use CONCAT or TEXTJOIN.
  • ISPMT ➔ Outdated financial function.

This list prioritizes modern, high-impact functions and excludes rarely used legacy tools (e.g., DB, DGET). For spaced repetition, focus on dynamic arrays (FILTER, SORT, UNIQUE) and XLOOKUP first.

Manoj Arora