Excel Functions

Here is the complete Excel Functions list, including lookup, logical, date and time, text, information, math functions with examples.

Each function refers to an example.

What is an Excel function?

Let’s see the definition of the Excel function.

Excel Functions are built-in presets, so there are hard-coded formulas in Excel. With its help, you can avoid manual data entry, and it has small, user-friendly names. Type an equal sign (=) in the formula bar or the cell to enter a function you want to use. After that, enter the function’s name and the arguments.

Excel Function List

Logical

The functions below use logical tests and logical operators to evaluate a formula. Learn more about Boolean expressions.

FunctionHow it works
ANDTest multiple conditions using AND operator
FALSEReturns the Boolean value FALSE
IFRuns a logical test and returns one value for a TRUE result and another for a FALSE result.
IFSRuns multiple tests and return a value corresponding to the first TRUE result
IFNAManage #N/A errors
IFERRORManage all formula errors
NOTGet the opposite of a given logical or Boolean value
ORTest multiple conditions using OR operator
SWITCHMatch multiple values, return the first match
TRUEReturns the Boolean value TRUE
XORPerforms exclusive OR

LOOKUP and Reference

Here is the list of lookup functions in Excel. Of course, we already know that XLOOKUP changes everything. But it is worth keeping in mind the older lookup functions too.

FunctionHow it works
ADDRESSCreate a cell reference as text from a row and column number
AREASGet the number of areas in a reference.
CHOOSEGet a value from a list based on position
COLUMNGet the column number of a reference
COLUMNSGet the number of columns in an array or reference
FORMULATEXTShow the formula in a cell
GETPIVOTDATARetrieve data from a pivot table in a formula
HLOOKUPLookup a value in a horizontally arranged table
HYPERLINKCreate a link in a cell
INDEXReturns the value at a given location in a range or array
INDIRECTCreate a cell reference from a given text string
LOOKUPLookup a value in a one-column range (an approximate match)
MATCHReturns the relative position of an item in an array that matches a specified value in a specified order.
OFFSETReturns a dynamic range constructed with five inputs: starting point, row offset, column offset, row heights, column widths
ROWGet the row number for a reference
ROWSCount the rows in a given range
TRANSPOSESwitch rows to columns and vice versa
VLOOKUPLookup a value in a table by matching on the first column
XLOOKUPLook up data in a vertically organized table
XMATCHReturn an item’s relative position in an array. By default, an exact match is required.

Date and Time

Date and Time functions help you create calculations based on dates and times.

FunctionHow it works
DATECreate a date from the individual year, month, and day components
DATEDIFReturns the difference between two date values in years, months, or days
DATEVALUEConverts a date defined as a text string into a valid Excel date
DAYReturn the day as a number (1-31) from a date
DAYSGet the number of days between two dates
DAYS360Get days between 2 dates in a 360-day year
EDATEReturn the date on the same day of the month (past or future)
EOMONTHReturns last day of month n months in the past or future
HOURGet the hour component of a time (0-23)
ISOWEEKNUMReturns a week number (1-54) that follows ISO standards
MINUTEExtracts the minute component of a time (0-59)
MONTHGet month from a date as a number (1-12)
NETWORKDAYSReturns the number of working days between two dates
NETWORKDAYS.INTLCalculates the number of working days between two dates
NOWReturn the current date and time
SECONDReturn the Second part as a number (0-59) from a time
TIMECreate a time using hour, minute, and second parts
TIMEVALUEConverts text-format time into a proper Excel time
TODAYGet the current date (updated automatically when a worksheet is changed)
WEEKDAYReturns a number that represents the day of the week (1-7)
WEEKNUMGet the week number for a given date
WORKDAYReturns a date n working days in the future or past
WORKDAY.INTLGet the week number for a given date (1-54)
YEARReturns the year component from a date (4-digit)
YEARFRACReturns the fraction of a year between two dates

Text

Text functions in Excel enable you to perform various calculations using strings.

FunctionHow it works
CHARGet a character from a number
CLEANRemove the non-printable characters from the text
CODEGet a numeric code from a character
CONCATJoins separated text values into one without delimiter
CONCATENATEJoins values and returns the result as text
DOLLARConverts a number to text using the Currency format
EXACTCompare two text strings
FINDGet the position (number) of text in a string
FIXEDConvert numbers to text with fixed decimals
LEFTExtracts N characters from the left side of a text string
LENGet the number of characters in a given text string
LOWERConvert text to lower case
MIDExtract n characters from inside a string
NUMBERVALUEConverts a number in text format to a numeric value, using separators
PROPERCapitalize the first letter in each word in a given string
REPLACEReplaces characters specified by location in a text string with another text string
REPTRepeats characters a given number of times
RIGHTExtracts n characters from the right side of a selected text string
SEARCHGet the location of text in a string (the position of the first character)
SUBSTITUTEReplaces text in a given string
TEXTConvert a number to text but keep the format
TEXTJOINJoin text values using a delimiter
TRIMStrips extra spaces from text
UNICHARGet UNICODE character from a number
UNICODEGet number from a UNICODE character
UPPERConvert text to upper case
VALUEConvert text to numeric format

Dynamic Array

Dynamic Arrays are resizable arrays. From now, Excel calculates the array automatically and returns values into multiple cells based on a formula entered in a single cell.

FunctionHow it works
BYCOLApplies a LAMBDA function to each column
BYROWApplies a LAMBDA function to row
FILTERFilters a range using single or multiple criteria
LAMBDACreate a custom function
LETLets you define named variables in a formula
MAKEARRAYCreate an array using calculated values
MAPMaps a custom LAMBDA function
RANDARRAYGenerates an array of random numbers between
REDUCEReduce an array to a single value using the custom LAMBDA function
SCANGenerate running totals using LAMBDA
SEQUENCEReturns a list of sequential numbers in an array
SINGLEReturns a single value using an implicit intersection
SORTSorts the contents of a range or array (asc. or desc.)
SORTBYSorts range or array by column
UNIQUEReturns a list of unique values in a list or range

Information

Take a quick overview of information functions in Excel.

FunctionHow it works
CELLReturn information about a cell
ERROR.TYPEReturns a number that according to a specific error value
INFOGet information about the current environment (Office version, OS version, etc.)
ISBLANKReturns with TRUE if a cell is empty
ISERRReturns TRUE for any error type except the #N/A
ISERRORGet TRUE result for any error type in a cell
ISEVENTest if a value is even
ISFORMULAReturns TRUE if a cell contains a formula
ISLOGICALTest if a value is logical
ISNAReturns TRUE when a cell has the #N/A error
ISNONTEXTReturns TRUE when a cell contains any value except text
ISNUMBERGet TRUE result when a cell has a number
ISODDReturns TRUE when a value in a cell is an odd number
ISREFReturns TRUE when a cell contains a reference
ISTEXTTest for a text value
NConvert a value to a number
NADisplay the #N/A error when information is missing
SHEETReturns the index number of a Worksheet
SHEETSCount the total number of Worksheets in the Workbook.
TFilter text values only in a range
TYPEReturns a numeric code (type of value in a cell)

Math

Math Functions are great.

FunctionHow it works
ABSConverts negative numbers to positive numbers
AGGREGATEPerforms an aggregate calculation
ARABICConverts Roman numbers to Arabic numbers
BASEConvert number to another base
CEILINGRound a number up to the nearest specified multiple (always rounds up)
CEILING.MATHRound a number up to the nearest multiple (control over rounding direction for negative numbers)
CEILING.PRECISERound a number up to the nearest specified multiple (rounds negative numbers toward zero)
COMBINGet number of combinations (repetitions does not allowed)
COMBINAGet number of combinations (repetitions allowed)
DECIMALConvert a number in a different base to a decimal number (for example, binary to decimal)
EVENRounds numbers up to the next even integer
EXPGet the value of e raised to the power of a number
FACTCalculate the factorial of a number
FACTDOUBLECalculate the double factorial of a number
FLOORRounds a number down to the nearest specified multiple (always rounds down)
FLOOR.MATHRounds a number down to nearest multiple (support for rounding negative numbers)
FLOOR.PRECISERound number down to nearest multiple (always rounds negative numbers away from zero)
GCDCalculate the greatest common divisor of numbers
INTGet the integer part of a number (rounds down)
LCMCalculate the least common multiple of selected integers
LNReturn the number’s neutral logarithm
LOGGet the logarithm of a number
LOG10Return the base-10 logarithm of a number
MDETERMGet the determinant of a matrix
MINVERSECalculate the inverse matrix of the given matrix
MMULTMultiplication matrices
MODGet the remainder part of two numbers after division
MROUNDRound a number to the nearest specified multiple
MUNITCreate a matrix based on the given dimension

Math – Part 2

FunctionHow it works
ODDRound a number up to the next odd integer
PIReturn the value of π
POWERRaise a number to a given power
PRODUCTMultiply numbers in a given range
QUOTIENTCalculate the integer part of a division
RANDGenerate a random number between 0 and 1
RANDBETWEENGet a random integer between two values
ROMANConverts Arabic numbers to Roman numbers
ROUNDRound a number to “n” digits
ROUNDDOWNRound down a number to “n” digits
ROUNDUPRound up a number to “n” digits
SIGNReturns the sign of a number (+1, 0, -1)
SQRTCalculate the square root of a positive number
SUBTOTALGet a subtotal
SUMReturn sum of values
SUMIFSum of cells based on a single criteria
SUMIFSSum of cells based on multiple criteria
SUMPRODUCTMultiply, then sum arrays, then return the sum of products
SUMSQGet the sum of the squares
SUMX2MY2Calculate the sum of squares (based on the difference of values in two arrays)
SUMX2PY2Get the sum of squares of corresponding values in two arrays
SUMXMY2Calculate the sum of squares of differences between corresponding values in two arrays
TRUNCTruncate number based on “n” digits

Financial

Use financial functions if you want.

FunctionHow it works
ACCRINTGet accrued interest periodic
ACCRINTMGet accrued interest at maturity
AMORDEGRCCalculate the depreciation for a given accounting
AMORLINCGet depreciation for each accounting period
COUPDAYBSCalculate the number of days between the beginning of the coupon period and the settlement date
COUPDAYSGet days in coupon period (includes the settlement date)
COUPDAYSNCCalculate the number of days between the settlement date and the next coupon date
COUPNCDGet next coupon date after settlement date
COUPNUMGet the number of coupons payable between the settlement date and maturity date
COUPPCDGet the previous coupon date before the settlement date (for a coupon bond)
CUMIPMTCalculate the cumulative interest paid on loan between the start and end period
CUMPRINCGet the cumulative principal paid on loan between the start and end period
DBReturns Depreciation – fixed-declining balance
DDBDepreciation – double-declining balance
DISCReturns the discount rate for a security
DOLLARDEConvert fractional notation to a decimal value (for example, 1 1/4 to 1.25)
DOLLARFRConvert decimal dollar to a decimal value
DURATIONCalculate annual duration using periodic interest
EFFECTReturn effective annual interest rate
FVGet the future value of an asset
FVSCHEDULEGet the future value of principal compound interest
INTRATEReturns the interest rate for a fully invested security
IPMTGet the interest portion of loan payment in a given payment period
IRRCalculate the internal rate of return
ISPMTGet the interest in a given period where principal payments are equal
MDURATIONReturn the Macauley modified duration par value of $100
MIRRGet the modified internal rate of return for a series of cash flows

Financial – Part 2

FunctionHow it works
NOMINALGet the nominal annual interest rate
NPERDetermine loan payment periods
NPVCalculate the net present value of an investment
ODDFPRICEReturns the price per $100 face value of a security with an odd first period
ODDFYIELDGet the yield of a security with an odd first period
ODDLPRICEReturns the price per $100 face value of a security with an odd last period
PDURATIONGet the number of periods required for an investment to reach a given value
PMTGet the periodic payment for a loan
PPMTCalculate the principal portion of a given loan payment
PRICEGet the price per $100 face value of a security that pays periodic interest
PRICEDISCReturns the price per $100 face value of a discounted security
PRICEMATGet price per $100 interest at maturity
PVCalculate the present value of an investment
RATEGet the interest rate of a loan
RECEIVEDGet amount received at maturity
RRIGet an equivalent interest rate for the growth of an investment
SLNReturns the straight-line depreciation of an asset for one period
SYDDepreciation – sum-of-years
TBILLEQGet bond-equivalent yield for a Treasury bill
TBILLPRICEGet the price per $100 face value for a Treasury bill
TBILLYIELDGet the yield for a Treasury bill
VDBCalculate depreciation with a variable double-declining method
XIRRGet the internal rate of return for a schedule of cash flows
XNPVReturns the NPV for a schedule of cash flows
YIELDGet the yield on a security that pays periodic interest
YIELDDISCCalculate the annual yield for a discounted security
YIELDMATReturn annual yield of security interest at maturity

Statistical

FunctionHow it works
AVEDEVCalculate variability
AVERAGECalculate the average of supplied numbers
AVERAGEAGet the average of supplied numbers (evaluate the logical values also)
AVERAGEIFGet the average of numbers in a range that meets the criteria
AVERAGEIFSGet the average of numbers in a range that meets one or more criteria
BINOM.DISTReturn binomial distribution probability
COUNTCount numbers in a list or range
COUNTACount the number in a list or range (ignore blank cells)
COUNTBLANKCount blank cells
COUNTIFCount cells based on a criteria
COUNTIFSCount cells based on one or more criteria
DEVSQReturn sum of squared deviations
FORECASTPredict value based on existing values along with a linear trend
FORECAST.ETSPredict value based on existing values along with a seasonal trend
FORECAST.LINEARPredict value along with a linear trend (from Excel 2016 FORECAST function was replaced)
FREQUENCYReturns a frequency distribution
GEOMEANGet geometric mean for a set of numeric values
HARMEANGet harmonic mean for a set of numeric values
INTERCEPTReturns the point at which a regression line will intersect the y-axis
LARGEGet the nth largest value
LINESTGet parameters of linear trend
MAXReturn the largest numeric value (ignores empty cells)
MAXAReturn the largest numeric value (ignores empty cells and evaluate logical values)
MAXIFSCalculate maximum value with one or more criteria

Statistical – Part 2

FunctionHow it works
MEDIANGet the middle number (median) of a group of numbers
MINGet the smallest value of a list or range
MINAGet the smallest value (evaluates logical values as 1 and 0)
MINIFSReturn minimum value based on one or more criteria
MODEGet the most frequently occurring number in a list of numeric values
NORM.DISTGet values and areas for the normal distribution
PERCENTILEReturn the kth percentile for a set of data
PERCENTRANKGet the rank of a value in a data set as a percentage of the data
PERMUTGet number of permutations (does not allow repetitions)
PERMUTATIONAGet number of permutations (allow repetitions)
PROBCalculate probability
QUARTILEReturns the quartile for a data set (split the range into 4 parts)
RANKGet the rank of a numeric value (compared to a list of other numeric values)
SKEWReturns the skewness of distribution (a measure of symmetry)
SLOPEGet slope of the linear regression line
SMALLGet a numeric value based on its position
STANDARDIZECalculate z-score
STDEVReturn the standard deviation in a sample
TRIMMEANCalculate the average (excluding outliers)
VAREstimates the variance of a sample of data

Trigonometry

FunctionHow it works
ACOSGet the inverse cosine of a number
ASINGet the inverse sine of a number
ATANReturn the arctangent of a number
ATAN2Get the arctangent from x- and y-coordinates
COSGet the cosine of an angle given in radians
COSHGet the hyperbolic cosine of a number
COTReturns the cotangent of an angle
CSCGet the cosecant of an angle
DEGREESConverts radians to degrees
RADIANSConverts degrees to radians
SECReturns the secant of an angle
SINGet the sine of an angle
SINHReturn hyperbolic sine of a number
TANReturn the tangent of an angle

Engineering

FunctionHow it works
BIN2DECConverts a binary number to the decimal equivalent
BIN2HEXConverts a binary number to hexadecimal equivalent
BIN2OCTConverts a binary number to octal equivalent
BITANDGet a ‘Bitwise AND’ of two numeric values
BITLSHIFTShifts a number by the specified number of bits
BITORGet a ‘Bitwise OR’ of two numbers
BITXORGet a ‘Bitwise XOR’ of two numbers
COMPLEXCreates a complex number
CONVERTConverts numbers between the measurement system
DEC2BINConverts a decimal number to a binary number
DEC2HEXConverts a decimal number to a hexadecimal number
DEC2OCTConverts a decimal number to an octal number
DELTATest if two numeric values are equal
HEX2BINConverts a hexadecimal number to a binary number
HEX2DECConverts a hexadecimal number to a decimal number
HEX2OCTConverts a hexadecimal number to an octal number
IMABSReturns the absolute value of a complex number
IMAGINARYGet the imaginary coefficient of a complex number
IMPOWERRaise a complex number to a given power
IMPRODUCTReturn product of complex numbers
IMREALCalculate the real coefficient of a complex number
IMSUBCalculate the difference between two complex numbers
IMSUMReturn the sum of complex numbers

Database

FunctionHow it works
DAVERAGEReturn average from matching records
DCOUNTCount records in a database based on criteria (only numeric values)
DCOUNTACount records in a database based on criteria (counts numeric and text values)
DGETGet a single value from matching record
DMAXGet the maximum from matching records
DMINGet the maximum from matching records
DPRODUCTReturn the product of values from matching records
DSTDEVGet standard deviation of sample in matching records
DSUMCalculate sum from matching records
DVARGet sample variance for matching records based on criteria.