Excel functions, explained

Pick a function. Each page says what it does in one sentence and lets you try it on a real table.

• SUMAdds up numbers, cells and ranges.
• AVERAGEReturns the average (arithmetic mean) of numbers.
• MINReturns the smallest number.
• MAXReturns the largest number.
• COUNTCounts the cells that contain numbers.
• COUNTACounts the cells that are not empty.
• COUNTBLANKCounts the empty cells in a range.
• ROUNDRounds a number to a number of digits.
• ROUNDUPAlways rounds away from zero.
• ROUNDDOWNAlways rounds towards zero.
• SUMIFAdds the cells that meet one condition.
• SUMIFSAdds the cells that meet several conditions.
• COUNTIFCounts the cells that meet a condition.
• COUNTIFSCounts the rows that meet several conditions.
• AVERAGEIFAverages the cells that meet a condition.
• AVERAGEIFSAverages the cells that meet several conditions.
• IFReturns one value if a test is true and another if it is false.
• ANDTRUE only if every test is true.
• ORTRUE if at least one test is true.
• NOTTurns TRUE into FALSE and FALSE into TRUE.
• IFSChecks several tests in order and returns the first match.
• SWITCHCompares one value with a list and returns the matching result.
• CHOOSEPicks a value from a list by its position.
• IFERRORShows a fallback instead of an error.
• VLOOKUPFinds a row by its first column and returns a value from it.
• XLOOKUPLooks up a value in one range and returns the match from another.
• INDEXReturns the value at a position in a range.
• MATCHReturns the position of a value in a range.
• CONCATJoins text from several cells or values.
• TEXTJOINJoins text with a separator between each piece.
• LEFTTakes characters from the start of text.
• RIGHTTakes characters from the end of text.
• MIDTakes characters from the middle of text.
• LENCounts the characters in text.
• FINDReturns the position of one text inside another.
• TRIMRemoves extra spaces from text.
• PROPERCapitalises the first letter of every word.
• UPPERTurns text into capitals.
• SUBSTITUTEReplaces one piece of text with another.
• YEARReturns the year of a date.
• MONTHReturns the month of a date as a number from 1 to 12.
• DATEBuilds a date from year, month and day.
• TODAYReturns today’s date.
• DAYSCounts the days between two dates.
• EDATEAdds a number of months to a date.
• EOMONTHReturns the last day of a month.
• WEEKDAYReturns the day of the week as a number.
• DATEDIFCounts whole days, months or years between two dates.
• NETWORKDAYSCounts the working days (Mon–Fri) between two dates, both included.
• WORKDAYReturns the date a number of working days later (or earlier).
• UNIQUEReturns a list without duplicates.
• SORTSorts a range.
• FILTERReturns the rows that meet a condition.
• SUMPRODUCTMultiplies ranges row by row and adds the results.
• LARGEReturns the k-th largest value.
• SMALLReturns the k-th smallest value.
• MEDIANReturns the middle value.