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 –

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- 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 | 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 | 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 | 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 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 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
|
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 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 | 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 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 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
|
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion