Function library
Every Excel function
All 521 functions from Microsoft’s list, grouped the way Excel groups them. 500 run right here in the practice sheet; the rest come with step-by-step guides for desktop Excel.
521 functions
Math & trigonometry (80)
ABSAbsolute valueACOSArccosine, in radiansACOSHInverse hyperbolic cosineACOTArccotangent, in radiansACOTHInverse hyperbolic cotangentAGGREGATELike SUBTOTAL, and can skip errorsARABICConverts Roman numerals to a numberASINArcsine, in radiansASINHInverse hyperbolic sineATANArctangent, in radiansATAN2Angle from x and y coordinatesATANHInverse hyperbolic tangentBASEConverts a number to another baseCEILING.MATHRounds up to a multipleCEILING.PRECISERounds up to a multipleCOMBINNumber of combinationsCOMBINACombinations with repetitionCOSCosine of an angle in radiansCOSHHyperbolic cosineCOTCotangentCOTHHyperbolic cotangentCSCCosecantCSCHHyperbolic cosecantDECIMALConverts text in a base to a numberDEGREESRadians to degreesEVENRounds up to an even numberEXPe raised to a powerFACTFactorial (n!)FACTDOUBLEDouble factorial (n!!)FLOOR.MATHRounds down to a multipleFLOOR.PRECISERounds down to a multipleGCDGreatest common divisorINTRounds down to a whole numberISO.CEILINGRounds up to a multipleLCMLeast common multipleLNNatural logarithmLOGLogarithm in any baseLOG10Base-10 logarithmMDETERMDeterminant of a square matrixMINVERSEInverse of a square matrixMMULTMatrix productMODRemainder after divisionMROUNDRounds to the nearest multipleMULTINOMIALMultinomial coefficientMUNITIdentity matrixODDRounds up to an odd numberPERCENTOFSubset as a share of the totalPIThe number πPOWERRaises to a powerPRODUCTMultiplies numbersQUOTIENTWhole-number part of a divisionRADIANSDegrees to radiansRANDRandom number between 0 and 1RANDARRAYArray of random numbersRANDBETWEENRandom whole number in a rangeROMANConverts a number to Roman numeralsROUNDRounds a numberROUNDDOWNRounds toward zeroROUNDUPRounds away from zeroSECSecantSECHHyperbolic secantSEQUENCEA list of numbersSERIESSUMSum of a power seriesSIGN1, 0 or -1 for the signSINSine of an angle in radiansSINHHyperbolic sineSQRTSquare rootSQRTPISquare root of number × πSUBTOTALSubtotal that skips other subtotalsSUMAdds numbersSUMIFAdds cells that meet a conditionSUMIFSAdds cells that meet several conditionsSUMPRODUCTSum of productsSUMSQSum of squaresSUMX2MY2Sum of x² − y²SUMX2PY2Sum of x² + y²SUMXMY2Sum of (x − y)²TANTangent of an angle in radiansTANHHyperbolic tangentTRUNCCuts off decimals
Statistical (111)
AVEDEVAverage distance from the meanAVERAGEAverage of numbersAVERAGEAAverage counting text as 0AVERAGEIFAverage of cells that meet a conditionAVERAGEIFSAverage with several conditionsBETA.DISTBeta distributionBETA.INVInverse of the beta distributionBINOM.DISTBinomial distributionBINOM.DIST.RANGEProbability of a range of successesBINOM.INVSmallest successes with cumulative ≥ alphaCHISQ.DISTChi-squared distributionCHISQ.DIST.RTRight-tailed chi-squared probabilityCHISQ.INVInverse of the chi-squared distributionCHISQ.INV.RTInverse of the right-tailed chi-squaredCHISQ.TESTChi-squared test of independenceCONFIDENCE.NORMConfidence interval (normal)CONFIDENCE.TConfidence interval (Student’s t)CORRELCorrelation coefficientCOUNTCounts numbersCOUNTACounts non-empty cellsCOUNTBLANKCounts empty cellsCOUNTIFCounts cells that meet a conditionCOUNTIFSCounts cells that meet several conditionsCOVARIANCE.PPopulation covarianceCOVARIANCE.SSample covarianceDEVSQSum of squared deviationsEXPON.DISTExponential distributionF.DISTF distributionF.DIST.RTRight-tailed F probabilityF.INVInverse of the F distributionF.INV.RTInverse of the right-tailed FF.TESTTwo-tailed F-test of variancesFISHERFisher transformationFISHERINVInverse Fisher transformationFORECASTPredicts y on a straight lineFORECAST.ETSForecasts a future value with exponential smoothing that understands seasons (for example monthly sales that peak every December).FORECAST.ETS.CONFINTGives the ± range around a FORECAST.ETS prediction, so you can show “expected 120 ± 15”.FORECAST.ETS.SEASONALITYTells you the season length Excel found in your data (for example 12 for a yearly pattern in monthly data).FORECAST.ETS.STATReturns the statistics behind an ETS forecast (smoothing parameters and error measures) so you can judge how good it is.FORECAST.LINEARPredicts y on a straight lineFREQUENCYHow many values fall in each binGAMMAGamma function valueGAMMA.DISTGamma distributionGAMMA.INVInverse of the gamma distributionGAMMALNNatural log of the gamma functionGAMMALN.PRECISENatural log of the gamma functionGAUSSProbability between the mean and zGEOMEANGeometric meanGROWTHValues along an exponential trendHARMEANHarmonic meanHYPGEOM.DISTHypergeometric distributionINTERCEPTWhere the best-fit line crosses yKURTKurtosisLARGEk-th largest valueLINESTBest-fit straight line statisticsLOGESTBest-fit exponential curve statisticsLOGNORM.DISTLognormal distributionLOGNORM.INVInverse of the lognormal distributionMAXLargest numberMAXALargest value counting text as 0MAXIFSLargest value meeting conditionsMEDIANMiddle valueMINSmallest numberMINASmallest value counting text as 0MINIFSSmallest value meeting conditionsMODE.MULTAll most-frequent numbersMODE.SNGLMost frequent numberNEGBINOM.DISTNegative binomial distributionNORM.DISTNormal distributionNORM.INVInverse of the normal distributionNORM.S.DISTStandard normal distributionNORM.S.INVInverse of the standard normalPEARSONPearson correlation coefficientPERCENTILE.EXCk-th percentile (0 to 1 exclusive)PERCENTILE.INCk-th percentile (0 to 1 inclusive)PERCENTRANK.EXCRank as a percentage (exclusive)PERCENTRANK.INCRank as a percentage (inclusive)PERMUTNumber of permutationsPERMUTATIONAPermutations with repetitionPHIStandard normal densityPOISSON.DISTPoisson distributionPROBProbability that values fall in a rangeQUARTILE.EXCQuartile (1 to 3)QUARTILE.INCQuartile (0 to 4)RANK.AVGRank, averaging tiesRANK.EQRank of a number in a listRSQR² of the best-fit lineSKEWSkewness of a sampleSKEW.PSkewness of a populationSLOPESlope of the best-fit lineSMALLk-th smallest valueSTANDARDIZEz-score of a valueSTDEV.PStandard deviation of a populationSTDEV.SStandard deviation of a sampleSTDEVASample standard deviation counting text as 0STDEVPAPopulation standard deviation counting text as 0STEYXStandard error of predicted yT.DISTStudent’s t distributionT.DIST.2TTwo-tailed t probabilityT.DIST.RTRight-tailed t probabilityT.INVLeft-tailed inverse of tT.INV.2TTwo-tailed inverse of tT.TESTt-test probabilityTRENDValues along a straight-line trendTRIMMEANMean after trimming the extremesVAR.PVariance of a whole populationVAR.SVariance of a sampleVARASample variance counting text as 0VARPAPopulation variance counting text as 0WEIBULL.DISTWeibull distributionZ.TESTOne-tailed z-test probability
Logical (19)
ANDTRUE if all are trueBYCOLApplies a LAMBDA to each columnBYROWApplies a LAMBDA to each rowFALSEThe value FALSEIFOne value if true, another if falseIFERRORReplaces errorsIFNAReplaces #N/AIFSFirst value whose test is trueLAMBDAMakes your own functionLETNames values inside a formulaMAKEARRAYBuilds an array with a LAMBDAMAPApplies a LAMBDA to each valueNOTReverses TRUE and FALSEORTRUE if any is trueREDUCEReduces an array to one valueSCANRunning results of a LAMBDASWITCHMatches a value against a listTRUEThe value TRUEXORTRUE if an odd number are true
Lookup & reference (40)
ADDRESSA cell address as textAREASNumber of areas in a referenceCHOOSEPicks a value by positionCHOOSECOLSPicks columns from an arrayCHOOSEROWSPicks rows from an arrayCOLUMNColumn number of a referenceCOLUMNSNumber of columnsDROPRemoves rows or columns from the start or endEXPANDGrows an array to a sizeFILTERRows that meet a conditionFORMULATEXTThe formula in a cell, as textGETPIVOTDATAReads one number out of a PivotTable by naming it (“Sales for North”), so the formula keeps working when the PivotTable changes shape.GROUPBYGroups rows and summarises values (like a quick PivotTable)HLOOKUPLooks across the first rowHSTACKJoins arrays side by sideHYPERLINKA clickable link (shows its name)IMAGEPuts a picture from the web inside a cell, where it moves and sorts with the data.INDEXValue at a row and columnINDIRECTThe reference named by some textLOOKUPApproximate lookup in a row or columnMATCHPosition of a value in a listOFFSETA reference moved from a starting pointPIVOTBYSummarises values by rows and columns (like a PivotTable)ROWRow number of a referenceROWSNumber of rowsRTDShows live, real-time data pushed by a program on your computer (for example a trading platform).SORTSorts a rangeSORTBYSorts by other rangesTAKERows or columns from the start or endTOCOLTurns an array into one columnTOROWTurns an array into one rowTRANSPOSESwaps rows and columnsTRIMRANGERemoves empty edge rows and columnsUNIQUERemoves duplicatesVLOOKUPLooks down the first columnVSTACKStacks arrays on top of each otherWRAPCOLSWraps a list into columnsWRAPROWSWraps a list into rowsXLOOKUPFinds a value and returns its matchXMATCHPosition of a value (modern MATCH)
Text (50)
ARRAYTOTEXTAn array written as textASCFull-width (double-byte) to half-widthBAHTTEXTNumber as Thai text with “baht”CHARCharacter from a code (1–255)CLEANRemoves non-printing charactersCODECode of the first characterCONCATJoins textCONCATENATEJoins textDBCSHalf-width to full-width (double-byte)DETECTLANGUAGETells you which language some text is written in, as a language code (for example "fr").DOLLARNumber as currency textEXACTTRUE if exactly the sameFINDPosition of text (case-sensitive)FINDBPosition of text (bytes)FIXEDNumber as text with fixed decimalsJISHalf-width to full-width (double-byte)LEFTFirst charactersLEFTBFirst characters (bytes)LENNumber of charactersLENBLength in bytesLOWERConverts to lower caseMIDCharacters from the middleMIDBCharacters from the middle (bytes)NUMBERVALUEText to number with any separatorsPHONETICFurigana (phonetic) textPROPERCapitalises Each WordREGEXEXTRACTExtracts text matching a patternREGEXREPLACEReplaces text matching a patternREGEXTESTTRUE if the text matches a patternREPLACEReplaces characters at a positionREPLACEBReplaces characters (bytes)REPTRepeats textRIGHTLast charactersRIGHTBLast characters (bytes)SEARCHPosition of text (any case, wildcards)SEARCHBPosition of text (bytes, any case)SUBSTITUTEReplaces textTThe text, or "" if it isn’t textTEXTFormats a number as textTEXTAFTERText after a delimiterTEXTBEFOREText before a delimiterTEXTJOINJoins text with a separatorTEXTSPLITSplits text into columns and rowsTRANSLATETranslates text between languages using Microsoft’s translation service.TRIMRemoves extra spacesUNICHARCharacter from a Unicode numberUNICODEUnicode number of the first characterUPPERConverts to CAPITALSVALUEConverts text to a numberVALUETOTEXTA value written as text
Date & time (25)
DATEBuilds a dateDATEDIFDifference in years, months or daysDATEVALUEConverts text to a dateDAYDay of a dateDAYSDays between two datesDAYS360Days between dates on a 360-day yearEDATESame day, months laterEOMONTHLast day of a monthHOURHour of a time (0–23)ISOWEEKNUMISO week numberMINUTEMinute of a time (0–59)MONTHMonth of a dateNETWORKDAYSWorking days between datesNETWORKDAYS.INTLWorking days with custom weekendsNOWCurrent date and timeSECONDSecond of a time (0–59)TIMEBuilds a timeTIMEVALUEConverts text to a timeTODAYToday’s dateWEEKDAYDay of the week as a numberWEEKNUMWeek number of the yearWORKDAYDate after a number of working daysWORKDAY.INTLWorking-day date with custom weekendsYEARYear of a dateYEARFRACFraction of a year between two dates
Financial (56)
ACCRINTAccrued interest of a periodic-interest securityACCRINTMAccrued interest paid at maturityAMORDEGRCFrench declining-balance depreciationAMORLINCFrench straight-line depreciationCOUPDAYBSDays from coupon start to settlementCOUPDAYSDays in the coupon periodCOUPDAYSNCDays from settlement to next couponCOUPNCDNext coupon date after settlementCOUPNUMNumber of coupons leftCOUPPCDCoupon date before settlementCUMIPMTInterest paid between two periodsCUMPRINCPrincipal paid between two periodsDBFixed-declining balance depreciationDDBDouble-declining balance depreciationDISCDiscount rate of a securityDOLLARDEFraction price to a decimalDOLLARFRDecimal price to a fractionDURATIONMacaulay duration of a bondEFFECTEffective annual interest rateEUROCONVERTConverts between euro-zone currenciesFVFuture value of an investmentFVSCHEDULEFuture value with changing ratesINTRATEInterest rate of a fully invested securityIPMTInterest part of a paymentIRRInternal rate of returnISPMTInterest paid in a period (straight-line loan)MDURATIONModified duration of a bondMIRRModified internal rate of returnNOMINALNominal annual interest rateNPERNumber of payment periodsNPVNet present valueODDFPRICEPrice of a bond with an odd first periodODDFYIELDYield of a bond with an odd first periodODDLPRICEPrice of a bond with an odd last periodODDLYIELDYield of a bond with an odd last periodPDURATIONPeriods to reach a valuePMTPayment for a loanPPMTPrincipal part of a paymentPRICEPrice per $100 of a bondPRICEDISCPrice of a discounted securityPRICEMATPrice of a security that pays interest at maturityPVPresent value of an investmentRATEInterest rate per periodRECEIVEDAmount received at maturityRRIEquivalent interest rate for growthSLNStraight-line depreciationSYDSum-of-years’ digits depreciationTBILLEQBond-equivalent yield of a Treasury billTBILLPRICEPrice of a Treasury billTBILLYIELDYield of a Treasury billVDBDeclining balance depreciation for any periodXIRRInternal rate of return for irregular datesXNPVNet present value of irregular cash flowsYIELDYield of a bondYIELDDISCYield of a discounted securityYIELDMATYield of a security that pays interest at maturity
Information (21)
CELLInformation about a cellERROR.TYPENumber for an error typeINFOInformation about the environmentISBLANKTRUE if emptyISERRTRUE if an error other than #N/AISERRORTRUE if any errorISEVENTRUE if evenISFORMULATRUE if the cell has a formulaISLOGICALTRUE if TRUE or FALSEISNATRUE if #N/AISNONTEXTTRUE if not textISNUMBERTRUE if a numberISODDTRUE if oddISOMITTEDTRUE if a LAMBDA argument was left outISREFTRUE if a cell referenceISTEXTTRUE if textNA value converted to a numberNAThe #N/A error valueSHEETSheet numberSHEETSNumber of sheetsTYPEType of a value (1, 2, 4, 16, 64)
Database (12)
DAVERAGEAverage of a column for matching recordsDCOUNTCounts numbers in matching recordsDCOUNTACounts non-empty cells in matching recordsDGETThe single value that matchesDMAXLargest value in matching recordsDMINSmallest value in matching recordsDPRODUCTProduct of values in matching recordsDSTDEVSample standard deviation of matching recordsDSTDEVPPopulation standard deviation of matching recordsDSUMSum of a column for matching recordsDVARSample variance of matching recordsDVARPPopulation variance of matching records
Engineering (54)
BESSELIModified Bessel function In(x)BESSELJBessel function Jn(x)BESSELKModified Bessel function Kn(x)BESSELYBessel function Yn(x)BIN2DECConverts binary to decimalBIN2HEXConverts binary to hexadecimalBIN2OCTConverts binary to octalBITANDBitwise ANDBITLSHIFTShifts bits leftBITORBitwise ORBITRSHIFTShifts bits rightBITXORBitwise XORCOMPLEXBuilds a complex numberCONVERTConverts between unitsDEC2BINConverts decimal to binaryDEC2HEXConverts decimal to hexadecimalDEC2OCTConverts decimal to octalDELTA1 if two numbers are equalERFError functionERF.PRECISEError functionERFCComplementary error functionERFC.PRECISEComplementary error functionGESTEP1 if number ≥ stepHEX2BINConverts hexadecimal to binaryHEX2DECConverts hexadecimal to decimalHEX2OCTConverts hexadecimal to octalIMABSAbsolute value (modulus)IMAGINARYImaginary partIMARGUMENTAngle (argument) in radiansIMCONJUGATEComplex conjugateIMCOSCosineIMCOSHHyperbolic cosineIMCOTCotangentIMCSCCosecantIMCSCHHyperbolic cosecantIMDIVQuotientIMEXPExponentialIMLNNatural logarithmIMLOG10Base-10 logarithmIMLOG2Base-2 logarithmIMPOWERRaised to a powerIMPRODUCTProduct of complex numbersIMREALReal partIMSECSecantIMSECHHyperbolic secantIMSINSineIMSINHHyperbolic sineIMSQRTSquare rootIMSUBDifferenceIMSUMSum of complex numbersIMTANTangentOCT2BINConverts octal to binaryOCT2DECConverts octal to decimalOCT2HEXConverts octal to hexadecimal
Compatibility (40)
BETADISTBeta cumulative distributionBETAINVInverse of the beta distributionBINOMDISTBinomial distributionCEILINGRounds up to a multipleCHIDISTRight-tailed chi-squared probabilityCHIINVInverse of the right-tailed chi-squaredCHITESTChi-squared test of independenceCONFIDENCEConfidence interval (normal)COVARPopulation covarianceCRITBINOMSmallest successes with cumulative ≥ alphaEXPONDISTExponential distributionFDISTRight-tailed F probabilityFINVInverse of the right-tailed FFLOORRounds down to a multipleFTESTTwo-tailed F-test of variancesGAMMADISTGamma distributionGAMMAINVInverse of the gamma distributionHYPGEOMDISTHypergeometric probabilityLOGINVInverse of the lognormal distributionLOGNORMDISTLognormal cumulative distributionMODEMost frequent numberNEGBINOMDISTNegative binomial probabilityNORMDISTNormal distributionNORMINVInverse of the normal distributionNORMSDISTStandard normal cumulative distributionNORMSINVInverse of the standard normalPERCENTILEk-th percentile (0 to 1 inclusive)PERCENTRANKRank as a percentage (inclusive)POISSONPoisson distributionQUARTILEQuartile (0 to 4)RANKRank of a number in a listSTDEVStandard deviation of a sampleSTDEVPStandard deviation of a populationTDISTt-distribution tail probabilityTINVTwo-tailed inverse of tTTESTt-test probabilityVARVariance of a sampleVARPVariance of a whole populationWEIBULLWeibull distributionZTESTOne-tailed z-test probability
Web (6)
CALLRuns a function inside a Windows DLL. It is an old Excel 4 macro feature, kept only for very old workbooks.ENCODEURLURL-encodes textFILTERXMLPulls values out of XML text using an XPath, usually after WEBSERVICE downloads it.REGISTER.IDRegisters a DLL function and returns its ID so CALL can use it. Excel 4 macro feature for old workbooks.STOCKHISTORYDownloads historical prices for a stock, fund or currency pair from Microsoft’s data service.WEBSERVICEDownloads text (often XML or JSON) from a web address straight into a cell.
Cube (7)
CUBEKPIMEMBERReturns a KPI (key performance indicator) value, goal, status or trend defined in an OLAP cube or Data Model.CUBEMEMBERPicks one item (a member or tuple) from the Data Model so CUBEVALUE can use it — like a row or column heading in a PivotTable.CUBEMEMBERPROPERTYReturns a property of a member in an OLAP cube (for example a product’s colour or a customer’s city).CUBERANKEDMEMBERReturns the nth member of a set — the way to build “Top 5” lists from a Data Model.CUBESETDefines a group of members (for example every product) that CUBERANKEDMEMBER and CUBESETCOUNT can work with — handy for Top-10 lists.CUBESETCOUNTCounts how many members a CUBESET contains.CUBEVALUEReturns a total (a measure) from a Power Pivot Data Model or an OLAP cube, filtered by the members you list.