Below is the list of Most Important 100+ Excel Functions along with its usage and formula. These Excel Functions are sub-divided into the following –

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

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 on a series of cash flows.
values
guess
MIRR in Excel It is the Modified Internal Rate of return, which returns percentage by taking into consideration both 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 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 TRUE result value.
logical_test
value_if_true
value_if_false
IFERROR in Excel 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 AND function in excel but if any one 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 non-printable 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 left-hand 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 This will get the value from the right-hand side of a string based on the number of characters we specify.
text
num_chars
SEARCH in Excel 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

DATE This excel function requires three elements, year, month, and day. It will format the supplied numbers as a date.
year
month
day
DAY It extracts the day number from the supplied date.
serial_number
EDATE in Excel It is used to get a date on the same day of the month, x months in the past or future.
start_date
months
EOMONTH This excel function returns the end of the month for the supplied date.
start_date
months
NOW in Excel It returns the current date and time.
–
TIME in Excel It requires three elements, hour, minute, and second.
hour
minute
second
TODAY in Excel It gives the current date.
–
WEEKDAY in Excel It returns the weekday of the supplied date.
serial_number
return_type
WORKDAY in Excel This excel function returns the date from the supplied date based on the number of days we specify.
start_date
days
holidays
YEAR in Excel This function extracts the year from the supplied date.
serial_number

Lookup & Reference Functions in Excel

ADDRESS This will create the cell reference or address of the cell as 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
HYPERLINK 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 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 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 first-row 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 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 This function simply do the multiplication of two supplied numbers.
number1
number2
RAND in Excel It returns values which 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 Excel This excel function rounds the given floating point number to a number of decimal places provided.
number
num_digits
ROUNDDOWN in Excel 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 Excel Find a number of positive square roots
number
SUBTOTAL in Excel 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 Excel 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 distribution 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 growth 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 Excel 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 Excel 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.S SD shows how data spread outs among data points.
number1
number2
T.TEST This function in excel calculates the probability associated with the T-test 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
ISNA This examines whether the supplied value is equal to #N/A or not.
value
ISNUMBER This function in excel tests whether the supplied value if a number or not.
value