Function guides

Excel functions, explained.

54 functions, each with the syntax, a worked example on real data, and the mistakes people make. Want to actually remember them? Practice in a live spreadsheet.

Math

• SUMAdds numbers and ranges.
• AVERAGEThe mean of the numbers.
• MINThe smallest number.
• MAXThe largest number.
• COUNTCounts cells that contain numbers.
• COUNTACounts cells that are not empty.
• ROUNDRounds to a number of decimals.
• ABSRemoves the minus sign.
• MEDIANThe middle value.
• ROUNDUPRounds up, away from zero.
• ROUNDDOWNRounds down, toward zero.

Conditional

• COUNTIFCounts cells that match a condition.
• COUNTIFSCounts rows that match every condition.
• SUMIFAdds values whose row matches a condition.
• SUMIFSAdds values whose row matches every condition.
• AVERAGEIFAverages values whose row matches a condition.
• AVERAGEIFSAverages values whose rows match every condition.
• MAXIFSThe largest value among rows that match every condition.
• MINIFSThe smallest value among rows that match every condition.
• COUNTBLANKCounts empty cells.

Logic

• IFReturns one value if a test is true, another if false.
• ANDTRUE only if every test is true.
• ORTRUE if any test is true.
• NOTFlips TRUE and FALSE.
• IFERRORShows a fallback when a formula errors.
• IFSReturns the value for the first test that is TRUE.

Lookup

• VLOOKUPFinds a row by its first column and returns another column.
• XLOOKUPSearches one column and returns the matching value from another.
• MATCHReturns the position of a value in a list.
• INDEXReturns the value at a position in a range.

Text

• CONCATJoins text together.
• UPPERConverts to capitals.
• LOWERConverts to lowercase.
• LENCounts characters.
• LEFTThe first characters of a text.
• RIGHTThe last characters of a text.
• TRIMRemoves extra spaces.
• PROPERCapitalizes the first letter of each word.
• SUBSTITUTEReplaces text inside text.
• TEXTFormats a number or date as text.
• TEXTJOINJoins text with a separator between items.

Dates

• DATEBuilds a date from a year, month and day.
• YEARThe year of a date.
• MONTHThe month of a date, 1–12.
• DAYThe day of the month.
• WEEKDAYThe day of the week as a number. 1 = Sunday by default.
• EOMONTHThe last day of a month, some months away.
• NETWORKDAYSCounts weekdays between two dates, including both.
• TODAYToday’s date. It updates every day.

Dynamic arrays

• UNIQUELists each value once. The result spills down.
• SORTSorts a range. The result spills down.
• FILTERReturns only the rows that meet a condition. The result spills.
• SUMPRODUCTMultiplies ranges item by item, then adds the results.

Finance

• PMTThe payment for a loan with a fixed rate.