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.

PDF GUIDE BOOKS
- Download Excel Shortcuts for Windows & Mac
- Basic to Advanced Excel Formulas PDF Guide
This Book will help you to get Excel Skills & Boost your career.
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
|
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
|
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
|

PDF GUIDE BOOKS
- Download Excel Shortcuts for Windows & Mac
- Basic to Advanced Excel Formulas PDF Guide
This Book will help you to get Excel Skills & Boost your career.
Cube Excel Formulas & Functions
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.
|
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
|
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,[weekend],[holidays])
|
Returns the number of whole 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 how many days are weekend days
|
YEARFRAC
|
=YEARFRAC(start_date,end_date,basis)
|
Returns the year fraction representing the number of whole days between start_date and end_date
|

PDF GUIDE BOOKS
- Download Excel Shortcuts for Windows & Mac
- Basic to Advanced Excel Formulas PDF Guide
This Book will help you to get Excel Skills & Boost your career.
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
|
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
|
IFERROR
|
=IFERROR(value, value_if_error)
|
Returns a value you specify if a formula evaluates to an 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
|
---|---|---|
ADDRESS
|
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
|
Returns a reference as text to a single cell in a worksheet
|
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,[range_lookup])
|
Looks in the first column of an array 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
|
CODE
|
=CODE(text)
|
Returns a numeric code for the first character in a text string
|
DOLLAR
|
=DOLLAR(number,decimals)
|
Converts a number to text, using the $ (dollar) currency 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
|
ASC
|
=ASC(text)
|
Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
|
BAHTTEXT
|
=BAHTTEXT(number)
|
Converts a number to text, using the ß (baht) currency 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
|
LEN
|
=LEN(text)
|
Returns the number of characters in a text string
|
MID
|
=MID(text,start_num,num_chars)
|
Returns a specific number of characters from a text string 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
|
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
|
Level up your Excel skills and transform your data into powerful insights. Join us in Corporate Excel Training in Mumbai, and Corporate Excel Training in Delhi for a comprehensive training session that covers everything from the basics to advanced techniques. Whether you’re a beginner or an intermediate user, this training will give you the skills and confidence to tackle any project with ease.

PDF GUIDE BOOKS
- Download Excel Shortcuts for Windows & Mac
- Basic to Advanced Excel Formulas PDF Guide
This Book will help you to get Excel Skills & Boost your career.