Exceljet

Quick, clean, and to the point

Excel Function Guide

Optional arguments in white. See also: 500 Formulas and 101 Functions.

Database

DAVERAGE
Get average from matching records
databasefieldcriteria
DCOUNT
Count matching records in a database
databasefieldcriteria
DCOUNTA
Count matching records in a database
databasefieldcriteria
DGET
Get value from matching record
databasefieldcriteria
DMAX
Get max from matching records
databasefieldcriteria
DMIN
Get min from matching records
databasefieldcriteria
DPRODUCT
Get product from matching records
databasefieldcriteria
DSTDEV
Get standard deviation of sample in matching records
databasefieldcriteria
DSTDEVP
Get standard deviation of population in matching records
databasefieldcriteria
DSUM
Get sum from matching records
databasefieldcriteria
DVAR
Get sample variance for matching records
databasefieldcriteria
DVARP
Get population variance for matching records
databasefieldcriteria

Date and time

DATE
Create a date with year, month, and day
yearmonthday
DATEDIF
Get days, months, or years between two dates
start_dateend_dateunit
DATEVALUE
Convert a date in text format to a valid date
date_text
DAY
Get the day as a number (1-31) from a date
date
DAYS
Get days between dates
end_datestart_date
DAYS360
Get days between 2 dates in a 360-day year
start_dateend_datemethod
EDATE
Shift date n months in future or past
start_datemonths
EOMONTH
Get last day of month n months in future or past
start_datemonths
HOUR
Get the hour as a number (0-23) from a Time
serial_number
ISOWEEKNUM
Get ISO week number for a given date
date
MINUTE
Get minute as a number (0-59) from time
serial_number
MONTH
Get month as a number (1-12) from a date
date
NETWORKDAYS
Get the number of working days between two dates
start_dateend_dateholidays
NETWORKDAYS.INTL
Get work days between two dates
start_dateend_dateweekendholidays
NOW
Get the current date and time
SECOND
Get the Second as a number (0-59) from a Time
serial_number
TIME
Create a time with hours, minutes, and seconds
hourminutesecond
TIMEVALUE
Get a valid time from a text string
time_text
TODAY
Get the current date
WEEKDAY
Get the day of the week as a number
serial_numberreturn_type
WEEKNUM
Get the week number for a given date
serial_numreturn_type
WORKDAY
Get a date n working days in the future or past
start_datedaysholidays
WORKDAY.INTL
Get date n working days in future or past
start_datedaysweekendholidays
YEAR
Get the year from a date
date
YEARFRAC
Get the fraction of a year between two dates
start_dateend_datebasis

Engineering

BIN2DEC
Converts a binary number to decimal
number
BIN2HEX
Converts a binary number to hexadecimal
numberplaces
BIN2OCT
Converts a binary number to octal
numberplaces
BITAND
Returns a 'Bitwise And' of two numbers
number1number2
BITLSHIFT
Returns a number shifted left by some number of bits
numbershift_amount
BITOR
Returns a 'Bitwise Or' of two numbers
number1number2
BITRSHIFT
Returns a number shifted right by some number of bits
numbershift_amount
BITXOR
Returns a 'Bitwise Xor' of two numbers
number1number2
COMPLEX
Convert coefficients to complex number
real_numi_numsuffix
CONVERT
Convert measurement units
numberfrom_unitto_unit
DEC2BIN
Converts a decimal number to binary
numberplaces
DEC2HEX
Converts a decimal number to hexadecimal
numberplaces
DEC2OCT
Converts a decimal number to octal
numberplaces
DELTA
Test two values are equal
number1number2
HEX2BIN
Converts a hexadecimal number to binary
numberplaces
HEX2DEC
Converts a hexadecimal number to decimal
number
HEX2OCT
Converts a hexadecimal number to octal
numberplaces
IMABS
Get absolute value of complex number
inumber
IMAGINARY
Get imaginary coefficient of complex number
inumber
IMPOWER
Raise complex number to given power
inumbernumber
IMPRODUCT
Get product of complex numbers
inumber1inumber2...
IMREAL
Get real coefficient of complex number
inumber
IMSUB
Get difference between two complex numbers
inumber1inumber2
IMSUM
Get sum of complex numbers
inumber1inumber2...

Financial

ACCRINT
Get accrued interest periodic
idfdsdrateparfreqbasiscalc
ACCRINTM
Get accrued interest at maturity
idsdrateparbasis
AMORDEGRC
Depreciation for accounting period coefficient
costpurchasefirstsalvageperiodratebasis
AMORLINC
Depreciation for accounting period
costpurchasefirstsalvageperiodratebasis
COUPDAYBS
Get days from coupon period to settlement date
settlementmaturityfrequencybasis
COUPDAYS
Get days in coupon period incl settlement date
settlementmaturityfrequencybasis
COUPDAYSNC
Get days from settlement date to next coupon date
settlementmaturityfrequencybasis
COUPNCD
Get next coupon date after settlement date
settlementmaturityfrequencybasis
COUPNUM
Get number of coupons payable
settlementmaturityfrequencybasis
COUPPCD
Get previous coupon date before settlement date
settlementmaturityfrequencybasis
CUMIPMT
Get cumulative interest paid on a loan
ratenperpvstart_periodend_periodtype
CUMPRINC
Get cumulative principal paid on a loan
ratenperpvstart_periodend_periodtype
DB
Depreciation - fixed-declining balance
costsalvagelifeperiodmonth
DDB
Depreciation - double-declining
costsalvagelifeperiodfactor
DISC
Get discount rate for a security
settlementmaturityprredemptionbasis
DOLLARDE
Convert dollar price as fraction to decimal
fractional_dollarfraction
DOLLARFR
Convert price to fractional notation
decimal_dollarfraction
DURATION
Get annual duration with periodic interest
settlementmaturitycouponyldfreqbasis
EFFECT
Get effective annual interest rate
nominal_ratenpery
FV
Get the future value of an investment
ratenperpmtpvtype
FVSCHEDULE
Get future value of principal compound interest
principalschedule
INTRATE
Get interest rate for fully invested security
settlementmaturityinvestmentredemptionbasis
IPMT
Get interest in given period
ratepernperpvfvtype
IRR
Calculate internal rate of return
valuesguess
ISPMT
Get interest paid for specific period
ratepernperpv
MDURATION
Get Macauley modified duration par value of $100
settlementmaturitycouponyldfreqbasis
MIRR
Calculate modified internal rate of return
valuesfinance_ratereinvest_rate
NOMINAL
Get annual nominal interest rate
effect_ratenpery
NPER
Get number of periods for loan or investment
ratepmtpvfvtype
NPV
Calculate net present value
ratevalue1value2...
ODDFPRICE
Get price per $100 odd first period
sdmdidfdrateyldredemfreqbasis
ODDFYIELD
Get yield security with odd first period
sdmdidfdrateprredemfreqbasis
ODDLPRICE
Get price per $100 face value with odd last period
sdmdidrateyldredemfreqbasis
ODDLYIELD
Get yield of security with odd last period
sdmdldrateprredemfreqbasis
PDURATION
Get periods required to reach given value
ratepvfv
PMT
Get the periodic payment for a loan
ratenperpvfvtype
PPMT
Get principal payment in given period
ratepernperpvfvtype
PRICE
Get price per $100 face value - periodic interest
sdmdrateyldredemptionfrequencybasis
PRICEDISC
Get price per $100 discounted security
sdmddiscountredemptionbasis
PRICEMAT
Get price per $100 interest at maturity
sdmdidrateyldbasis
PV
Get the present value of an investment
ratenperpmtfvtype
RATE
Get the interest rate per period of an annuity
nperpmtpvfvtypeguess
RECEIVED
Get amount received at maturity
settlementmaturityinvestmentdiscountbasis
RRI
Get equivalent interest rate for growth
nperpvfv
SLN
Depreciation - straight-line
costsalvagelife
SYD
Depreciation - sum-of-years
costsalvagelifeperiod
TBILLEQ
Get bond-equivalent yield for a Treasury bill
settlementmaturitydiscount
TBILLPRICE
Get price per $100 Treasury bill
settlementmaturitydiscount
TBILLYIELD
Get yield for a Treasury bill
settlementmaturityprice
VDB
Depreciation - double-declining variable
costsalvagelifestartendfactorno_switch
XIRR
Calculate internal rate of return for irregular cash flows
valuesdatesguess
XNPV
Calculate net present value for irregular cash flows
ratevaluesdates
YIELD
Get yield for security that pays periodic interest
sdmdrateprredemptionfrequencybasis
YIELDDISC
Get annual yield for discounted security
sdmdprredemptionbasis
YIELDMAT
Get annual yield of security interest at maturity
sdmdidrateprbasis

Information

CELL
Get information about a cell
info_typereference
ERROR.TYPE
Test for a specific error value
error_val
INFO
Get information about current environment
type_text
ISBLANK
Test if a cell is empty
value
ISERR
Test for any error but #N/A
value
ISERROR
Test for any error
value
ISEVEN
Test if a value is even
value
ISFORMULA
Test if cell contains a formula
reference
ISLOGICAL
Test if a value is logical
value
ISNA
Test for the #N/A error
value
ISNONTEXT
Test for a non-text value
value
ISNUMBER
Test for numeric value
value
ISODD
Test if a value is odd
value
ISREF
Test for a reference
value
ISTEXT
Test for a text value
value
N
Convert a value to a number
value
NA
Create an #N/A error
SHEET
Get sheet index number
value
SHEETS
Get number of sheets in a reference
reference
T
Filter text values only
value
TYPE
Get the type of value in a cell
value

Logical

AND
Test multiple conditions with AND
logical1logical2...
FALSE
Generate the logical value FALSE
IF
Test for a specific condition
logical_testvalue_if_truevalue_if_false
IFERROR
Trap and handle errors
valuevalue_if_error
IFNA
Trap and handle #N/A errors
valuevalue_if_na
IFS
Test multiple conditions, return first true
test1value1test2, value2...
NOT
Reverse arguments or results
logical
OR
Test multiple conditions with OR
logical1logical2...
SWITCH
Match multiple values, return first match
expressionval1/result1val2/result2...default
TRUE
Generate the logical value TRUE
XOR
Perform exclusive OR
logical1logical2...

Lookup and reference

ADDRESS
Create a cell address from a given row and column
row_numcol_numabs_numa1sheet
AREAS
Get the number of areas in a reference.
reference
CHOOSE
Get a value from a list based on position
index_numvalue1value2...
COLUMN
Get the column number of a reference.
reference
COLUMNS
Get the number of columns in an array or reference.
array
FORMULATEXT
Get the formula in a cell
reference
GETPIVOTDATA
Retrieve data from a pivot table in a formula
data_fieldpivot_tablefield1, item1...
HLOOKUP
Look up a value in a table by matching on the first row
valuetablerow_indexrange_lookup
HYPERLINK
Create a clickable link.
link_locationfriendly_name
INDEX
Get a value in a list or table based on location
arrayrow_numcol_numarea_num
INDIRECT
Create a reference from text
ref_texta1
LOOKUP
Look up a value in a one-column range
lookup_valuelookup_vectorresult_vector
MATCH
Get the position of an item in an array
lookup_valuelookup_arraymatch_type
MMULT
Perform matrix multiplication
array1array2
OFFSET
Create a reference offset from given starting point
referencerowscolsheightwidth
ROW
Get the row number of a reference
reference
ROWS
Get the number of rows in an array or reference.
array
TRANSPOSE
Flip the orientation of a range of cells
array
VLOOKUP
Lookup a value in a table by matching on the first column
valuetablecol_indexrange_lookup

Math

ABS
Find the absolute value of a number
number
AGGREGATE
Return aggregate calculation
function_numoptionsref1ref2
ARABIC
Converts a Roman numerals to an Arabic numerals
roman_text
ASIN
Get the angle of a triangle.
number
CEILING
Round a number up to nearest multiple
numbermultiple
CEILING.MATH
Round a number up to nearest multiple
numbersignificancemode
CEILING.PRECISE
Round a number up to nearest multiple
numbersignificance
COS
Get the cosine of an angle
number
DECIMAL
Converts an alpha-numeric number to decimal
numberradix
DEGREES
Converts radians to degrees
angle
EVEN
Round a number up to the next even integer
number
EXP
Find the value of e raised to the power of a number
number
FACT
Find the factorial of a number
number
FACTDOUBLE
Get double factorial of a number
number
FLOOR
Round a number down to the nearest specified multiple
numbermultiple
FLOOR.MATH
Round number down to nearest multiple
numbersignificancemode
FLOOR.PRECISE
Round number down to nearest multiple
numbersignificance
GCD
Get the greatest common divisor of two or more numbers
number1number2...
INT
Get the integer part of a decimal by rounding down
number
LCM
Get the least common multiple or two or more numbers
number1number2...
LN
Get the natural logarithm of a number
number
LOG
Get the logarithm of a number
numberbase
LOG10
Get the base-10 logarithm of a number
number
MDETERM
Get matrix determinant of given array
array
MINVERSE
Get inverse matrix of array
array
MOD
Get the remainder from division
numberdivisor
MROUND
Round a number to the nearest specified multiple
numbermultiple
MUNIT
Return unit matrix for a given dimension
dimension
ODD
Round a number up to the next odd integer
number
PI
Get the value of π
POWER
Raise a number to a power
numberpower
PRODUCT
Get the product of supplied numbers
number1number2...
QUOTIENT
Returns the quotient without a remainder.
numeratordenominator
RADIANS
Converts degrees into radians
angle
RAND
Get a random number between 0 and 1
RANDBETWEEN
Get a random integer between two values
bottomtop
ROMAN
Converts numbers to Roman numerals
numberform
ROUND
Round a number to a given number of digits
numbernum_digits
ROUNDDOWN
Round a number down to a given number of digits
numbernum_digits
ROUNDUP
Round a number up to a given number of digits
numbernum_digits
SIGN
Get the sign of a number
number
SIN
Get the sine of an angle
number
SQRT
Find the positive square root of a number
number
SUBTOTAL
Get a subtotal in a list or database
function_numref1ref2...
SUM
Add numbers together
number1number2number3...
SUMIF
Sum numbers in a range that meet supplied criteria
rangecriteriasum_range
SUMIFS
Sum cells that match multiple criteria
sum_rangerange1criteria1range2criteria2...
SUMPRODUCT
Multiply, then sum arrays
array1array2...
TAN
Get the tangent of an angle
number
TRUNC
Truncate a number to a given precision
numbernum_digits

Statistical

AVEDEV
Get sum of squared deviations
number1number2...
AVERAGE
Get the average of a group of numbers
number1number2...
AVERAGEA
Get the average of a group of numbers and text
value1value2...
AVERAGEIF
Get the average of numbers that meet criteria
rangecriteriaaverage_range
AVERAGEIFS
Average cells that match multiple criteria
avg_rngrange1criteria1range2criteria2...
COMBIN
Get total number of combinations
numbernumber_chosen
COUNT
Count numbers
value1value2...
COUNTA
Count the number of non-blank cells
value1value2...
COUNTBLANK
Count cells that are blank
range
COUNTIF
Count cells that match criteria
rangecriteria
COUNTIFS
Count cells that match multiple criteria
range1criteria1range2criteria2...
DEVSQ
Get sum of squared deviations
number1number2...
FREQUENCY
Get the frequency of values in a data set
data_arraybins_array
GEOMEAN
Calculate geometric mean
number1number2...
HARMEAN
Calculate harmonic mean
number1number2...
LARGE
Get the nth largest value
arrayn
MAX
Get the largest value
number1number2...
MAXA
Return largest value.
value1value2...
MAXIFS
Get maximum value with criteria
max_rangerange1criteria1range2criteria2...
MEDIAN
Get the median of a group of numbers
number1number2...
MIN
Get the smallest value.
number1number2...
MINA
Return smallest value.
value1value2...
MINIFS
Get minimum value with criteria
min_rangerange1criteria1range2criteria2...
MODE
Get most frequently occurring number
number1number2...
MODE.MULT
Get most frequently occurring numbers
number1number2...
MODE.SNGL
Get most frequently occurring number
number1number2...
PERCENTILE
Get kth percentile
arrayk
PERCENTILE.EXC
Get kth percentile
arrayk
PERCENTILE.INC
Get kth percentile
arrayk
PERCENTRANK
Get percentile rank, inclusive
arrayxsignificance
PERCENTRANK.EXC
Get percentile rank, exclusive
arrayxsignificance
PERCENTRANK.INC
Get percentile rank, inclusive
arrayxsignificance
PERMUT
Get total number of permutations
numbernumber_chosen
QUARTILE
Get the quartile in a data set
arrayquart
QUARTILE.EXC
Get the quartile in a data set
arrayquart
QUARTILE.INC
Get the quartile in a data set
arrayquart
RANK
Rank a number against a range of numbers
numberarrayorder
RANK.AVG
Rank a number against a range of numbers
numberreforder
RANK.EQ
Rank a number against a range of numbers
numberreforder
SMALL
Get the nth smallest value
arrayn
STANDARDIZE
Calculate a normalized value (z-score)
xmeanstandard_dev
STDEV
Get the standard deviation in a sample
number1number2...
STDEV.P
Get standard deviation of population
number1number2...
STDEV.S
Get the standard deviation in a sample
number1number2...
STDEVA
Get standard deviation in a sample
number1number2...
STDEVP
Get standard deviation of population
number1number2...
STDEVPA
Get standard deviation for a population
number1number2...
TRIMMEAN
Calculate mean excluding outliers
arraypercent
VAR
Get variation of a sample
number1number2...
VAR.P
Get variation of population
number1number2...
VAR.S
Get variation of a sample
number1number2...
VARA
Get variation of a sample
number1number2...
VARP
Get variation of a population
number1number2...
VARPA
Get variation of a population
number1number2...

Text

CHAR
Get a character from a number
number
CLEAN
Strip non-printable characters from text
text
CODE
Get the code for a character
text
CONCAT
Join text values without delimiter
text1text2...
CONCATENATE
Join text together
text1text2text3...
DOLLAR
Convert a number to text in currency format
numberdecimals
EXACT
Compare two text strings
text1text2
FIND
Get the location of text in a string
find_textwithin_textstart_num
FIXED
Format number as text with fixed decimals
numberdecimalsno_commas
LEFT
Extract text from the left of a string
textnum_chars
LEN
Get the length of text.
text
LOWER
Convert text to lower case
text
MID
Extract text from inside a string
textstart_numnum_chars
PROPER
Capitalize the first letter in each word
text
REPLACE
Replace text based on location
old_textstart_numnum_charsnew_text
REPT
Repeat text as specified
textnumber_times
RIGHT
Extract text from the right of a string
textnum_chars
SEARCH
Get the location of text in a string
find_textwithin_textstart_num
SUBSTITUTE
Replace text based on content
textold_textnew_textinstance
TEXT
Convert a number to text in a number format
valueformat_text
TEXTJOIN
Join text values with a delimiter
delimiterignore_emptytext1text2...
TRIM
Remove extra spaces from text
text
UNICHAR
Get Unicode character by number
number
UNICODE
Get number from Unicode character
text
UPPER
Convert text to upper case
text
VALUE
Convert text to a number
text

Dynamic array

FILTER
Filters range with given criteria
arrayincludeif_empty
RANDARRAY
Get array of random numbers
rowscolumnsminmaxinteger
SEQUENCE
Get array of list of seuential numbers
rowscolumnsstartstep
SINGLE
Get single value with implicit intersection
value
SORT
Sorts range or array
arraysort_indexsort_orderby_col
SORTBY
Sorts range or array by column
arrayby_arraysort_orderarray/order...
UNIQUE
Extract unique values from range
arrayby_coloccurs_once

See also: 101 Excel functions you should know.