Excel function guides
Excel functions, explained and practised.
Plain-English guides to the 20 Excel functions people look up most. Each one has the syntax, worked examples, the mistakes that trip people up, and a live spreadsheet to try it in. No account needed.
Lookup
Pull matching information from another table.
XLOOKUP
Find a value in one column and return the match from another.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
VLOOKUP
Match a value in a table's first column and return another column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
INDEX and MATCH
The lookup that works in every Excel version and can look left.
=INDEX(array, row_num, [column_num])
Conditional sums & counts
Totals, counts and averages for the rows that meet your conditions.
SUMIFS
Add up the rows that meet every condition.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
SUMIF
Add up the values that meet one condition.
=SUMIF(range, criteria, [sum_range])
COUNTIFS
Count the rows that meet several conditions.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
COUNTIF
Count the cells that meet one condition.
=COUNTIF(range, criteria)
AVERAGEIFS
Average the rows that meet every condition.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Logic
Decisions, rules and error handling.
IF
Return one result if a test is true, another if it's false.
=IF(logical_test, value_if_true, [value_if_false])
IFS
Check conditions in order and return the first match.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)
IFERROR
Show your own value instead of an error.
=IFERROR(value, value_if_error)
AND and OR
Combine several tests into one TRUE or FALSE.
=AND(logical1, [logical2], …)
Text
Clean, split and join text.
CONCAT and TEXTJOIN
Join text from several cells, with or without a delimiter.
=CONCAT(text1, [text2], …)
LEFT, RIGHT and MID
Take characters from the start, end or middle of text.
=LEFT(text, [num_chars])
TRIM
Remove leading, trailing and repeated spaces.
=TRIM(text)
SUBSTITUTE
Replace specific text inside a value.
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Dates
Month ends, deadlines and working days.
Math & rounding
Rounding and multiply-and-add totals.
From one function to real workbooks
Every guide links to the Excel Quest lesson that teaches it on a realistic dataset, with instant feedback on each formula. See how the lessons fit together in the curriculum, or test yourself with the Excel interview questions.