This page isn't translated yet, so its text is in English. Function names and formulas are shown in your Excel language.
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.