Excel Function Reference

By Ravindra Bagale · Last updated: 31 October 2026

Clean reference pages: purpose, return value, syntax, arguments, version, Why→What→How, worked examples, errors, tips, quiz, practice file and a revision card.

Lookup

• XLOOKUPLook up a value and return a matching result from any column (left or right).
• VLOOKUPLook up a value in the first column of a range and return a value from another column.
• HLOOKUPLook up a value in the first row of a range and return a value from another row.
• INDEXReturn the value at a given row and column position inside a range.
• MATCHReturn the relative position of a lookup value inside a range.
• XMATCHModern MATCH: find position with exact, wildcard, or reverse search.
• LOOKUPClassic vector or array lookup (prefer XLOOKUP today).
• CHOOSEReturn one value from a list based on an index number.
• OFFSETReturn a range shifted from a starting cell by rows and columns.
• INDIRECTTurn a text string into a real cell or range reference.
• FORMULATEXTShow the formula in a cell as text.
• HYPERLINKCreate a clickable link to a web page, file, or place in the workbook.

Text

• TEXTJOINJoin text from many cells with a delimiter; optionally skip blanks.
• CONCATJoin text strings or ranges without a delimiter control.
• CONCATENATELegacy join of text arguments (prefer TEXTJOIN or CONCAT).
• TEXTFormat a number or date as text with a format code.
• LEFTReturn the leftmost characters from a text string.
• RIGHTReturn the rightmost characters from a text string.
• MIDReturn characters from the middle of a text string.
• LENCount the number of characters in a text string.
• FINDFind the start position of one text inside another (case-sensitive).
• SEARCHFind text position with wildcards (not case-sensitive).
• SUBSTITUTEReplace specific text with new text inside a string.
• REPLACEReplace characters at a fixed position with new text.
• TRIMRemove extra spaces, leaving single spaces between words.
• CLEANRemove non-printable characters from text.
• UPPERConvert text to UPPER CASE.
• LOWERConvert text to lower case.
• PROPERCapitalise the first letter of each word.
• VALUEConvert a text number into a real number.
• TEXTBEFOREReturn the text before a delimiter.
• TEXTAFTERReturn the text after a delimiter.
• TEXTSPLITSplit text into columns or rows by a delimiter.
• NUMBERVALUEConvert locale-style text numbers into values.

Date

• TODAYReturn today's date (updates when the workbook recalculates).
• NOWReturn the current date and time.
• DATEBuild a date from year, month and day numbers.
• DATEVALUEConvert a date stored as text into a real date.
• DAYReturn the day number (1–31) from a date.
• MONTHReturn the month number (1–12) from a date.
• YEARReturn the four-digit year from a date.
• EOMONTHReturn the last day of a month, shifted by n months.
• EDATEReturn a date shifted by n months.
• NETWORKDAYSCount working days between two dates (skip weekends/holidays).
• NETWORKDAYS.INTLWorking days with custom weekend patterns.
• WORKDAYReturn a date n working days after a start date.
• WORKDAY.INTLWorkday shift with custom weekends.
• DATEDIFDifference between two dates in years, months or days.
• WEEKDAYReturn weekday number for a date.
• WEEKNUMReturn the week number of the year.
• YEARFRACFraction of a year between two dates.

Logical

• IFReturn one value if a test is TRUE, another if FALSE.
• IFSTest several conditions in order and return the first match.
• ANDTRUE only when every argument is TRUE.
• ORTRUE when at least one argument is TRUE.
• NOTReverse TRUE to FALSE and FALSE to TRUE.
• XORTRUE when an odd number of arguments are TRUE.
• IFERRORReturn a fallback value when a formula errors.
• IFNAReturn a fallback only when the error is #N/A.
• SWITCHMatch an expression against a list of values and return the pair.
• TRUEReturn the logical value TRUE.
• FALSEReturn the logical value FALSE.
• LAMBDACreate a custom reusable formula (like a tiny function) without VBA.
• LETName intermediate calculations inside a formula to make it clearer and faster.

Math

• SUMAdd numbers in a range or list of values.
• SUMIFAdd cells that meet one condition.
• SUMIFSAdd cells that meet several conditions at once.
• SUMPRODUCTMultiply arrays and then add the products.
• ROUNDRound a number to a given number of digits.
• ROUNDUPAlways round a number away from zero.
• ROUNDDOWNAlways round a number toward zero.
• INTRound a number down to the nearest integer.
• TRUNCCut off decimal places without rounding.
• ABSReturn the absolute (positive) value of a number.
• MODReturn the remainder after division.
• POWERRaise a number to a power.
• SQRTReturn the square root of a number.
• PRODUCTMultiply all numbers in a list or range.
• QUOTIENTInteger portion of a division.
• SIGNReturn 1, 0 or -1 depending on the sign of a number.
• RANDRandom decimal between 0 and 1.
• RANDBETWEENRandom integer between two numbers.
• SUBTOTALAggregate that can ignore hidden or filtered rows.
• AGGREGATEFlexible aggregate with options to ignore errors and hidden rows.

Statistical

• AVERAGEArithmetic mean of numbers.
• AVERAGEIFAverage of cells that meet one condition.
• AVERAGEIFSAverage of cells that meet several conditions.
• COUNTCount cells that contain numbers.
• COUNTACount non-empty cells.
• COUNTBLANKCount empty cells in a range.
• COUNTIFCount cells that meet one condition.
• COUNTIFSCount cells that meet several conditions.
• MAXLargest value in a list.
• MINSmallest value in a list.
• MAXIFSLargest value that meets several conditions.
• MINIFSSmallest value that meets several conditions.
• MEDIANMiddle value in a sorted list of numbers.
• MODE.SNGLMost frequently occurring number.
• STDEV.SSample standard deviation.
• VAR.SSample variance.
• LARGENth largest value in a range.
• SMALLNth smallest value in a range.
• RANK.EQRank of a number in a list.
• PERCENTILE.INCPercentile of a range (inclusive).

Dynamic Array

• FILTERReturn all rows that match one or more conditions (spills).
• SORTSort a range or array and spill the result.
• SORTBYSort a range by the values in another range.
• UNIQUEReturn unique values from a range.
• SEQUENCEGenerate a list or grid of sequential numbers.
• RANDARRAYSpill an array of random numbers.
• TAKEReturn the first or last n rows/columns of an array.
• DROPRemove the first or last n rows/columns of an array.
• CHOOSECOLSReturn selected columns from an array.
• CHOOSEROWSReturn selected rows from an array.
• HSTACKStack arrays side by side horizontally.
• VSTACKStack arrays one under another.
• TOCOLFlatten an array into a single column.
• TOROWFlatten an array into a single row.
• WRAPCOLSWrap a row/column into a 2D array by columns.
• WRAPROWSWrap a row/column into a 2D array by rows.
• GROUPBYGroup rows by one or more columns and aggregate with a spill array (Excel 365).
• MAPApply a LAMBDA to every value in one or more arrays and spill the results.
• REDUCEAccumulate a running result across an array with a LAMBDA (fold).
• SCANLike REDUCE, but spill every intermediate accumulator value.
• BYROWRun a LAMBDA once per row of an array and spill one result per row.
• BYCOLRun a LAMBDA once per column of an array and spill one result per column.
• MAKEARRAYBuild a new array from row and column index numbers using a LAMBDA.
• PIVOTBYCreate a pivot-like summary with row and column fields that spills as an array.
• EXPANDPad an array to more rows and columns, filling extras with a value.
• TRIMRANGETrim unused edges from a range so formulas use the compact used block.
• ARRAYTOTEXTConvert an array to text for display, notes, or debugging.

Financial

• PMTCalculate the payment for a loan with constant payments and rate.
• IPMTInterest portion of a loan payment for a period.
• PPMTPrincipal portion of a loan payment for a period.
• FVFuture value of an investment with constant payments.
• PVPresent value of an investment or loan.
• NPERNumber of periods for an investment or loan.
• RATEInterest rate per period of an annuity.
• NPVNet present value of cash flows at a discount rate.
• IRRInternal rate of return for a series of cash flows.
• XIRRIRR when cash flows are not equally spaced.
• XNPVNPV when cash flows are not equally spaced.
• CUMIPMTCumulative interest paid between two periods.
• CUMPRINCCumulative principal paid between two periods.
• RRIEquivalent interest rate for growth of an investment.
• EFFECTEffective annual interest rate.
• NOMINALNominal annual interest rate.
• STOCKHISTORYPull historical stock price data into a spill grid (365 / Stocks data).

Info

• ISBLANKTRUE if a cell is empty.
• ISERRORTRUE if a value is any error.
• ISNATRUE if a value is the #N/A error.
• ISNUMBERTRUE if a value is a number.
• ISTEXTTRUE if a value is text.
• ISLOGICALTRUE if a value is TRUE or FALSE.
• TYPEReturn a number code for the type of a value.
• NConvert a value to a number (TRUE→1, text→0).
• NAReturn the #N/A error value.
• ERROR.TYPEReturn a number code for an error type.
• CELLReturn information about a cell (format, address, etc.).
• INFOReturn information about the current operating environment.
• SHEETReturn the sheet number of a reference.
• SHEETSReturn the number of sheets in a reference or workbook.
• IMAGEInsert an image into a cell from a URL (Excel 365).