Function library

Excel functions

Short, plain-English guides to the functions and skills you'll use most. Each one has a worked example and a practice exercise.

48 guides

• Lookup & referenceVLOOKUPFinds a value in the first column of a table and returns a value from another column in the same row.
• Lookup & referenceHLOOKUPFinds a value in the top row of a table and returns a value from a row below it.
• Lookup & referenceXLOOKUPSearches one range and returns the matching item from another. A modern, simpler replacement for VLOOKUP and HLOOKUP.
• Lookup & referenceINDEXReturns the value at a given position in a range.
• Lookup & referenceMATCHReturns the position of a value in a list.
• Lookup & referenceINDEX & MATCHCombines INDEX and MATCH into a flexible lookup that works in any direction and in every version of Excel.
• Lookup & referenceIFERRORShows a value of your choice instead of an error such as #N/A or #DIV/0!.
• Maths & statisticsSUMAdds numbers together: single cells, ranges, or both.
• Maths & statisticsSUMIFAdds up the values that meet one condition.
• Maths & statisticsSUMIFSAdds up the values that meet several conditions at once.
• Maths & statisticsAVERAGEReturns the mean of a group of numbers.
• Maths & statisticsAVERAGEIF & AVERAGEIFSAverages only the values that meet one or more conditions.
• Maths & statisticsCOUNT & COUNTACOUNT counts cells containing numbers. COUNTA counts cells that are not empty.
• Maths & statisticsCOUNTIFCounts the cells that meet one condition.
• Maths & statisticsCOUNTIFSCounts the rows that meet several conditions at once.
• Maths & statisticsMIN & MAXReturn the smallest and largest numbers in a range.
• Maths & statisticsMAXIFS & MINIFSReturn the largest or smallest value that meets one or more conditions.
• Maths & statisticsLARGE & SMALLReturn the nth largest or smallest number, such as the 2nd highest score.
• Maths & statisticsROUND, ROUNDUP & ROUNDDOWNRound a number to a set number of decimal places.
• Maths & statisticsMEDIAN & MODEMEDIAN returns the middle value; MODE returns the most common one.
• Maths & statisticsRANKReturns the position of a number within a list, such as 1st, 2nd or 3rd.
• Maths & statisticsSUMPRODUCTMultiplies ranges together row by row and adds up the results.
• Maths & statisticsMOD & ABSMOD returns the remainder after division. ABS removes the minus sign from a number.
• TextCONCAT & the & operatorJoin pieces of text together into one.
• TextTEXTJOINJoins a range of text with a separator, such as a comma, between each item.
• TextTEXTBEFORE & TEXTAFTERReturn the text before or after a delimiter, such as a space, comma or @ sign.
• TextExtract the first wordPull out the first word of some text, such as a first name.
• TextExtract the last wordPull out the last word of some text, such as a surname, however many words come first.
• TextLEFT, RIGHT & MIDReturn characters from the start, end or middle of some text.
• TextLENReturns the number of characters in some text, including spaces.
• TextFIND & SEARCHReturn the position of some text inside a cell.
• TextSUBSTITUTEReplaces one piece of text with another everywhere it appears.
• TextTRIMRemoves extra spaces from text, leaving single spaces between words.
• TextUPPER, LOWER & PROPERChange text to capitals, small letters, or a capital at the start of each word.
• LogicalIFReturns one value if a condition is true and another if it is false.
• LogicalIFSTests several conditions in order and returns the result for the first one that is true.
• LogicalSWITCHCompares one value against a list of exact matches and returns the matching result.
• LogicalAND, OR, NOT & XORCombine or reverse TRUE/FALSE tests.
• LogicalComparison operatorsCompare two values with =, <>, >, <, >= and <=, returning TRUE or FALSE.
• DatesTODAYReturns today's date, updating automatically every day.
• DatesYEAR, MONTH & DAYReturn the year, month or day number from a date.
• DatesEOMONTHReturns the last day of a month, a given number of months away.
• DatesNETWORKDAYSCounts the working days between two dates, leaving out weekends.
• References & toolsAbsolute references ($A$1)Lock a cell reference with $ so it stays the same when a formula is copied.
• References & toolsMixed references ($A1 and A$1)Lock only the column or only the row of a reference.
• References & toolsWildcard charactersMatch part of some text with *, ? and ~ in COUNTIF, SUMIF, lookups and Find.
• References & toolsDrop-down listsLet people pick a value from a list, so entries are consistent and spelled correctly.
• References & toolsFormat PainterCopy the formatting of one cell onto others in two clicks.

No guides match that search. Try a function name such as SUM.