Below is the list of Most Important 100+ Excel Functions along with its usage and formula. These Excel Functions are subdivided into the following –
 Financial Functions
 Logical Functions
 Text Functions
 Date and Time Functions
 Lookup & Reference Functions
 Math Functions
 Statistical Functions
 Information Functions
Financial Functions in Excel
FV in Excel  Calculates the future value of an investment at a specified rate of interest 
rate
nper
pmt
pv
type

IPMT  Calculates the interest of an investment for a specific period. 
rate
per
nper
pv
fv
type

IRR  This excel function is simply the Internal Rate of Return for your investment in a series of cash flows. 
values
guess

MIRR in Excel MIRR In ExcelMIRR or (modified internal rate of return) in excel is an inbuild financial function to calculate the MIRR for the cash flows supplied with a period. It takes the initial investment, interest rate and the interest earned from the earned amount and returns the MIRR.read more  It is the Modified Internal Rate of return, which returns the percentage by taking into consideration both the Finance Rate & Reinvestment Rate. 
values
finance_rate
reinvest_rate

NPER  It is simply the number of installments or EMI’s required to clear off the loan. 
rate
pmt
pv
fv
type

NPV  Net Present Value is the value of future cash inflows and outflows by taking into consideration of present discount rate. 
rate
value1
value2

PMT PMTPMT function is an advanced financial function to calculate the monthly payment against the simple loan amount. You have to provide basic information, including loan amount, interest rate, and duration of payment, and the function will calculate the payment as a result.read more  This excel function returns the per month payment required to return the loan. 
rate
nper
pv
fv
type

PPMT in Excel  It returns only the principal amount for an investment or loan 
per
nper
pv
fv
type

PRICE in Excel  It returns the redemption value per 100 face value. 
settlement
maturity
rate
yld
redemption
frequency
basis

PV in Excel  It gives the present value of an investment by taking into consideration of constant interest rate and payments. 
rate
nper
pmt
fv
type

RATE in Excel  It returns the interest rate for an investment or loan. 
nper
pmt
pv
fv
type
guess

XIRR  It returns the internal rate of return without any specific periodic period. 
values
dates
guess

YIELD  It estimates the amount earned on investment security over a period of time. 
Logical Functions in Excel
AND  If all the supplied conditions are true, it will return TRUE or else FALSE. 
logical1
logical2

IF  This tests the logic given; it the condition is true, then we can get whatever the result we want if FALSE also we can other than the TRUE result value. 
logical_test
value_if_true
value_if_false

IFERROR in Excel IFERROR In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.read more  This returns the specific value if the applied formula returns an error result. 
value
value_if_error

NOT  This works indirectly, if the value is TRUE, it will result in FLASE, and if the result is FALSE, it will return TRUE. 
logical

OR in Excel  This is just like the AND function in excel, but if anyone of the supplied is true, it will return TRUE. 
logical1
logical2

TRUE  This is the formula to get TRUE value without manual typing 
–

Text Functions in Excel
CHAR  This excel function converts numbers to characters based on the American Standard Code For Information Interchange (ASCII). 
number

CLEAN  It removes only nonprintable values. 
text

CODE  This returns the numerical code for an alphabetic word. 
text

EXACT in Excel  This tests two cell values, whether they are exact, are not. 
text1
text2

LEFT  This will get the value from the lefthand side of a string based on the number of characters we specify. 
text
num_chars

LEN in Excel  This excel function returns the numbers of characters in a cell. Space is also a character. 
text

MID in Excel  This can fetch the data from the middle of the supplied value. 
text
start_num
num_chars

PROPER  This will arrange the text properly by changing the first character to an uppercase letter. 
text

REPLACE  This excel function replaces the text with new text from the specified position 
old_text
start_num
num_chars
new_text

REPT  This will repeat the supplied character based on the number we specify. 
text
number_times

RIGHT in Excel RIGHT In ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more  This will get the value from the righthand side of a string based on the number of characters we specify. 
text
num_chars

SEARCH in Excel SEARCH In ExcelSearch function gives the position of a substring in a given string when we give a parameter of the position to search from. As a result, this formula requires three arguments. The first is the substring, the second is the string itself, and the last is the position to start the search.read more  This will return the position of the supplied character in the specific value. 
find_text
within_text
start_num

SUBSTITUTE in Excel  This excel function substitutes the existing character with a new character. 
text
old_text
new_text
instance_num

TEXT  This will convert the value to text based on the format we specify. 
value
format_text

TRIM in Excel  This eliminates unnecessary spaces or trailing spaces. 
text

VALUE in Excel  This will convert the numerical values, which are in text format to number format. 
text

Date and Time Functions in Excel
Lookup & Reference Functions in Excel
ADDRESS  This will create the cell reference or address of the cell as a text value. 
row_num
column_num
abs_num
a1
sheet_text

CHOOSE  This excel function chooses the value from various values. 
index_num
value1
value2

COLUMN  This function returns the selected column number 
reference

COLUMNS in Excel  It will return how many columns are selected 
array

GETPIVOTDATA  It extracts data from the pivot table from the specified row and from the specified column 
data_field
pivot_table
field1
item1

HLOOKUP  It extracts data which is there horizontally based on the lookup value. 
lookup_value
table_array
row_index_num
range_lookup

HYPERLINKHYPERLINKA hyperlink is an inbuilt function in excel used to create hyperlinks for a particular cell. When the hyperlink is created, it redirects to a specific webpage. The hyperlink formula has two arguments, one is the URL while the other is the name we provide to the URL.read more  This creates a URL to open the data, which is where your PC or laptop. 
link_location
friendly_name

INDEX  It returns the value from the lookup value. 
array
row_num
column_num
area_num

INDIRECT Function in Excel  This indirectly refers to the other cell from a different cell. 
ref_text
a1

LOOKUP in Excel LOOKUP In ExcelThe LOOKUP excel function searches a value in a range (single row or column). It returns a corresponding match from the exact position of another range. The corresponding match is a piece of information associated with the value being searched.read more  It looks for a value in one row and one column 
lookup_value
lookup_vector
result_vector

MATCH  This returns the row number of a lookup value from the array. 
lookup_value
lookup_array
match_type

OFFSET in Excel  This function returns a reference to a range. 
reference
rows
cols
height
width

ROW  Returns the firstrow number in the reference provided 
reference

Transpose in Excel  This function is used to switch cells between rows and columns. 
array

VLOOKUP  This excel function returns the value of a lookup value from another table, which is there vertically. 
lookup_value
table_array
col_index_num
range_lookup

Math Functions in Excel
ABS  This function converts all the negative numbers to positive numbers. 
number

AGGREGATE  This excel function supplies some of the important formulas like Average, Count, Max, Min, and many other functions by ignoring error values 
function_num
options
array
k

CEILING Excel Function  Returns a rounded number based on a multiple significance. 
number
significance
mode

COMBIN Excel Function  This function returns the number of combinations for a specified number of items. 
number
number_chosen

EVEN  This excel function converts all the odd numbers to even numbers. 
number

EXP Excel Function  It raises the supplied number to the power of e. 
number

INT in Excel  This function is responsible for returning a number’s integer portion. 
number

LN Excel LN ExcelLN function calculates the logarithm of a given number which takes a single number as the argument and gives the result as the logarithm.read more  This function is used to calculate the natural logarithm of a number. 
number

LOG in Excel  It returns the logarithm of a number to the base user gives 
number
base

ODD in Excel  This function converts all the even numbers to ODD numbers. 
number

POWER in Excel  This function raises the base number to a different number of times at the top of the base. 
number
power

PRODUCT in Excel PRODUCT In ExcelProduct excel function is an inbuilt mathematical function which is used to calculate the product or multiplication of the given numbers. If you provide this formula arguments as 2 and 3 as =PRODUCT(2,3) then the result displayed is 6.read more  This function simply does the multiplication of two supplied numbers. 
number1
number2

RAND in Excel  It returns values that are greater than zero but less than 1. This is a volatile function. 
–

ROUND  It either rounds up or rounds down to a specified number of decimals. 
number
num_digits

ROUNDUP in ExcelROUNDUP In ExcelThe ROUNDUP function in Excel is a builtin function that is used to calculate the rounded value of a number to the highest degree. It rounds numbers away from zero, thus if the input to this function is =ROUNDUP(0.40,1), the output will be 0.4.read more  This excel function rounds the given floatingpoint number to a number of decimal places provided. 
number
num_digits

ROUNDDOWN in ExcelROUNDDOWN In ExcelROUNDDOWN function is a builtin function in Microsoft Excel which rounds off the given number to the nearest lower number w.r.t the given number. This function can be accessed by typing the keyword =ROUNDDOWN( in any cell).read more  To round off the given number, this function is used. 
number
num_digits

SIGN  This function calculates the sign of a supplied number. 
number

SIN in Excel  This function calculates the SIN of an angle 
number

SQRT in ExcelSQRT In ExcelThe Square Root function is an arithmetic function built into Excel that is used to determine the square root of a given number. To use this function, type the term =SQRT and hit the tab key, which will bring up the SQRT function. Moreover, this function accepts a single argument.read more  Find a number of positive square roots 
number

SUBTOTAL in ExcelSUBTOTAL In ExcelThe Excel Subtotal function is a builtin function that returns the subtotal of a given data table or data sets. This formula requires two inputs, the first of which is the function number and the second of which is the range.read more  This function ignores all the hidden rows and gives the calculation only for the visible rows. 
function_num
ref1

SUM  This excel function adds all the numerical values which are there in the range. 
number1
number2

SUMIF in Excel  This function adds the numbers based on the single criteria given by the user. 
range
criteria
sum_range

TAN  This function simultaneously does the summation as well as multiplication corresponding to the values. 
array1
array2
array3

TAN in Excel  This function returns the tangent of an angle. 
number

TANH in ExcelTANH In ExcelTANH is an inbuilt trigonometric function hyperbolic tangent which calculates the ratio of the hyperbolic sine and cosine functions. It takes single argument as an input.read more  TANH function gives the hyperbolic tangent of a given number, and the number is considered as an angle to radians. 
number

Statistical Functions in Excel
AVERAGE  This excel function returns the average value of the supplied numbers. 
number1
number2

CORREL Excel Function  This is nothing but a correlation between two data sets. 
array1
array2

COUNT  This function counts all the numerical values in the supplied range. 
value1
value2

COUNTIF  This excel function counts only the value w specify 
range
criteria

F.TEST in Excel  This function is used to decide if two populations having normal distributionNormal DistributionNormal Distribution is a bellshaped frequency distribution curve which helps describe all the possible values a random variable can take within a given range with most of the distribution area is in the middle and few are in the tails, at the extremes. This distribution has two key parameters: the mean (µ) and the standard deviation (σ) which plays a key role in assets return calculation and in risk management strategy.read more have similar variances or the standard deviation. 
array1
array2

FORECAST Excel Formula  This function calculates or estimates the sales values based on previous trends. 
x
known_ys
known_xs

FREQUENCY in Excel  This excel function shows how often a certain value is occurring in an array 
data_array
bins_array

GROWTH  It calculates the exponential growthCalculates The Exponential GrowthExponential Growth refers to the increase due to compounding of the data over time and follows a curve representing an exponential function. Exponential growth formula: Final value = Initial value * (1 + Annual Growth Rate/No of Compounding ) No. of years * No. of compoundingread more of given numbers based on two data sets. 
known_ys
known_xs
new_xs
const

LARGE  It is responsible for returning the nth largest value from a given set of values in the spreadsheet. 
array
k

LINEST ExcelLINEST ExcelThe builtin LINEST Function in Excel calculates statistics for a line by the leastsquares regression method & returns an array that defines the line proving to be wellsuited for the given data. read more  It is used to calculate a line’s stats. 
known_ys
known_xs
const
stats

MAX in Excel  This function is given the maximum value from the supplied numbers. 
number2

MEDIAN  This function returns the middle value of the supplied numbers. 
number1
number2

MIN in Excel  This function is given the minimum value from the supplied numbers. 
number1
number2

MODE  This excel function returns the MODE for each set of data. 
number1
number2

NORMDIST  It estimates the probability of the variables plotting below or above a certain value. 
x
mean
standard_dev
cumulative

NORM.S.INV  This is the inverse of standard normal cumulative distribution. 
Probability

PERCENTILE in ExcelPERCENTILE In ExcelThe PERCENTILE Function returns the nth percentile from a supplied set of values. For example, you can use the PERCENTILE to find the 90th percentile, 80th percentile, etc. The PERCENTILE in excel is a builtin function of Microsoft Excel and is categorized under the Statistical Function.read more  This function returns the nth percentile of the set of values supplied. 
array
k

QUARTILE in Excel  This function in excel is used to find the different quartiles of a data set. 
array
quart

SLOPE in Excel  This function calculates how steep the line is 
known_ys
known_xs

SMALL in Excel  This function returns the nth smallest value of the set of values supplied. 
array
k

STDEV.SSTDEV.SThe standard deviation in excel shows the variability of the data values from the mean (average). The STDEV and STDEV.S calculate sample standard deviation in Excel, while STDEVP and STDEV.P compute population standard deviation.read more  SD shows how data spread outs among data points. 
number1
number2

T.TEST  This function in excel calculates the probability associated with the TtestTtestA Ttest is a method to identify whether the means of two groups differ from one another significantly. It is an inferential statistics approach that facilitates the hypothesis testing.read more of the student. 
array1
array2
tails
type

TREND  This function calculates the linear trend according to the given linear data set. 
known_ys
known_xs
new_xs
const

Information Functions in Excel
ISBLANK in Excel  This examines whether the supplied value is blank or not. 
value

ISERROR in Excel  This examines whether the supplied value is an error or not. 
value

ISNAISNAThe ISNA function is an error handling function in Excel. It helps to find out whether any cell has a “#N/A error” or not. This function returns the value “true” if “#N/A error” is identified or "false" if not identified.read more  This examines whether the supplied value is equal to #N/A or not. 
value

ISNUMBER  This function in excel testsExcel TestsThe Excel Test is a tool used by employers to assess their employees' Excel knowledge during the recruitment process. The goal is to assess skill level in Excel as it reduces human work and makes it very simple.read more whether the supplied value if a number or not. 
value

 35+ Courses
 120+ Hours
 Full Lifetime Access
 Certificate of Completion
Leave a Reply