Excel Formulas & Functions in PDF List

yodalearning.com/tutorials/excel-formulas-pdf
Yoda Learning June 14, 2020
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

CONCATENATE =CONCATENATE(text1,text2,…) Joins several text

items into one text
item. Easier to use ‘&’
instead of the
function usually.
FLOOR =FLOOR(number,significance) Rounds a number
down, toward zero
BINOMDIST =BINOMDIST(number_s,trials,probability_s,cumulative) Returns the individual
term binomial

distribution probability

CHIDIST =CHIDIST(x,deg_freedom) Returns the one-

tailed probability of
the chi-squared
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)

LOGINV =LOGINV(probability,mean,standard_dev) Returns the inverse

of the lognormal
cumulative
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

1/10
Functions Excel Formulas Description

NORMSDIST =NORMSDIST(z) Returns the standard

normal cumulative
distribution
NORMSINV =NORMSINV(probability) Returns the inverse
of the standard
normal cumulative
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
STDEVP =STDEVP(number1,number2,…) Calculates standard
deviation based on

the entire population

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
BETAINV =BETAINV(probability,alpha,beta,A,B) Returns the inverse
of the cumulative
distribution function
for a specified beta
distribution
COVAR =COVAR(array1,array2) Returns covariance,
the average of the
products of paired
deviations
2/10
Functions Excel Formulas Description

CRITBINOM =CRITBINOM(trials,probability_s,alpha) Returns the smallest

value for which the
cumulative 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
GAMMAINV =GAMMAINV(probability,alpha,beta) Returns the inverse
of the gamma
cumulative
distribution
HYPGEOMDIST =HYPGEOMDIST(sample_s,number_sample,population_s,number_pop) Returns the
hypergeometric

distribution

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
WEIBULL =WEIBULL(x,alpha,beta,cumulative) Calculates variance
based on the entire
population, 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

3/10
Functions Excel Formulas Description

CUBEKPIMEMBER =CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption) Returns a key

performance
indicator (KPI)
name,
property, and
measure, and
displays the
name and
property in the
cell. A KPI is
a quantifiable
measurement,
such as
monthly gross
profit or
quarterly
employee
turnover, used
to monitor an
organization’s
performance.
CUBEMEMBER =CUBEMEMBER(connection,member_expression,caption) Returns a
member or
tuple in a
cube
hierarchy. Use
to validate
that the
member or
tuple exists in
the cube.
CUBEMEMBERPROPERTY =CUBEMEMBERPROPERTY(connection,member_expression,property) Returns the
value of a
member
property in the
cube. Use to
validate that a
member
name exists
within the
cube and to
return the
specified
property for
this member.
CUBERANKEDMEMBER =CUBERANKEDMEMBER(connection,set_expression,rank,caption) Returns the
nth, or
ranked,
member in a
set. Use to
return one or
more
elements in a
set, such as
the top sales
performer or
top 10
students.

4/10
Functions Excel Formulas Description

CUBESET =CUBESET(connection,set_expression,caption,sort_order,sort_by) Defines a

calculated set
of members
or tuples by
sending a set
expression to
the cube on
the server,
which creates
the set, and
then 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

DGET =DGET(database,field,criteria) Extracts from a database a single record that matches the

specified criteria

DSUM =DSUM(database,field,criteria) Adds the numbers in the field column of records in the

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

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

DPRODUCT =DPRODUCT(database,field,criteria) Multiplies the values in a particular field of records that match

the criteria in a database

DSTDEV =DSTDEV(database,field,criteria) Estimates the standard deviation based on a sample of

selected database entries

DSTDEVP =DSTDEVP(database,field,criteria) Calculates the standard deviation based on the entire

population of selected database entries

DVAR =DVAR(database,field,criteria) Estimates variance based on a sample from selected

database entries

DVARP =DVARP(database,field,criteria) Calculates variance based on the entire population of 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
5/10
Functions Excel Formulas Description

DATEVALUE =DATEVALUE(date_text) Converts a date in the form of text

to a serial number

DAY =DAY(serial_number) Converts a serial number to a day

of the 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

NOW =NOW() Returns the serial number of the

current date 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

TIMEVALUE =TIMEVALUE(time_text) Converts a time in the form of text

to a serial number

TODAY =TODAY() Returns the serial number of

today’s date

YEAR =YEAR(serial_number) Converts a serial number to a year

DAYS360 =DAYS360(start_date,end_date,method) Calculates the number of days

between two dates based on a
360-day year
EDATE =EDATE(start_date,months) Returns the serial number of the
date that is the indicated number of
months before or after the start
date
EOMONTH =EOMONTH(start_date,months) Returns the serial number of the
last day of the month before or

after a specified number of months

NETWORKDAYS =NETWORKDAYS(start_date,end_date,[holidays]) Returns the number of whole

workdays between two dates
NETWORKDAYS.INTL =NETWORKDAYS.INTL(start_date,end_date, Returns the number of whole
[weekend],[holidays]) workdays between two dates using
parameters to 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
WEEKNUM =WEEKNUM(serial_number,[return_type]) Converts a serial number to a
number representing where the

week falls numerically with a year

WORKDAY =WORKDAY(start_date, days, [holidays]) Returns the serial number of the

date before or after a specified
number of workdays
WORKDAY.INTL =WORKDAY.INTL(start_date,days,weekend,holidays) Returns the serial number of the
date before or after a specified
number of workdays using
parameters to indicate which and
6/10
Functions Excel Formulas Description

YEARFRAC =YEARFRAC(start_date,end_date,basis) Returns the year fraction

representing the number of whole
days between start_date and
end_date

Information Excel Formulas & Functions

Functions Excel Formulas Description

Returns information about the formatting, location, or contents of a
CELL =CELL(info_type, cell
[reference])
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

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], Specifies a logical test to perform

[value_if_false])
IFERROR =IFERROR(value, Returns a value you specify if a formula evaluates to an error;
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, Looks up values in a vector or array

array)– 2 types
7/10
Lookup & Reference Excel Formulas

Functions Excel Formulas Description

Returns a reference as text to a single
ADDRESS =ADDRESS(row_num, column_num, [abs_num], [a1], cell in a worksheet
[sheet_text])
COLUMN =COLUMN([reference]) Returns the column number of a
reference
COLUMNS =COLUMNS(array) Returns the number of columns in a
reference
HLOOKUP =HLOOKUP(lookup_value,table_array,row_index_num,
[range_lookup]) Looks in the top row of an array and
returns the value of the indicated cell
INDEX =INDEX(array,row_num,[column_num])– 2 types Uses an index to choose a value from
a reference or array
INDIRECT =INDIRECT(ref_text,a1) Returns a reference indicated by a text
value

MATCH =MATCH(lookup_value,lookup_array,match_type) Looks up values in a reference or array

OFFSET =OFFSET(reference,rows,cols,height,width) Returns a reference offset from a given

reference

ROW =ROW([reference]) Returns the row number of a reference

ROWS =ROWS(array) Returns the number of rows in a

reference
VLOOKUP =VLOOKUP(lookup_value,table_array,col_index_num, Looks in the first column of an array
[range_lookup]) and 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

GETPIVOTDATA =GETPIVOTDATA(data_field,pivot_table,field,item,…) Returns data stored in a PivotTable

report

HYPERLINK =HYPERLINK(link_location,friendly_name) Creates a shortcut or jump that opens

a 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
8/10
Functions Excel Formulas Description GE

CODE =CODE(text) Returns a numeric code for the first ST

character in a text string EP

DOLLAR =DOLLAR(number,decimals) Converts a number to text, using the $ =G

(dollar) currency format ES

FIXED =FIXED(number,decimals,no_commas) Formats a number as text with a fixed TE

number of decimals P(n

PHONETIC =PHONETIC(reference) Extracts the phonetic (furigana) characters um

from a text string ber,

REPT =REPT(text,number_times) Repeats text a given number of times ste

SUBSTITUTE =SUBSTITUTE(text,old_text,new_text,instance_num) Substitutes new text for old text in a text p)

string
Tes
T =T(value) Converts its arguments to text
ts
VALUE =VALUE(text) Converts a text argument to a number
wh
ASC =ASC(text) Changes full-width (double-byte) English
letters or katakana within a character string eth
to half-width (single-byte) characters
er a
BAHTTEXT =BAHTTEXT(number) Converts a number to text, using the ß
(baht) currency format nu

mb
Most Common Excel Formulas & Functions
er
Functions Excel Formulas Description
is
FIND =FIND(find_text,within_text,start_num) Finds one text value within another (case-
gre
sensitive)
ater
LEFT =LEFT(text,num_chars) Returns the leftmost characters from a text
tha
value
na
LEN =LEN(text) Returns the number of characters in a text
thre
string
sho
MID =MID(text,start_num,num_chars) Returns a specific number of characters from a
ld
text string starting at the position you specify
v
REPLACE =REPLACE(old_text,start_num,num_chars,new_text) Replaces characters within text alu
e
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

9/10
Functions Excel Formulas Description

ERF.PRECISE =ERF.PRECISE(X) Returns the error function

ERFC.PRECISE =ERFC.PRECISE(X) Returns the complementary ERF function integrated

between x and infinity

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

HEX2DEC =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

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

10/10