2022 updates MS Excel - Data Cleaning Course Enroll now  
ExcelTutorials

Excel Formulas & Functions in PDF List

11 Mins read

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

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.

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…