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.