■ Excel Functions – Cheat Sheet (Text Wrapped)
[Link] Function Formula Syntax One-Line Explanation Explanation of Components
1 MID =MID(text, start_num, Extracts characters from the text=source string, start_num=start position,
num_chars) middle of text. num_chars=no. of chars
2 LEFT =LEFT(text, num_chars) Extracts characters from the text=source string, num_chars=no. of chars
left side.
3 RIGHT =RIGHT(text, num_chars) Extracts characters from the text=source string, num_chars=no. of chars
right side.
4 LEN =LEN(text) Counts characters in text. text=string to measure
5 SEARCH =SEARCH(find_text, Finds position of substring find_text=what to find, within_text=where,
within_text, [start_num]) (case-insensitive). start_num=optional starting pos
6 FIND =FIND(find_text, within_text, Finds position of substring same as SEARCH but case-sensitive
[start_num]) (case-sensitive).
7 REPLACE =REPLACE(old_text, Replaces part of text with new old_text=original, start_num=start pos,
start_num, num_chars, text. num_chars=no. of chars, new_text=replacement
new_text)
8 & ="Text1" & "Text2" Joins text strings. Concatenates values, spaces must be added
manually
9 IF =IF(logical_test, value_if_true, Returns value if TRUE, else logical_test=condition, value_if_true=if true,
value_if_false) another. value_if_false=if false
10 IFERROR =IFERROR(value, Returns alternate value if error value=formula, value_if_error=fallback
value_if_error) occurs.
11 SUM =SUM(number1, [number2], Adds numbers. number1, number2…=numbers, ranges, references
…)
12 SUMIF =SUMIF(range, criteria, Adds numbers that meet range=check range, criteria=condition,
[sum_range]) condition. sum_range=numbers to sum
[Link] Function Formula Syntax One-Line Explanation Explanation of Components
13 SUMIFS =SUMIFS(sum_range, Adds numbers that meet sum_range=values, criteria_range(s)=ranges,
criteria_range1, criteria1, …) multiple conditions. criteria(s)=conditions
14 SUMPRODUCT =SUMPRODUCT(array1, Multiplies arrays and sums array1, array2…=numeric arrays/ranges
[array2], …) results.
15 AVERAGEIF =AVERAGEIF(range, criteria, Averages values that meet range=criteria range, criteria=condition,
[average_range]) condition. average_range=numbers to average
16 COUNT =COUNT(value1, [value2], …) Counts numeric values. value1, value2…=numbers/ranges
17 COUNTIF =COUNTIF(range, criteria) Counts cells meeting one range=check area, criteria=condition
condition.
18 COUNTIFS =COUNTIFS(criteria_range1, Counts cells meeting multiple pairs of criteria_range + criteria
criteria1, …) conditions.
19 MAX =MAX(number1, [number2], Returns largest number. number1, number2…=values/ranges
…)
20 SEQUENCE =SEQUENCE(rows, [columns], Creates array of sequential rows=no. of rows, columns, start, step
[start], [step]) numbers.
21 XLOOKUP =XLOOKUP(lookup_value, Flexible lookup returning lookup_value=what to find, lookup_array=where,
lookup_array, return_array, matches. return_array=what to return, others optional
[if_not_found], [match_mode],
[search_mode])
22 VLOOKUP =VLOOKUP(lookup_value, Vertical lookup in first column. lookup_value=search item, table_array=table,
table_array, col_index_num, col_index_num=column no.,
[range_lookup]) range_lookup=TRUE/FALSE
23 HLOOKUP =HLOOKUP(lookup_value, Horizontal lookup in first row. lookup_value=search item, table_array=table,
table_array, row_index_num, row_index_num=row no.,
[range_lookup]) range_lookup=TRUE/FALSE
24 MATCH =MATCH(lookup_value, Returns position of item in lookup_value=search item, lookup_array=range,
lookup_array, [match_type]) range. match_type=exact/approx
[Link] Function Formula Syntax One-Line Explanation Explanation of Components
25 INDEX =INDEX(array, row_num, Returns value at a given row & array=range, row_num=row pos,
[column_num]) column. column_num=optional col
26 INDIRECT =INDIRECT(ref_text, [a1]) Turns text into a reference. ref_text=cell reference in text, a1=TRUE(A1
style)/FALSE(R1C1)
27 OFFSET =OFFSET(reference, rows, Returns range offset from reference=start point, rows, cols=shift,
cols, [height], [width]) reference. height/width=size
28 DAVERAGE =DAVERAGE(database, field, Averages column values database=table, field=column, criteria=condition
criteria) matching condition. table
29 DSUM =DSUM(database, field, Sums column values matching database=table, field=column, criteria=condition
criteria) condition. table
30 RATE =RATE(nper, pmt, pv, [fv], Returns interest rate per nper=periods, pmt=payment, pv=present value,
[type], [guess]) period. fv=future value, type=timing, guess=estimate
31 PMT =PMT(rate, nper, pv, [fv], Calculates periodic payment. rate=interest, nper=periods, pv=present value, fv,
[type]) type
32 CUMIPMT =CUMIPMT(rate, nper, pv, Returns cumulative interest rate, nper, pv, start_period, end_period, type
start_period, end_period, type) paid.
33 CUMPRINC =CUMPRINC(rate, nper, pv, Returns cumulative principal rate, nper, pv, start_period, end_period, type
start_period, end_period, type) paid.
34 NPER =NPER(rate, pmt, pv, [fv], Returns number of periods. rate, pmt, pv, fv, type
[type])
35 FV =FV(rate, nper, pmt, [pv], Returns future value of rate, nper, pmt, pv, type
[type]) investment.
36 PPMT =PPMT(rate, per, nper, pv, [fv], Returns principal part of rate=interest, per=period, nper, pv, fv, type
[type]) payment.
37 TODAY =TODAY() Returns current date. No arguments
38 EDATE =EDATE(start_date, months) Returns date after start_date=base date, months=months to move
adding/subtracting months.
[Link] Function Formula Syntax One-Line Explanation Explanation of Components
39 UNIQUE =UNIQUE(array, [by_col], Returns unique values from a array=input, by_col=TRUE(columns),
[exactly_once]) range. exactly_once=only once