Function reference
Excel Functions Explained
Plain-English guides to the Excel functions people actually reach for, each with a worked table, copy-paste examples, the errors to expect, and a link straight into the exercise that drills it.
Lookup and reference
- VLOOKUPFinds a value in the first column of a table and returns something from a column to its right.
- XLOOKUPSearches any range and returns a value from any other, in either direction, with a built-in fallback.
- INDEX MATCHMATCH finds the position of a value; INDEX returns what is at that position. Together they look up anything, in any direction.
- HLOOKUPSearches the top row of a table and returns a value from a row beneath it.
Maths and conditional totals
- SUMIFAdds the numbers in a range, but only on the rows where a condition is met.
- SUMIFSAdds numbers only where every one of several conditions is true at once.
- COUNTIFCounts how many cells in a range meet a single condition.
- SUMAdds up every number in the ranges you give it, ignoring text and blanks.
- AVERAGEAdds the numbers in a range and divides by how many there were.
- ROUNDRounds a number to the number of decimal places you specify.
- COUNTIFSCounts rows where every one of several conditions is true at once.
- SUMPRODUCTMultiplies ranges together row by row, then adds up the results.
- MIN, MAXReturn the smallest and largest numbers in a range.
- COUNT, COUNTACOUNT counts numbers; COUNTA counts anything that is not empty.
- MODReturns what is left over after dividing one number by another.
- POWERRaises a number to a power — the function form of the ^ operator.
- CEILING, FLOORRound a number up or down to the nearest multiple of something you choose.
- ABSReturns a number with any minus sign removed.
- SIGNReturns 1 for a positive number, -1 for a negative one, and 0 for zero.
- QUOTIENTDivides two numbers and returns only the whole-number part.
- EVEN, ODDRound a number up to the next even or odd whole number.
- GCD, LCMGCD finds the largest number that divides them all; LCM finds the smallest that they all divide into.
- MDETERMReturns the determinant of a square array of numbers.
Statistics
- MEDIANReturns the middle value of a set of numbers when they are put in order.
- MODEReturns the number that appears most often in a range.
- STDEV, VARMeasure how far the values in a set typically sit from their average.
- RANKReturns the position of a number within a list, largest first by default.
- LARGE, SMALLReturn the nth largest or nth smallest value in a range.
Logic and error handling
- IFReturns one value when a condition is true and another when it is false.
- IFERRORRuns a formula and returns your own value instead if it produces an error.
- AND, OR, NOTCombine several TRUE/FALSE tests into a single TRUE or FALSE.
- IFSChecks conditions in order and returns the result of the first one that is true.
- SWITCHCompares one value against a list of options and returns the matching result.
- IFNARuns a formula and returns your own value only if it produces #N/A.
Text
- LEFT, RIGHT, MIDTake a fixed number of characters from the start, the end, or the middle of a text value.
- LENReturns how many characters a value contains, spaces included.
- TRIMStrips leading, trailing and repeated spaces, leaving single spaces between words.
- CONCAT and &Sticks several values together into one, with or without a separator.
- UPPER, LOWER, PROPERConvert text to capitals, to lower case, or to Title Case.
- VALUEConverts a number that is stored as text into a real number.
- DOLLARConverts a number into text formatted as currency.
- REPTRepeats a piece of text as many times as you ask.
Dates and time
- DATEDIFReturns the gap between two dates in whole years, months or days, depending on the unit you ask for.
- EOMONTHReturns the last day of the month, a given number of months before or after a date.
- WORKDAYReturns the date a given number of working days away, skipping weekends and any holidays you list.
- DATEAssembles a real date from three separate numbers.
- YEAR, MONTH, DAYPull the year, month or day number out of a date.
- WEEKDAYReturns a number saying which day of the week a date is.
- TIMEAssembles a time value from separate hour, minute and second numbers.
- HOUR, MINUTE, SECONDPull the hour, minute or second number out of a time.
- TIMEVALUEConverts a time written as text into a real time value.
Finance
- PMTWorks out the fixed periodic payment on a loan, given the amount, rate and number of periods.
- PVReturns what a series of future payments, or a future lump sum, is worth now.
- FVReturns what regular deposits, plus any starting balance, will grow to.
- RATEWorks out the interest rate implied by a payment, a term and an amount.
- CUMIPMT, CUMPRINCTotal the interest, or the capital, paid between any two payment numbers of a loan.
- DBReturns the depreciation of an asset for one period, using the fixed-declining-balance method.
Dynamic arrays
- FILTERReturns every row of a range that meets your condition, as a live result that resizes itself.
- UNIQUEReturns the distinct values from a range, with duplicates removed.
- TEXTSPLITSplits text on a delimiter and spills the pieces across columns or down rows.
- SORTReturns a range in sorted order, as a live result that re-sorts itself.
- TEXTBEFORE, TEXTAFTERReturn the part of a text value before or after a delimiter you name.
- LETDefines names inside a formula so you can reuse a calculation instead of repeating it.