MS Excel - Functions reference fileStandard operators (e.g. + - / *) plus basic functions (e.g. SUM, MIN, MAX, AVERAGE) plus other generic functions cover most MS Excel user needs. Below, you can download a handy reference file with examples for those other generic functions.
Download MS Excel function reference file (361kB - downloaded 927 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 - multifunctional SUBTOTAL
- Example 9: Dynamic sorting or finding nth largest or nth smallest value - LARGE and SMALL
- Example 10: Avoiding additional columns - SUMPRODUCT
- Array formula intermezzo *
- Example 11: Connecting a second table with multiple columns to a first table**: MATCH versus VLOOKUP (and HLOOKUP)
- Example 11B: fast VLOOKUP
- 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.