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
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
- Logical Functions
- Text Functions
- Date and Time Functions
- Lookup & Reference Functions
- Math Functions
- Statistical Functions
- Information Functions
Financial Functions in Excel
| FV | 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 | 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 | It returns only the principal amount for an investment or loan |
per
nper
pv
fv
type
|
| PRICE | It returns the redemption value per 100 face value. |
settlement
maturity
rate
yld
redemption
frequency
basis
|
| PV | It gives the present value of an investment by taking into consideration of constant interest rate and payments. |
rate
nper
pmt
fv
type
|
| RATE | 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 security investments over a period of time. |
Logical Functions in Excel
| AND | If all the supplied condition 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 | 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 | 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 | 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 | This excel function returns the numbers of characters in a cell. Space is also a character. |
text
|
| MID | 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 | 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 | This will return the position of the supplied character in the specific value. |
find_text
within_text
start_num
|
| SUBSTITUTE | 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 | This eliminates unnecessary spaces or trailing spaces. |
text
|
| VALUE | 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 date. |
year
month
day
|
| DAY | It extracts the day number from the supplied date. |
serial_number
|
| EDATE | 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 | It returns the current date and time. |
–
|
| TIME | It requires three elements, hour, minute, and second. |
hour
minute
second
|
| TODAY | It gives the current date. |
–
|
| WEEKDAY | It returns the weekday of the supplied date. |
serial_number
return_type
|
| WORKDAY | This excel function returns the date from the supplied date based on the number of days we specify. |
start_date
days
holidays
|
| YEAR | 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 | 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 | This indirectly refers to the other cell from a different cell. |
ref_text
a1
|
| LOOKUP | 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 | 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 | 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 | Returns a rounded number based on a multiple significance. |
number
significance
mode
|
| COMBIN | 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 | It raises the supplied number to the power of e. |
number
|
| INT | This function is responsible for returning a number’s integer portion. |
number
|
| LN | This function is used to calculate the natural logarithm of a number. |
number
|
| LOG | It returns the logarithm of a number to the base user gives |
number
base
|
| ODD | This function converts all the even numbers to ODD numbers. |
number
|
| POWER | This function raises the base number to a different number of times at the top of the base. |
number
power
|
| PRODUCT | This function simply do the multiplication of two supplied numbers. |
number1
number2
|
| RAND | 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 | This excel function rounds the given floating point number to a number of decimal places provided. |
number
num_digits
|
| ROUNDDOWN | 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 | This function calculates the SIN of an angle |
number
|
| SQRT | Find a number of positive square roots |
number
|
| SUBTOTAL | 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 | This function adds the numbers based on the single criteria given by the user. |
range
criteria
sum_range
|
| SUMPRODUCT | This function simultaneously does the summation as well as multiplication corresponding to the values. |
array1
array2
array3
|
| TAN | This function returns the tangent of an angle. |
number
|
| TANH | 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 | 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 | This function is used to decide if two populations having normal distribution have similar variances or the standard deviation. |
array1
array2
|
| FORECAST | This function calculates or estimates the sales values based on the previous trends. |
x
known_ys
known_xs
|
| FREQUENCY | 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 | It is used to calculate a line’s stats. |
known_ys
known_xs
const
stats
|
| MAX | 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 | 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 | This function returns the nth percentile of the set of values supplied. |
array
k
|
| QUARTILE | This function in excel is used to find the different quartiles of a data set. |
array
quart
|
| SLOPE | This function calculates how steep the line is |
known_ys
known_xs
|
| SMALL | 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 | This examines whether the supplied value is blank or not. |
value
|
| ISERROR | 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
