Here is the compiled list categorized by analytical purpose:
1. Database Functions (D-Functions)
Specifically designed for statistical analysis of structured tables/databases .
- DCOUNT: Counts numeric cells matching criteria
- DCOUNTA: Counts nonblank cells matching criteria
- DAVERAGE: Averages matching records
- DSUM: Sums matching records
- DMAX: Returns maximum from matching records
- DMIN: Returns minimum from matching records
- DPRODUCT: Multiplies values in matching records
- DSTDEV: Sample standard deviation of matching records
- DSTDEVP: Population standard deviation of matching records
- DVAR: Sample variance of matching records
- DVARP: Population variance of matching records
- DGET: Extracts a single record
2. Lookup & Reference Functions
Essential for scenario analysis and dynamic data retrieval .
- VLOOKUP / HLOOKUP: Vertical/horizontal exact or approximate lookup
- XLOOKUP: Modern successor to V/HLOOKUP (returns any column/row)
- INDEX: Returns value at intersection of row and column
- MATCH: Returns position of a lookup value
- XMATCH: Updated version of MATCH with more match modes
- FILTER: Returns all matching values from an array (dynamic array)
- CHOOSE: Returns value from list based on index number
- OFFSET: Returns range offset from reference (used for rolling calculations)
- INDIRECT: Returns reference specified by text string
- HYPERLINK: Creates shortcut/jump
3. Statistical & Aggregation Functions
Core descriptive statistics and conditional math .
- SUM / SUMIF / SUMIFS: Sum with zero, one, or multiple conditions
- COUNT / COUNTIF / COUNTIFS: Count numeric cells with conditions
- AVERAGE / AVERAGEIF / AVERAGEIFS: Arithmetic mean with conditions
- SUBTOTAL: Returns subtotal (SUM, AVERAGE, COUNT, etc.) ignoring filtered rows
- MAX / MIN: Largest/smallest value
- MEDIAN: Middle value
- MODE: Most frequent value
- STDEV.S / STDEV.P: Standard deviation (sample vs population)
- VAR.S / VAR.P: Variance (sample vs population)
- PRODUCT: Multiplies values in a range
- ABS: Absolute value
4. Logical Functions
Conditional branching and error handling .
- IF: Returns one value if true, another if false
- AND / OR / NOT: Boolean logic (returns TRUE/FALSE)
- IFERROR / IFNA: Catches errors and returns specified alternative
- SWITCH: Evaluates expression against list of values (alternative to nested IF)
5. Text & String Manipulation Functions
Data cleaning and dynamic label generation .
- CONCATENATE / CONCAT: Joins strings
- TEXTJOIN: Joins strings with delimiter (ignores blanks)
- LEFT / RIGHT / MID: Extracts characters from start/end/middle
- FIND: Locates substring position (case-sensitive)
- REPLACE: Replaces characters
- TEXT: Formats number as text in specific format
- & (Ampersand): Operator for string concatenation
- ENCODEURL: URL-encodes text (Web functions)
6. Date & Time Functions
Time intelligence and date arithmetic .
- TODAY / NOW: Current date / current date-time
- DATE / TIME: Creates date/time from components
- YEAR / MONTH / DAY: Extracts components
- HOUR / MINUTE / SECOND: Extracts time components
- DATEDIF: Calculates difference in days/months/years
- DAYS / DAYS360: Day count (actual vs 360-day year)
- WEEKDAY / WEEKNUM / ISOWEEKNUM: Day of week / week number
- EDATE / EOMONTH: Date n months away / end of month
- NETWORKDAYS: Working days between dates
- DATEVALUE / TIMEVALUE: Converts text date/time to serial number
7. Financial Functions
Loan calculations, depreciation, and security pricing .
- PMT: Loan payment amount
- NPV / IRR: Net Present Value / Internal Rate of Return
- FV: Future value of investment
- ACCRINT / ACCRINTM: Accrued interest (periodic/at maturity)
- CUMIPMT / CUMPRINC: Cumulative interest/principal
- DB / DDB: Declining balance / Double-declining balance depreciation
- DISC: Discount rate for security
- DOLLARDE: Converts fractional dollar price to decimal
8. Information Functions
Cell property and data type verification .
- ISNUMBER: Checks if value is number
- ISNA: Checks if error is #N/A
- TYPE: Indicates data type
9. What-If Analysis & Validation Tools
Sensitivity and scenario structuring .
- Data Table (Feature, not function): Varies inputs to see impact on PMT/NPV/IRR
- Data Validation (Feature, not function): Restricts input types (drop-downs, ranges)
If you require a full alphabetical list or a complete export from Microsoft’s official library, please clarify that you need the exhaustive inventory beyond these curated categories.