Excel function reference
What each function does, how to write it, a worked example and the mistakes to avoid, with its name in every Excel language we support.
02Adding up
03Counting
04Logic
- IFReturns one value when a test is true and another when it's false.
- IFSChecks several tests in order and returns the result of the first true one.
- SWITCHCompares one value with a list and returns the matching result.
- ANDReturns TRUE only when every test is true.
- ORReturns TRUE when at least one test is true.
- NOTTurns TRUE into FALSE and FALSE into TRUE.
- XORTRUE when an odd number of its tests are TRUE; with two tests, exactly one.
- ISNUMBERChecks whether a cell holds a number.
- ISTEXTChecks whether a cell holds text.
- ISBLANKChecks whether a cell is empty.
- ISERRORTRUE when a value or calculation is any error.
- IFERRORShows your own result instead of an error.
05Lookups
- IFNAReplaces only the #N/A error with your own result.
- VLOOKUPFinds a value in the first column of a table and returns a value from the same row.
- HLOOKUPFinds a value in the top row of a table and returns a value from the same column.
- XLOOKUPFinds a value in one range and returns the matching item from another.
06INDEX and MATCH
07Finding text
08Changing text
- CONCATJoins texts, cells and ranges into one text.
- TEXTJOINJoins texts with a separator between them, optionally skipping empty cells.
- UPPERTurns all letters of a text into capitals.
- LOWERTurns all letters of a text into small letters.
- PROPERCapitalises the first letter of each word.
- TRIMRemoves extra spaces from a text.
- SUBSTITUTEReplaces a piece of text with another wherever it appears.
- TEXTBEFOREReturns the text before a given separator.
- TEXTAFTERReturns the text after a given separator.
09Dates
- DATEBuilds a date from a year, a month and a day.
- TODAYReturns today's date.
- YEARReturns the year of a date.
- MONTHReturns the month of a date, from 1 to 12.
- DAYReturns the day of the month of a date, from 1 to 31.
- WEEKDAYReturns the day of the week of a date as a number.
- DAYSReturns the number of days between two dates.
- DATEDIFCounts whole years, months or days between two dates.
- EOMONTHReturns the last day of a month, a number of months away.
- NETWORKDAYSCounts the working days between two dates, Monday to Friday.
10Statistics
- AVERAGEReturns the average (arithmetic mean) of numbers.
- AVERAGEIFAverages the numbers whose row meets one condition.
- AVERAGEIFSAverages the numbers whose row meets several conditions.
- MAXReturns the largest number.
- MINReturns the smallest number.
- MAXIFSReturns the largest number whose row meets one or more conditions.
- LARGEReturns the k-th largest number, such as the second highest.
- SMALLReturns the k-th smallest number, such as the second lowest.
- MEDIANReturns the middle value of a set of numbers.
- MODEReturns the number that appears most often.
- RANK.EQReturns a number's rank in a list: 1 for the largest by default.
- ROUNDRounds a number to a given number of decimal places.
- CEILINGRounds a number up to the next multiple of another.
- FLOORRounds a number down to the previous multiple of another.
- EVENRounds a number up to the next even whole number.
- ODDRounds a number up to the next odd whole number.