Function guides

The 60 functions that come up most in employer Excel tests. Each guide has the syntax, worked examples on a small grid, the mistakes that cost points, and what a test is likely to ask.

Math (10)
Statistical (14)
Logical (7)
Lookup (8)
Text (13)
Date (8)

More guides: what each Excel error means, how-to guides for menu tasks and 25 sample test questions with answers.

Math

Totals, rounding and the arithmetic helpers behind every budget and invoice.

Function	What it does
SUM	Adds numbers, cell references and ranges.
SUMIF	Adds the values in a range that meet one condition.
SUMIFS	Adds the values in a range that meet every one of several conditions.
ROUND	Rounds a number to a set number of decimal places.
ROUNDUP	Rounds a number up, away from zero, to a set number of digits.
ROUNDDOWN	Rounds a number toward zero to a set number of digits.
ABS	Returns the absolute value of a number, its distance from zero.
INT	Rounds a number down to the nearest integer.
MOD	Returns the remainder after one number is divided by another.
SUMPRODUCT	Multiplies matching items in ranges and adds the products.

Statistical

Counting, averaging, ranking and the conditional versions of each.

Function	What it does
AVERAGE	Returns the arithmetic mean of numbers.
MAX	Returns the largest number in a set. MIN returns the smallest.
COUNT	Counts the cells that contain numbers.
COUNTA	Counts the cells that are not empty.
COUNTIF	Counts the cells in a range that meet one condition.
COUNTIFS	Counts the rows that meet every one of several conditions.
MIN	Returns the smallest number in a set.
COUNTBLANK	Counts the empty cells in a range.
AVERAGEIF	Averages the values in a range that meet one condition.
AVERAGEIFS	Averages the values in a range that meet every one of several conditions.
LARGE	Returns the k-th largest value in a range.
SMALL	Returns the k-th smallest value in a range.
RANK.EQ	Returns the rank of a number within a list, with ties sharing the same rank.
MEDIAN	Returns the middle value of a set of numbers.

Logical

Decisions: IF and its relatives, plus error handling.

Function	What it does
IF	Returns one value if a condition is true and another if it is false.
AND	Returns TRUE only if every condition is true.
IFS	Checks conditions in order and returns the value for the first one that is true.
IFERROR	Returns a value you choose when a formula results in an error.
IFNA	Returns a value you choose when a formula results in
NOT	Reverses TRUE to FALSE and FALSE to TRUE.
OR	Returns TRUE if any one of the conditions is true.

Lookup

Finding a value in a table and returning something from the same row.

Function	What it does
VLOOKUP	Finds a value in the first column of a table and returns a value from another column in the same row.
XLOOKUP	Finds a value in one range and returns the matching item from another range.
INDEX	Returns the value at a given row and column position in a range.
MATCH	Returns the position of a value within a row or column.
HLOOKUP	Finds a value in the top row of a table and returns a value from a row below it.
FILTER	Returns the rows of a range that meet a condition, spilling the results into neighboring cells.
SORT	Returns a sorted copy of a range, spilling the results into neighboring cells.
UNIQUE	Returns the distinct values in a range, removing duplicates.

Text

Cleaning, splitting, joining and reformatting text.

Function	What it does
TRIM	Removes extra spaces from text, leaving single spaces between words.
LEFT	Returns a number of characters from the start of text.
CONCAT	Joins text from several cells or ranges into one string.
TEXTJOIN	Joins text from a range with a delimiter between each item, optionally skipping blanks.
MID	Returns characters from the middle of text, starting at a position you choose.
RIGHT	Returns a number of characters from the end of text.
LEN	Returns the number of characters in text.
FIND	Returns the position of one text string inside another, matching case exactly.
SEARCH	Returns the position of one text string inside another, ignoring case and allowing wildcards.
SUBSTITUTE	Replaces specific text inside a string with new text.
UPPER	Converts text to uppercase.
TEXT	Converts a number or date to text in a number format you specify.
VALUE	Converts text that looks like a number into a real number.

Date

Dates, deadlines, working days and the gaps between them.

Function	What it does
TODAY	Returns the current date, updated every time the workbook recalculates.
DATE	Builds a date from separate year, month and day numbers.
YEAR	Returns the year of a date as a four-digit number.
MONTH	Returns the month of a date as a number from 1 to 12.
EOMONTH	Returns the last day of the month, a given number of months before or after a date.
EDATE	Returns the date that is a given number of months before or after a start date.
NETWORKDAYS	Counts the working days between two dates, excluding weekends and optionally holidays.
DATEDIF	Returns the number of complete years, months or days between two dates.