Excel Formulas & Functions in PDF List
What do you understand about the Advanced Excel Formulas and Basic Excel Functions?
This Blog will give you the Excel formulas PDF/list of the Key Functions of Excel. Excel Formulas PDF is a list of the most useful or extensively
used excel formulas in day-to-day working life with Excel. These formulas, we can use in Excel 2013. 2016 as well as 2019.
The Excel Functions covered here are: VLOOKUP, INDEX, MATCH, RANK, AVERAGE, SMALL, LARGE, LOOKUP, ROUND, COUNTIFS,
SUMIFS, FIND, DATE, and many more. Let’s dive right in…
Compatibility Excel Formulas & Functions
Functions Excel Formulas Description
Joins several text items into one text item. Easier to use
CONCATENATE =CONCATENATE(text1,text2,…)
‘&’ instead of the function usually.
FLOOR =FLOOR(number,significance) Rounds a number down, toward zero
Returns the individual term binomial distribution
BINOMDIST =BINOMDIST(number_s,trials,probability_s,cumulative)
probability
Returns the one-tailed probability of the chi-squared
CHIDIST =CHIDIST(x,deg_freedom)
distribution
CHIINV =CHITEST(actual_range,expected_range) Returns the test for independence
CONFIDENCE =CONFIDENCE(alpha,standard_dev,size) Returns the confidence interval for a population mean
FTEST =FTEST(array1,array2)
Functions Excel Formulas Description
Returns the inverse of the lognormal cumulative
LOGINV =LOGINV(probability,mean,standard_dev)
distribution
LOGNORMDIST =LOGNORMDIST(x,mean,standard_dev) Returns the cumulative lognormal distribution
MODE =MODE(number1,number2,…) Returns the most common value in a data set
NORMDIST =NORMDIST(x,mean,standard_dev,cumulative) Returns the normal cumulative distribution
NORMINV =NORMINV(probability,mean,standard_dev) Returns the inverse of the normal cumulative distribution
NORMSDIST =NORMSDIST(z) Returns the standard normal cumulative distribution
Returns the inverse of the standard normal cumulative
NORMSINV =NORMSINV(probability)
distribution
PERCENTILE =PERCENTILE(array,k) Returns the k-th percentile of values in a range
PERCENTRANK =PERCENTRANK(array,x,significance) Returns the percentage rank of a value in a data set
POISSON =POISSON(x,mean,cumulative) Returns the Poisson distribution
QUARTILE =QUARTILE(array,quart) Returns the quartile of a data set
RANK =RANK(number,ref,order) Returns the rank of a number in a list of numbers
STDEV =STDEV(number1,number2,…) Estimates standard deviation based on a sample
Calculates standard deviation based on the entire
STDEVP =STDEVP(number1,number2,…)
population
Functions Excel Formulas Description
TDIST =TDIST(x,deg_freedom,tails) Returns the Student’s t-distribution
TINV =TINV(probability,deg_freedom) Returns the inverse of the Student’s t-distribution
VAR =VAR(number1,number2,…) Estimates variance based on a sample
VARP =VARP(number1,number2,…) Calculates variance based on the entire population
FINV =FINV(probability,deg_freedom1,deg_freedom2) Returns the inverse of the F probability distribution
FORECAST =FORECAST(x,known_y’s,known_x’s) Returns a value along a linear trend
BETADIST =BETADIST(x,alpha,beta,A,B) Returns the beta cumulative distribution function
Returns the inverse of the cumulative distribution function
BETAINV =BETAINV(probability,alpha,beta,A,B)
for a specified beta distribution
Returns covariance, the average of the products of paired
COVAR =COVAR(array1,array2)
deviations
Returns the smallest value for which the cumulative
CRITBINOM =CRITBINOM(trials,probability_s,alpha) binomial distribution is less than or equal to a criterion
value
EXPONDIST =EXPONDIST(x,lambda,cumulative) Returns the exponential distribution
POISSON =POISSON(x,mean,cumulative) Returns the Poisson distribution
FDIST =FDIST(x,deg_freedom1,deg_freedom2) Returns the F probability distribution
GAMMADIST =GAMMADIST(x,alpha,beta,cumulative) Returns the gamma distribution
Functions Excel Formulas Description
GAMMAINV =GAMMAINV(probability,alpha,beta) Returns the inverse of the gamma cumulative distribution
=HYPGEOMDIST(sample_s,number_sample,population_s,nu
HYPGEOMDIST Returns the hypergeometric distribution
mber_pop)
NEGBINOMDIST =NEGBINOMDIST(number_f,number_s,probability_s) Returns the negative binomial distribution
TTEST =TTEST(array1,array2,tails,type) Returns the probability associated with a Student’s t-test
Calculates variance based on the entire population,
WEIBULL =WEIBULL(x,alpha,beta,cumulative)
including numbers, text, and logical values
ZTEST =ZTEST(array,x,sigma) Returns the one-tailed probability-value of a z-test
Cube Excel Formulas & Functions
Functions Excel Formulas Description
Returns a key performance indicator (KPI) name, prop
CUBEKPIMEMBER =CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption) property in the cell. A KPI is a quantifiable measureme
employee turnover, used to monitor an organization’s p
Returns a member or tuple in a cube hierarchy. Use to
CUBEMEMBER =CUBEMEMBER(connection,member_expression,caption)
member or tuple exists in the cube.
Functions Excel Formulas Description
Returns the value of a member property in the cube. Us
=CUBEMEMBERPROPERTY(connection,member_expression,pr
CUBEMEMBERPROPERTY a member name exists within the cube and to return the
operty)
property for this member.
=CUBERANKEDMEMBER(connection,set_expression,rank,capti Returns the nth, or ranked, member in a set. Use to retu
CUBERANKEDMEMBER
on) elements in a set, such as the top sales performer or top
Defines a calculated set of members or tuples by sendin
CUBESET =CUBESET(connection,set_expression,caption,sort_order,sort_by) expression to the cube on the server, which creates the
returns that set to Microsoft Office Excel.
CUBESETCOUNT =CUBESETCOUNT(set) Returns the number of items in a set.
CUBEVALUE =CUBEVALUE(connection,member_expression1,…) Returns an aggregated value from a cube
Database Excel Formulas & Functions
Functions Excel Formulas Description
Extracts from a database a single record that matches the
DGET =DGET(database,field,criteria)
specified criteria
Adds the numbers in the field column of records in the
DSUM =DSUM(database,field,criteria)
database that match the criteria
DAVERAGE =DAVERAGE(database,field,criteria) Returns the average of selected database entries
DCOUNT =DCOUNT(database,field,criteria) Counts the cells that contain numbers in a database
Functions Excel Formulas Description
DCOUNTA =DCOUNTA(database,field,criteria) Counts nonblank cells in a database
DMAX =DMAX(database,field,criteria) Returns the maximum value from selected database entries
DMIN =DMIN(database,field,criteria) Returns the minimum value from selected database entries
Multiplies the values in a particular field of records that
DPRODUCT =DPRODUCT(database,field,criteria)
match the criteria in a database
Estimates the standard deviation based on a sample of
DSTDEV =DSTDEV(database,field,criteria)
selected database entries
Calculates the standard deviation based on the entire
DSTDEVP =DSTDEVP(database,field,criteria)
population of selected database entries
Estimates variance based on a sample from selected
DVAR =DVAR(database,field,criteria)
database entries
Calculates variance based on the entire population of
DVARP =DVARP(database,field,criteria)
selected database entries
Date & Time Excel Formulas & Functions
Functions Excel Formulas Description
DATE =DATE(year,month,day) Returns the serial number of a particular date
Converts a date in the form of text to a serial
DATEVALUE =DATEVALUE(date_text)
number
DAY =DAY(serial_number) Converts a serial number to a day of the
Functions Excel Formulas Description
month
HOUR =HOUR(serial_number) Converts a serial number to an hour
MINUTE =MINUTE(serial_number) Converts a serial number to a minute
MONTH =MONTH(serial_number) Converts a serial number to a month
Returns the serial number of the current date
NOW =NOW()
and time
SECOND =SECOND(serial_number) Converts a serial number to a second
TIME =TIME(hour,minute,second) Returns the serial number of a particular time
Converts a time in the form of text to a serial
TIMEVALUE =TIMEVALUE(time_text)
number
TODAY =TODAY() Returns the serial number of today’s date
YEAR =YEAR(serial_number) Converts a serial number to a year
Calculates the number of days between two
DAYS360 =DAYS360(start_date,end_date,method)
dates based on a 360-day year
Returns the serial number of the date that is
EDATE =EDATE(start_date,months) the indicated number of months before or
after the start date
Returns the serial number of the last day of
EOMONTH =EOMONTH(start_date,months)
the month before or after a specified number
Functions Excel Formulas Description
of months
Returns the number of whole workdays
NETWORKDAYS =NETWORKDAYS(start_date,end_date,[holidays])
between two dates
Returns the number of whole workdays
between two dates using parameters to
NETWORKDAYS.INTL =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
indicate which and how many days are
weekend days
WEEKDAY =WEEKDAY(serial_number,[return_type]) Converts a serial number to a day of the week
Converts a serial number to a number
WEEKNUM =WEEKNUM(serial_number,[return_type]) representing where the week falls
numerically with a year
Returns the serial number of the date before
WORKDAY =WORKDAY(start_date, days, [holidays])
or after a specified number of workdays
Returns the serial number of the date before
or after a specified number of workdays using
WORKDAY.INTL =WORKDAY.INTL(start_date,days,weekend,holidays)
parameters to indicate which and how many
days are weekend days
Returns the year fraction representing the
YEARFRAC =YEARFRAC(start_date,end_date,basis) number of whole days between start_date and
end_date
Information Excel Formulas & Functions
Functions Excel Formulas Description
CELL =CELL(info_type, [reference]) Returns information about the formatting, location, or contents of a cell
ISBLANK =ISBLANK(value) Returns TRUE if the value is blank
ISERROR =ISERROR(value) Returns TRUE if the value is any error value
ISNONTEXT =ISNONTEXT(value) Returns TRUE if the value is not text
ISNUMBER =ISNUMBER(value) Returns TRUE if the value is a number
ISTEXT =ISTEXT(value) Returns TRUE if the value is text
ERROR.TYPE =ERROR.TYPE(error_val) Returns a number corresponding to an error type
INFO =INFO(type_text) Returns information about the current operating environment
ISERR =ISERR(value) Returns TRUE if the value is any error value except #N/A
ISEVEN =ISEVEN(number) Returns TRUE if the number is even
ISLOGICAL =ISLOGICAL(value) Returns TRUE if the value is a logical value
ISNA =ISNA(value) Returns TRUE if the value is the #N/A error value
ISODD =ISODD(number) Returns TRUE if the number is odd
ISREF =ISREF(value) Returns TRUE if the value is a reference
N =N(value) Returns a value converted to a number
NA =NA() Returns the error value #N/A
Functions Excel Formulas Description
TYPE =TYPE(value) Returns a number indicating the data type of a value
Logical Excel Formulas & Functions
Functions Excel Formulas Description
AND =AND(logical1,logical2,…) Returns TRUE if all of its arguments are TRUE
FALSE =FALSE Returns the logical value FALSE
IF =IF(logical_test, [value_if_true], [value_if_false]) Specifies a logical test to perform
Returns a value you specify if a formula evaluates to an error;
IFERROR =IFERROR(value, value_if_error)
otherwise, returns the result of the formula
NOT =NOT(logical) Reverses the logic of its argument
OR =OR(logical1,logical2,…) Returns TRUE if any argument is TRUE
TRUE =TRUE Returns the logical value TRUE
LOOKUP =LOOKUP(lookup_value, array)– 2 types Looks up values in a vector or array
Lookup & Reference Excel Formulas
Functions Excel Formulas Description
Returns a reference as text to a single cell
ADDRESS =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
in a worksheet
COLUMN =COLUMN([reference]) Returns the column number of a reference
Returns the number of columns in a
COLUMNS =COLUMNS(array)
reference
=HLOOKUP(lookup_value,table_array,row_index_num, Looks in the top row of an array and
HLOOKUP
[range_lookup]) returns the value of the indicated cell
Uses an index to choose a value from a
INDEX =INDEX(array,row_num,[column_num])– 2 types
reference or array
Returns a reference indicated by a text
INDIRECT =INDIRECT(ref_text,a1)
value
MATCH =MATCH(lookup_value,lookup_array,match_type) Looks up values in a reference or array
Returns a reference offset from a given
OFFSET =OFFSET(reference,rows,cols,height,width)
reference
ROW =ROW([reference]) Returns the row number of a reference
ROWS =ROWS(array) Returns the number of rows in a reference
Looks in the first column of an array and
VLOOKUP =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) moves across the row to return the value
of a cell
CHOOSE =CHOOSE(index_num,value1,value2,…) Chooses a value from a list of values
Functions Excel Formulas Description
GETPIVOTDATA =GETPIVOTDATA(data_field,pivot_table,field,item,…) Returns data stored in a PivotTable report
Creates a shortcut or jump that opens a
HYPERLINK =HYPERLINK(link_location,friendly_name) document stored on a network server, an
intranet, or the Internet
TRANSPOSE =TRANSPOSE(array) Returns the transpose of an array
AREAS =AREAS(reference) Returns the number of areas in a reference
Text Excel Formulas & Functions
Functions Excel Formulas Description
EXACT =EXACT(text1,text2) Checks to see if two text values are identical
LOWER =LOWER(text) Converts text to lowercase
PROPER =PROPER(text) Capitalizes the first letter in each word of a text value
TRIM =TRIM(text) Removes spaces from text
UPPER =UPPER(text) Converts text to uppercase
CHAR =CHAR(number) Returns the character specified by the code number
CLEAN =CLEAN(text) Removes all nonprintable characters from text
CODE =CODE(text) Returns a numeric code for the first character in a text string
Functions Excel Formulas Description
Converts a number to text, using the $ (dollar) currency
DOLLAR =DOLLAR(number,decimals)
format
FIXED =FIXED(number,decimals,no_commas) Formats a number as text with a fixed number of decimals
PHONETIC =PHONETIC(reference) Extracts the phonetic (furigana) characters from a text string
REPT =REPT(text,number_times) Repeats text a given number of times
SUBSTITUTE =SUBSTITUTE(text,old_text,new_text,instance_num) Substitutes new text for old text in a text string
T =T(value) Converts its arguments to text
VALUE =VALUE(text) Converts a text argument to a number
Changes full-width (double-byte) English letters or katakana
ASC =ASC(text) within a character string to half-width (single-byte)
characters
Converts a number to text, using the ß (baht) currency
BAHTTEXT =BAHTTEXT(number)
format
Most Common Excel Formulas & Functions
Functions Excel Formulas Description
FIND =FIND(find_text,within_text,start_num) Finds one text value within another (case-sensitive)
LEFT =LEFT(text,num_chars) Returns the leftmost characters from a text value
Functions Excel Formulas Description
LEN =LEN(text) Returns the number of characters in a text string
Returns a specific number of characters from a text string
MID =MID(text,start_num,num_chars)
starting at the position you specify
REPLACE =REPLACE(old_text,start_num,num_chars,new_text) Replaces characters within text
RIGHT =RIGHT(text,num_chars) Returns the rightmost characters from a text value
SEARCH =SEARCH(find_text,within_text,start_num) Finds one text value within another (not case-sensitive)
Engineering Excel Formulas & Functions
Functions Excel Formulas Description
CONVERT =CONVERT(number,from_unit,to_unit) Converts a number from one measurement system to another
DELTA =DELTA(number1,number2) Tests whether two values are equal
ERF =ERF(lower_limit,upper_limit) Returns the error function
ERFC =ERFC(x) Returns the complementary error function
GESTEP =GESTEP(number,step) Tests whether a number is greater than a threshold value
ERF.PRECISE =ERF.PRECISE(X) Returns the error function
ERFC.PRECISE =ERFC.PRECISE(X) Returns the complementary ERF function integrated between x and infinity
Functions Excel Formulas Description
BESSELI =BESSELI(x,n) Returns the modified Bessel function In(x)
BESSELJ =BESSELJ(x,n) Returns the Bessel function Jn(x)
BESSELK =BESSELK(x,n) Returns the modified Bessel function Kn(x)
BESSELY =BESSELY(x,n) Returns the Bessel function Yn(x)
BIN2DEC =BIN2DEC(number) Converts a binary number to decimal
BIN2HEX =BIN2HEX(number,places) Converts a binary number to hexadecimal
DEC2OCT =DEC2OCT(number,places) Converts a decimal number to octal
HEX2BIN =HEX2BIN(number,places) Converts a hexadecimal number to binary
0 =HEX2DEC(number) Converts a hexadecimal number to decimal
HEX2OCT =HEX2OCT(number,places) Converts a hexadecimal number to octal
IMABS =IMABS(inumber) Returns the absolute value (modulus) of a complex number
IMAGINARY =IMAGINARY(inumber) Returns the imaginary coefficient of a complex number
IMARGUMENT =IMARGUMENT(inumber) Returns the argument theta, an angle expressed in radians
IMCONJUGATE =IMCONJUGATE(inumber) Returns the complex conjugate of a complex number
IMCOS =IMCOS(inumber) Returns the cosine of a complex number
IMDIV =IMDIV(inumber1,inumber2) Returns the quotient of two complex numbers
Functions Excel Formulas Description
IMEXP =IMEXP(inumber) Returns the exponential of a complex number
IMLN =IMLN(inumber) Returns the natural logarithm of a complex number
IMLOG10 =IMLOG10(inumber) Returns the base-10 logarithm of a complex number
IMLOG2 =IMLOG2(inumber) Returns the base-2 logarithm of a complex number
IMPOWER =IMPOWER(inumber,number) Returns a complex number raised to an integer power
IMPRODUCT =IMPRODUCT(inumber1,inumber2,…) Returns the product of complex numbers
IMREAL =IMREAL(inumber) Returns the real coefficient of a complex number
IMSIN =IMSIN(inumber) Returns the sine of a complex number
IMSQRT =IMSQRT(inumber) Returns the square root of a complex number
IMSUB =IMSUB(inumber1,inumber2) Returns the difference between two complex numbers
IMSUM =IMSUM(inumber1,inumber2,…) Returns the sum of complex numbers
OCT2BIN =OCT2BIN(number,places) Converts an octal number to binary
OCT2DEC =OCT2DEC(number) Converts an octal number to decimal
OCT2HEX =OCT2HEX(number,places) Converts an octal number to hexadecimal
