Excel tutorials

List all Syntax of Excel Functions

On this web site you can find basic Excel functions and their syntaxes are displayed.

 

Databases in Excel and their syntax

DAVERAGE  DAVERAGE(database,field,criteria)
DCOUNT DCOUNT(database,field,criteria)
DCOUNTA DCOUNTA(database,field,criteria)
DGET  DGET(database,field,criteria)
DMAX DMAX(database,field,criteria)
DMIN DMIN(database,field,criteria)
DPRODUCT DPRODUCT(database,field,criteria)
DSTDEV DSTDEV(database,field,criteria)
DSTDEVP DSTDEVP(database,field,criteria)
DSUM DSUM(database,field,criteria)
DVAR DVAR(database,field,criteria)
DVARP DVARP(database,field,criteria)
   

Date and Time functions in Excel and their syntax

DATE DATE(year,month,day)
DATEVALUE DATEVALUE(date_text)
DAY  DAY(serial_number)
DAYS360 DAYS360(start_date,end_date,method)
EDATE EDATE(start_date,months)
EOMONTH EOMONTH(start_date,months)
HOUR HOUR(serial_number)
MINUTE MINUTE(serial_number)
MONTH MONTH(serial_number)
NETWORKDAYS NETWORKDAYS(start_date,end_date,holidays)
NOW NOW( )
SECOND SECOND(serial_number)
TIME TIME(hour,minute,second)
TIMEVALUE TIMEVALUE(time_text)
TODAY TODAY( )
WEEKDAY WEEKDAY(serial_number,return_type)
WEEKNUM WEEKNUM(serial_num,return_type)
WORKDAY WORKDAY(start_date,days,holidays)
YEAR YEAR(serial_number)
YEARFRAC YEARFRAC(start_date,end_date,basis)
   

Financial functions in Excel and their syntax

ACCRINT ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
ACCRINTM ACCRINTM(issue,maturity,rate,par,basis)
AMORDEGRC AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)
AMORLINC AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis)
COUPDAYBS COUPDAYBS(settlement,maturity,frequency,basis)
COUPDAYS COUPDAYS(settlement,maturity,frequency,basis)
COUPDAYSNC COUPDAYSNC(settlement,maturity,frequency,basis)
COUPNCD COUPNCD(settlement,maturity,frequency,basis)
COUPNUM COUPNUM(settlement,maturity,frequency,basis)
COUPPCD COUPPCD(settlement,maturity,frequency,basis)
CUMIPMT CUMIPMT(rate,nper,pv,start_period,end_period,type)
CUMPRINC CUMPRINC(rate,nper,pv,start_period,end_period,type)
DB DB(cost,salvage,life,period,month)
DDB DDB(cost,salvage,life,period,factor)
DISC DISC(settlement,maturity,pr,redemption,basis)
DOLLARDE DOLLARDE(fractional_dollar,fraction)
DOLLARFR DOLLARFR(decimal_dollar,fraction)
DURATION DURATION(settlement,maturity,coupon,yld,frequency,basis)
EFFECT EFFECT(nominal_rate,npery)
FV FV(rate,nper,pmt,pv,type)
FVSCHEDULE FVSCHEDULE(principal,schedule)
INTRATE INTRATE(settlement,maturity,investment,redemption,basis)
IPMT IPMT(rate,per,nper,pv,fv,type)
IRR IRR(values,guess)
ISPMT ISPMT(rate,per,nper,pv)
MDURATION MDURATION(settlement,maturity,coupon,yld,frequency,basis)
MIRR MIRR(values,finance_rate,reinvest_rate)
NOMINAL NOMINAL(effect_rate,npery)
NPER NPER(rate,pmt,pv,fv,type)
NPV NPV(rate,value1,value2,...)
ODDFPRICE ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld,redemption,frequency,basis)
ODDFYIELD ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,basis)
ODDLPRICE ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,basis)
ODDLYIELD ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)
PMT PMT(rate,nper,pv,fv,type)
PPMT PPMT(rate,per,nper,pv,fv,type)
PRICE PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)
PRICEDISC PRICEDISC(settlement,maturity,discount,redemption,basis)
PRICEMAT PRICEMAT(settlement,maturity,issue,rate,yld,basis)
PV PV(rate,nper,pmt,fv,type)
RATE RATE(nper,pmt,pv,fv,type,guess)
RECEIVED RECEIVED(settlement,maturity,investment,discount,basis)
SLN SLN(cost,salvage,life)
SYD SYD(cost,salvage,life,per)
TBILLEQ TBILLEQ(settlement,maturity,discount)
TBILLPRICE TBILLPRICE(settlement,maturity,discount)
TBILLYIELD TBILLYIELD(settlement,maturity,pr)
VDB VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
XIRR XIRR(values,dates,guess)
XNPV XNPV(rate,values,dates)
YIELD YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
YIELDDISC YIELDDISC(settlement,maturity,pr,redemption,basis)
YIELDMAT YIELDMAT(settlement,maturity,issue,rate,pr,basis)
   

Information functions in Excel and their syntax

CELL CELL(info_type,reference)
ERROR.TYPE ERROR.TYPE(error_val)
INFO INFO(type_text)
ISBLANK ISBLANK(value)
ISERR ISERR(value)
ISERROR ISERROR(value)
ISEVEN ISEVEN(number)
ISLOGICAL ISLOGICAL(value)
ISNA ISNA(value)
ISNONTEXT ISNONTEXT(value)
ISNUMBER ISNUMBER(value)
ISODD ISODD(number)
ISREF ISREF(value)
ISTEXT ISTEXT(value)
N N(value)
NA NA()
TYPE TYPE(value)
   

Engineering functions in Excel and their syntax

BESSELI BESSELI(x,n)
BESSELJ BESSELJ(x,n)
BESSELK BESSELK(x,n)
BESSELY BESSELY(x,n)
BIN2DEC BIN2DEC(number)
BIN2HEX BIN2HEX(number,places)
BIN2OCT BIN2OCT(number,places)
COMPLEX COMPLEX(real_num,i_num,suffix)
CONVERT CONVERT(number,from_unit,to_unit)
DEC2BIN DEC2BIN(number,places)
DEC2HEX DEC2HEX(number,places)
DEC2OCT DEC2OCT(number,places)
DELTA DELTA(number1,number2)
ERF ERF(lower_limit,upper_limit)
ERFC ERFC(x)
GESTEP GESTEP(number,step)
HEX2BIN HEX2BIN(number,places)
HEX2DEC HEX2DEC(number)
HEX2OCT HEX2OCT(number,places)
IMABS IMABS(inumber)
IMAGINARY IMAGINARY(inumber)
IMARGUMENT IMARGUMENT(inumber)
IMCONJUGATE IMCONJUGATE(inumber)
IMCOS IMCOS(inumber)
IMDIV IMDIV(inumber1,inumber2)
IMEXP IMEXP(inumber)
IMLN IMLN(inumber)
IMLOG10 IMLOG10(inumber)
IMLOG2 IMLOG2(inumber)
IMPOWER IMPOWER(inumber,number)
IMPRODUCT IMPRODUCT(inumber1,inumber2,...)
IMREAL IMREAL(inumber)
IMSIN IMSIN(inumber)
IMSQRT IMSQRT(inumber)
IMSUB IMSUB(inumber1,inumber2)
IMSUM IMSUM(inumber1,inumber2,...)
OCT2BIN OCT2BIN(number,places)
OCT2DEC OCT2DEC(number)
OCT2HEX OCT2HEX(number,places)
   

Logical functions in Excel and their syntax

AND AND(logical1,logical2,...)
FALSE FALSE()
IF IF(logical_test,value_if_true,value_if_false)
NOT NOT(logical)
OR OR(logical1,logical2,...)
TRUE TRUE()
   

Math and trigonometry functions in Excel and their syntax

ABS ABS(number)
ACOS ACOS(number)
ACOSH ACOSH(number)
AGGREGATE AGGREGATE(function_num,options,ref1,...)
AGGREGATE(function,options,array,k)
ASIN ASIN(number)
ASINH ASINH(number)
ATAN ATAN(number)
ATAN2 ATAN2(x_num,y_num)
ATANH ATANH(number)
CEILING CEILING(number,significance)
COMBIN COMBIN(number,number_chosen)
COS COS(number)
COSH COSH(number)
DEGREES DEGREES(angle)
EVEN EVEN(number)
EXP EXP(number)
FACT FACT(number)
FACTDOUBLE FACTDOUBLE(number)
FLOOR FLOOR(number,significance)
GCD GCD(number1,number2,...)
INT INT(number)
LCM LCM(number1,number2,...)
LN LN(number)
LOG LOG(number,base)
LOG10 LOG10(number)
MDETERM MDETERM(array)
MINVERSE MINVERSE(array)
MMULT MMULT(array1,array2)
MOD MOD(number,divisor)
MROUND MROUND(number,multiple)
MULTINOMIAL MULTINOMIAL(number1,number2,...)
ODD ODD(number)
PI PI()
POWER POWER(number,power)
PRODUCT PRODUCT(number1,number2,...)
QUOTIENT QUOTIENT(numerator,denominator)
RADIANS RADIANS(angle)
RAND RAND()
RANDBETWEEN RANDBETWEEN(bottom,top)
ROMAN ROMAN(number,form)
ROUND ROUND(number,num_digits)
ROUNDDOWN ROUNDDOWN(number,num_digits)
ROUNDUP ROUNDUP(number,num_digits)
SERIESSUM SERIESSUM(x,n,m,coefficients)
SIGN SIGN(number)
SIN SIN(number)
SINH SINH(number)
SQRT SQRT(number)
SQRTPI SQRTPI(number)
SUBTOTAL SUBTOTAL(function_num,ref1,...)
SUM SUM(number1,number2,...)
SUMIF SUMIF(range,criteria,sum_range)
SUMPRODUCT SUMPRODUCT(array1,array2,array3,...)
SUMSQ SUMSQ(number1,number2,...)
SUMX2MY2 SUMX2MY2(array_x,array_y)
SUMX2PY2 SUMX2PY2(array_x,array_y)
SUMXMY2 SUMXMY2(array_x,array_y)
TAN TAN(number)
TANH TANH(number)
TRUNC TRUNC(number,num_digits)
   

Lookup and reference functions in Excel and their syntax

ADDRESS ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
AREAS AREAS(reference)
CHOOSE CHOOSE(index_num,value1,value2,...)
COLUMN COLUMN(reference)
COLUMNS COLUMNS(array)
GETPIVOTDATA GETPIVOTDATA(data_field,pivot_table,field,item,...)
HLOOKUP HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HYPERLINK HYPERLINK(link_location,friendly_name)
INDEX INDEX(array,row_num,column_num)
INDEX(reference,row_num,column_num,area_num)
INDIRECT INDIRECT(ref_text,a1)
LOOKUP LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP(lookup_value,array)
MATCH MATCH(lookup_value,lookup_array,match_type)
OFFSET OFFSET(reference,rows,cols,height,width)
ROW ROW(reference)
ROWS ROWS(array)
RTD RTD(progID,server,topic1,topic2,...)
TRANSPOSE TRANSPOSE(array)
VLOOKUP VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
   

Statistical functions in Excel and their syntax

AVEDEV AVEDEV(number1,number2,...)
AVERAGE AVERAGE(number1,number2,...)
AVERAGEA AVERAGEA(value1,value2,...)
BINOMDIST BINOMDIST(number_s,trials,probability_s,cumulative)
BETADIST BETADIST(x,alpha,beta,A,B)
BETAINV BETAINV(probability,alpha,beta,A,B)
CHIDIST CHIDIST(x,deg_freedom)
CHIINV CHIINV(probability,deg_freedom)
CHITEST CHITEST(actual_range,expected_range)
CONFIDENCE CONFIDENCE(alpha,standard_dev,size)
CORREL CORREL(array1,array2)
COUNT COUNT(value1,value2,...)
COUNTA COUNTA(value1,value2,...)
COUNTBLANK COUNTBLANK(range)
COUNTIF COUNTIF(range,criteria)
COVAR COVAR(array1,array2)
CRITBINOM CRITBINOM(trials,probability_s,alpha)
DEVSQ DEVSQ(number1,number2,...)
EXPONDIST EXPONDIST(x,lambda,cumulative)
FDIST FDIST(x,deg_freedom1,deg_freedom2)
FINV FINV(probability,deg_freedom1,deg_freedom2)
FISHER FISHER(x)
FISHERINV FISHERINV(y)
FORECAST FORECAST(x,known_y's,known_x's)
FREQUENCY FREQUENCY(data_array,bins_array)
FTEST FTEST(array1,array2)
GAMMADIST GAMMADIST(x,alpha,beta,cumulative)
GAMMAINV GAMMAINV(probability,alpha,beta)
GAMMALN GAMMALN(x)
GEOMEAN GEOMEAN(number1,number2,...)
GROWTH GROWTH(known_y's,known_x's,new_x's,const)
HARMEAN HARMEAN(number1,number2,...)
HYPGEOMDIST HYPGEOMDIST(sample_s,number_sample,population_s,number_pop)
INTERCEPT INTERCEPT(known_y's,known_x's)
KURT KURT(number1,number2,...)
LARGE LARGE(array,k)
LINEST LINEST(known_y's,known_x's,const,stats)
LOGEST LOGEST(known_y's,known_x's,const,stats)
LOGINV LOGINV(probability,mean,standard_dev)
LOGNORMDIST LOGNORMDIST(x,mean,standard_dev)
MAX MAX(number1,number2,...)
MAXA MAXA(value1,value2,...)
MEDIAN MEDIAN(number1,number2,...)
MIN MIN(number1,number2,...)
MINA MINA(value1,value2,...)
MODE MODE(number1,number2,...)
NEGBINOMDIST NEGBINOMDIST(number_f,number_s,probability_s)
NORMDIST NORMDIST(x,mean,standard_dev,cumulative)
NORMINV NORMINV(probability,mean,standard_dev)
NORMSDIST NORMSDIST(z)
NORMSINV NORMSINV(probability)
PEARSON PEARSON(array1,array2)
PERCENTILE PERCENTILE(array,k)
PERCENTRANK PERCENTRANK(array,x,significance)
PERMUT PERMUT(number,number_chosen)
POISSON POISSON(x,mean,cumulative)
PROB PROB(x_range,prob_range,lower_limit,upper_limit)
QUARTILE QUARTILE(array,quart)
RANK RANK(number,ref,order)
RSQ RSQ(known_y's,known_x's)
SKEW SKEW(number1,number2,...)
SLOPE SLOPE(known_y's,known_x's)
SMALL SMALL(array,k)
STANDARDIZE STANDARDIZE(x,mean,standard_dev)
STDEV STDEV(number1,number2,...)
STDEVA STDEVA(value1,value2,...)
STDEVP STDEVP(number1,number2,...)
STDEVPA STDEVPA(value1,value2,...)
STEYX STEYX(known_y's,known_x's)
TDIST TDIST(x,deg_freedom,tails)
TINV TINV(probability,deg_freedom)
TREND TREND(known_y's,known_x's,new_x's,const)
TRIMMEAN TRIMMEAN(array,percent)
TTEST  TTEST(array1,array2,tails,type)
VAR VAR(number1,number2,...)
VARA VARA(value1,value2,...)
VARP VARP(number1,number2,...)
VARPA VARPA(value1,value2,...)
WEIBULL WEIBULL(x,alpha,beta,cumulative)
ZTEST ZTEST(array,x,sigma)
   

Text functions in Excel and their syntax

ASC ASC(text)
BAHTTEXT BAHTTEXT(number)
CHAR CHAR(number)
CLEAN CLEAN(text)
CODE CODE(text)
CONCATENATE CONCATENATE(text1,text2,...)
DOLLAR DOLLAR(number,decimals)
EXACT EXACT(text1,text2)
FIND FIND(find_text,within_text,start_num)
FIXED FIXED(number,decimals,no_commas)
JIS JIS(text)
LEFT LEFT(text,num_chars)
LEN LEN(text)
LOWER LOWER(text)
MID MID(text,start_num,num_chars)
PHONETIC PHONETIC(reference)
PROPER PROPER(text)
REPLACE REPLACE(old_text,start_num,num_chars,new_text)
REPT REPT(text,number_times)
RIGHT RIGHT(text,num_chars)
SEARCH SEARCH(find_text,within_text,start_num)
SUBSTITUTE SUBSTITUTE(text,old_text,new_text,instance_num)
T T(value)
TEXT TEXT(value,format_text)
TRIM TRIM(text)
UPPER UPPER(text)
VALUE VALUE(text)