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 3806 times)

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 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

* File includes array formula examples. However, try to avoid using array formulas.
** Nowadays this can be done by 'underlying' Excel PowerPivot.
*** MS Excel 2016 offers the histogram as a new standard chart.

Excel Standard functions file

Contents

Sneak preview