Excel functions, explained with examples

79 of the Excel functions people use most, each with plain-English explanations, live examples, common mistakes and practice.

Math

• SUMAdds numbers
• AVERAGEMean of numbers
• MINSmallest number
• MAXLargest number
• MEDIANMiddle value
• PRODUCTMultiplies numbers
• LARGEk-th largest
• SMALLk-th smallest
• ROUNDRounds to digits
• ROUNDUPRounds away from zero
• ROUNDDOWNRounds toward zero
• INTRounds down to integer
• ABSAbsolute value
• MODRemainder after division
• SQRTSquare root
• POWERRaises to a power

Counting

• COUNTCounts numbers
• COUNTACounts non-empty cells
• COUNTBLANKCounts empty cells
• COUNTIFCounts cells that match
• COUNTIFSCounts with several conditions

Conditional

• SUMIFAdds cells that match
• SUMIFSAdds with several conditions
• AVERAGEIFAverages cells that match
• AVERAGEIFSAverages with several conditions

Logic

• IFPicks a result from a test
• IFSFirst true test wins
• ANDTrue if all tests are true
• ORTrue if any test is true
• NOTFlips TRUE and FALSE
• IFERRORFallback when a formula errors
• IFNAFallback for #N/A only

Text

• LEFTCharacters from the start
• RIGHTCharacters from the end
• MIDCharacters from the middle
• LENLength of text
• UPPERALL CAPS
• LOWERall lowercase
• PROPERCapitalize Each Word
• TRIMRemoves extra spaces
• CONCATJoins text
• CONCATENATEJoins text (older)
• TEXTJOINJoins with a separator
• FINDPosition of text (case-sensitive)
• SEARCHPosition of text
• SUBSTITUTEReplaces text
• VALUEText to number
• TEXTNumber to formatted text

Lookup

• VLOOKUPVertical lookup
• HLOOKUPHorizontal lookup
• XLOOKUPModern lookup in any direction
• MATCHPosition of a value
• INDEXValue at a position

Dates

• DATEBuilds a date
• TODAYToday’s date
• YEARYear of a date
• MONTHMonth number
• DAYDay of the month
• WEEKDAYDay of the week as a number
• EDATESame day, months later
• EOMONTHLast day of a month
• DAYSDays between dates
• DATEDIFWhole years, months or days between
• NETWORKDAYSWeekdays between dates

Finance

• PMTLoan payment
• FVFuture value of savings
• PVPresent value
• SUMPRODUCTMultiply then add
• STDEV.SStandard deviation

Cleanup

• ISBLANKIs the cell empty?
• ISNUMBERIs it a number?
• ISTEXTIs it text?
• ISERRORIs it an error?
• TEXTBEFOREText before a delimiter
• TEXTAFTERText after a delimiter

Dynamic arrays

• UNIQUERemoves duplicates, spills
• SORTSorts a range, spills
• FILTERKeeps matching rows, spills
• SEQUENCENumbered list, spills