Excel Standard functions file

Standard operators (e.g. + - / *) plus basic functions (e.g. SUM, MIN, MAX, AVERAGE) plus other generic functions cover most MS Excel user needs. The reference file contains tens of working examples of those other generic functions and more advanced random number generation.

Download MS Excel function reference file (361kB - downloaded 4464 times)

For more advanced functions, see Various User Defined Functions (VBA) and Various Lambda functions, and menu Apps.
Programming with VBA you can develop any functionality you need. See VBA course.

Contents

Example 1: String functions - LEFT, MID, RIGHT, CONCATENATE / &, TRIM, SUBSTITUTE, FIND, LEN

Example 2: AND, OR, IF

Example 3: Handling errors using IFERROR and ISERROR

Example 4: Table look up - MATCH and INDEX (or OFFSET)

Example 4B: XMATCH (newer, better alternative for MATCH)

Example 5: Referring to different tables - INDIRECT

Example 6: Multiple criteria SUMIF/COUNTIF and SUMIFS/COUNTIFS/AVERAGEIFS

Example 7: Dynamic ranges - OFFSET revisited

Example 8: Calculating while filtering rows / calculating with visible cells only - multifunctional SUBTOTAL

Example 9: Dynamic sorting or finding nth largest or nth smallest value - LARGE and SMALL. Office365/Excel 2021: SORT.

Example 10: Avoiding additional columns - SUMPRODUCT

Array formula intermezzo *

Example 11-A: Connecting a second table with multiple columns to a first table**: MATCH versus VLOOKUP (and HLOOKUP)

Example 11-B: fast VLOOKUP

Example 11-C: XLOOKUP

Example 12: Creating a histogram using COUNTIF ***

Example 13: Transposing data - TRANSPOSE versus OFFSET in combination with COLUMN

Example 14: Random number generation - RAND() and several distribution types → useful for Monte Carlo simulation

Excel Standard functions file

Contents

Sneak preview