Excel Functions

Publication Date :

04 Apr, 2019

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

What Are Excel Functions?

Below is the list of the most important 100+ Excel functions and their formula usage. These Excel functions are categorized into the following:

Excel functions are classified into financial, logical, text, date and time, lookup & reference, math, statistical, and information functions.
Excel functions like IRR, MIRR, NPER, NPV, PMT, PPMT, Price, PV, Interest Rate, XIRR, and Yield help find investment interest, understand potential returns and expenses, and provide insights into the Internal Rate of Return.
Excel functions are essential for converting numbers to ASCII characters. The AND function returns TRUE if all conditions are true, while the IFERROR function returns a specific value if an error is returned.

List Of Excel Functions

#1 - Financial Functions

FV in Excel	Calculate the future value of an investment at a specified rate of interest	rate nper pmt pv type
IPMT	Calculate 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 considering both the finance and reinvestment rates.	values finance_rate reinvest_rate
NPER	The number of installments or EMIs 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 considering the current discount rate.	rate value1 value2
PMT	This Excel function returns the per month payment required to repay 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 considering constant interest rates 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 some time.

#2 - 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	-

#3 - 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

#4 - Date And Time Functions

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

#5 - Lookup & Reference Functions

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

#6 - Math Functions

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

#7 - Statistical Functions

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 student's T-test	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

#8 - Information Functions

ISBLANK in Excel	It examines whether the supplied value is blank or not.	value
ISERROR in Excel	It examines whether the supplied value is an error or not.	value
ISNA	It examines whether the supplied value is equal to #N/A or not.	value
ISNUMBER	This function in excel tests whether the supplied value is a number or not.	value

Frequently Asked Questions

1. Why are Excel functions useful?

Excel functions are crucial for converting numbers to ASCII characters, with the AND function indicating true conditions and the IFERROR function providing a specific value if an error is returned.

2. What is Excel function?

Functions are predefined formulas in Excel that perform calculations using specific arguments in a specific order and can be found on the Formulas tab on the Ribbon, enabling simple or complex calculations.

3. What are the rules of functions in Excel?

To enter a function in Excel, one must use the = sign, a valid Excel name (e.g., SUM, AVERAGE), followed by an opening and closing parenthesis and enclosed in a parenthesis with arguments.