BeginnerTotals and maths
SUM
Adds up numbers. Give it a range of cells and it totals every number in it.
=SUM(number1, [number2], ...)51 formulas
All formulas
BeginnerTotals and maths
AVERAGE
Finds the average (the mean) of the numbers in a range.
=AVERAGE(number1, [number2], ...)BeginnerTotals and maths
MIN
Returns the smallest number in a range.
=MIN(number1, [number2], ...)BeginnerTotals and maths
MAX
Returns the largest number in a range.
=MAX(number1, [number2], ...)BeginnerTotals and maths
ROUND
Rounds a number to the number of decimal places you choose.
=ROUND(number, num_digits)BeginnerTotals and maths
INT
Rounds a number down to the nearest whole number.
=INT(number)IntermediateTotals and maths
SUMPRODUCT
Multiplies matching cells in two or more ranges, then adds the results. Perfect for quantity times price.
=SUMPRODUCT(array1, [array2], ...)IntermediateTotals and maths
MEDIAN
Finds the middle value. Half the numbers are above it and half below, so a few huge values do not distort it.
=MEDIAN(number1, [number2], ...)IntermediateTotals and maths
LARGE
Returns the 1st, 2nd, 3rd... largest number in a range.
=LARGE(array, k)IntermediateTotals and maths
SMALL
Returns the 1st, 2nd, 3rd... smallest number in a range.
=SMALL(array, k)IntermediateTotals and maths
RANK
Tells you the position of a number in a list: 1 for the highest, 2 for the next, and so on.
=RANK(number, ref, [order])BeginnerCounting
COUNT
Counts how many cells contain numbers.
=COUNT(value1, [value2], ...)BeginnerCounting
COUNTA
Counts how many cells are not empty, whatever they contain.
=COUNTA(value1, [value2], ...)BeginnerCounting
COUNTIF
Counts the cells that meet one condition, such as equal to a word or bigger than a number.
=COUNTIF(range, criteria)IntermediateCounting
COUNTIFS
Counts the rows that meet several conditions at the same time.
=COUNTIFS(range1, criteria1, [range2, criteria2], ...)BeginnerCounting
COUNTBLANK
Counts the empty cells in a range.
=COUNTBLANK(range)BeginnerTotals and maths
SUMIF
Adds up the numbers in one range when another range meets a condition. For example, total quantity for Pen.
=SUMIF(range, criteria, [sum_range])IntermediateTotals and maths
SUMIFS
Adds up numbers that meet several conditions at once.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)IntermediateTotals and maths
AVERAGEIF
Averages the numbers that meet a condition.
=AVERAGEIF(range, criteria, [average_range])AdvancedTotals and maths
MAXIFS
Finds the largest number that meets one or more conditions.
=MAXIFS(max_range, criteria_range1, criteria1, ...)BeginnerLogic
IF
Shows one result when a test is true and another when it is false.
=IF(logical_test, value_if_true, [value_if_false])IntermediateLogic
IFS
Checks several conditions in order and returns the result for the first one that is true. A cleaner way than many IFs inside each other.
=IFS(test1, value1, [test2, value2], ...)BeginnerLogic
AND
Returns TRUE only when every condition you give it is true. Usually used inside IF.
=AND(logical1, [logical2], ...)BeginnerLogic
OR
Returns TRUE when at least one of the conditions is true.
=OR(logical1, [logical2], ...)IntermediateLogic
IFERROR
Shows a friendly result instead of an error such as #N/A or #DIV/0!.
=IFERROR(value, value_if_error)IntermediateLookup
VLOOKUP
Looks for a value in the first column of a table and returns a value from another column in the same row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])IntermediateLookup
XLOOKUP
A modern lookup: finds a value in one column and returns the matching value from another column, to the left or right.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])IntermediateLookup
MATCH
Tells you the position of a value in a list. For example, that "Mouse" is the third item.
=MATCH(lookup_value, lookup_array, [match_type])IntermediateLookup
INDEX
Returns the value at a given row (and column) of a range.
=INDEX(array, row_num, [column_num])BeginnerText
LEFT
Takes a number of characters from the start of some text.
=LEFT(text, [num_chars])BeginnerText
RIGHT
Takes a number of characters from the end of some text.
=RIGHT(text, [num_chars])IntermediateText
MID
Takes characters from the middle of some text, starting at a position you choose.
=MID(text, start_num, num_chars)BeginnerText
LEN
Counts the characters in some text, including spaces.
=LEN(text)BeginnerText
TRIM
Removes extra spaces from text: leading and trailing spaces, and doubled spaces between words.
=TRIM(text)BeginnerText
UPPER
Changes text to capital letters.
=UPPER(text)BeginnerText
LOWER
Changes text to small letters.
=LOWER(text)BeginnerText
PROPER
Capitalises the first letter of each word and makes the rest small.
=PROPER(text)IntermediateText
SUBSTITUTE
Replaces some text with other text inside a cell.
=SUBSTITUTE(text, old_text, new_text, [instance_num])IntermediateText
FIND
Finds where some text starts inside other text. It is case sensitive.
=FIND(find_text, within_text, [start_num])BeginnerText
CONCAT
Joins text from several cells into one. You can also use the & sign.
=CONCAT(text1, [text2], ...)IntermediateText
TEXTJOIN
Joins a whole range with a separator you choose, and can skip empty cells.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)IntermediateText
TEXT
Turns a number or date into text in the format you choose.
=TEXT(value, format_text)IntermediateDates
DATE
Builds a real date from a year, month and day.
=DATE(year, month, day)BeginnerDates
TODAY
Returns today’s date. It updates every time the workbook is opened.
=TODAY()IntermediateDates
DATEDIF
Counts the complete years, months or days between two dates.
=DATEDIF(start_date, end_date, unit)IntermediateDates
EDATE
Moves a date forward or back by whole months and keeps the day of the month where it can.
=EDATE(start_date, months)IntermediateDates
NETWORKDAYS
Counts the working days (Monday to Friday) between two dates, including both dates.
=NETWORKDAYS(start_date, end_date, [holidays])BeginnerDates
YEAR
Returns the year from a date.
=YEAR(serial_number)IntermediateFinance
PMT
Works out the regular payment on a loan with a fixed interest rate.
=PMT(rate, nper, pv, [fv], [type])BeginnerLogic
ISBLANK
Tells you whether a cell is empty: TRUE or FALSE.
=ISBLANK(value)IntermediateLogic
ISNUMBER
Tells you whether a value is a number: TRUE or FALSE. Often used to turn a MATCH result into yes or no.
=ISNUMBER(value)No formula matches.
Try a simpler word, such as “count”, “date” or “text”, or describe the problem on the Excel Solutions page.
Search by problemNot sure which formula you need?
Describe the job in your own words, such as “remove extra spaces” or “compare two lists”.