Excel functions explained
What each function does, how to write it, worked examples on small tables, and the mistakes everyone makes at first. Then practice it in a real spreadsheet.
Guides
Sums
Averages, minimum and maximum
Counting
- COUNTCOUNT counts cells that contain numbers; COUNTA counts every non-empty cell; COUNTBLANK counts empty ones.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
Rounding
Logic and errors
- IFIF checks a condition and returns one value if it's true and another if it's false.
- ANDAND returns TRUE only when every condition is true.
- OROR returns TRUE when at least one condition is true.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.
Lookup
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- INDEXINDEX returns the value at a given row and column of a range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.