100+ Excel Functions (explained
with Examples and Videos)
Here you will find a detailed tutorial on 100+ Excel Functions & VBA
Functions. Each Excel function is covered in detail with Examples and
Videos.
This Tutorial Covers:
Excel Functions – Date and Time
Excel Functions – Logical
Excel Functions – Lookup & Reference
Excel Functions – Math
Excel Functions – Statistics
Excel Functions – Text Functions
Excel Functions – Info
Excel Functions – Financial
VBA Functions
Excel Functions – Date and Time
Excel Function Description
Excel DATE function can be used when you want to get
the date value using the year, month and, day values as
Excel DATE Function
the input arguments. It returns a serial number that
represents a specific date in Excel.
Excel DATEVALUE function is best suited for
Excel DATEVALUE situations when a date is stored as text. This function
Function converts the date from text format to a serial number that
Excel recognizes as a date.
Excel DAY function can be used when you want to get
the day value (ranging between 1 to 31) from a specified
Excel DAY Function
date. It returns a value between 0 and 31 depending on
the date used as the input.
Excel HOUR function can be used when you want to get
the HOUR integer value from a specified time value. It
Excel HOUR Function
returns a value between 0 (12:00 A.M.) and 23 (11:00
P.M.) depending on the time value used as the input
Excel MINUTE function can be used when you want to
get the MINUTE integer value from a specified time
Excel MINUTE Function
value. It returns a value between 0 and 59 depending on
the time value used as the input.
Excel NETWORKDAYS function can be used when you
want to get the number of working days between two
Excel NETWORKDAYS
given dates. It does not count the weekends between the
Function
specified dates (by default the weekend is Saturday and
Sunday). It can also exclude any specified holidays.
Excel NETWORKDAYS.INTL function can be used
when you want to get the number of working days
Excel between two given dates. It does not count the weekends
NETWORKDAYS.INTL and holidays, both of which can be specified by the user.
Function It also enables you to specify the weekend (for example,
you can specify Friday and Saturday as the weekend, or
only Sunday as the weekend).
Excel NOW function can be used to get the current date
Excel NOW Function
and time value.
Excel SECOND function can be used want to get the
integer value of the seconds from a specified time value.
Excel SECOND Function
It returns a value between 0 and 59 depending on the
time value used as the input.
Excel TODAY function can be used to get the current
Excel TODAY Function date. It returns a serial number that represents the current
date.
Excel WEEKDAY function can be used to get the day of
the week as a number for the specified date. It returns a
Excel WEEKDAY Function
number between 1 and 7 that represents the
corresponding day of the week.
Excel WORKDAY function can be used when you want
Excel WORKDAY Function to get the date after a given number of working days. By
default, it takes Saturday and Sunday as the weekend
Excel WORKDAY.INTL function can be used when you
Excel WORKDAY.INTL want to get the date after a given number of working
Function days. In this function, you can specify the weekend to be
days other than Saturday and Sunday.
Excel DATEDIF function can be used when you want to
calculate the number of years, months, or days between
Excel DATEDIF Function
the two specified dates. A good example would be
calculating the age.
Excel Functions – Logical
Excel Function Description
Excel AND function can be used when you want to check multiple
Excel AND
conditions. It returns TRUE only when all the given conditions are
Function
true.
Excel FALSE Excel FALSE function returns the logical value FALSE. It does not
Function take any input arguments.
Excel IF Function is best suited for situations where you want to
Excel IF
evaluate a condition, and the return a value if it is TRUE and another
Function
value if it is FALSE.
Excel IFS Function is best suited for situations where you want to
Excel IFS test multiple conditions at once and then return the result based on it.
Function This is helpful as you don’t have to create long nested IF formulas
that can get confusing.
Excel IFERROR function is best-suited to handle formula that
Excel IFERROR
evaluates to an error. You can specify a value to show if the formula
Function
returns an error.
Excel NOT Excel NOT function can be used when you want to reverse the value
Function of a logical argument (TRUE/FALSE).
Excel OR Excel OR function can be used when you want to check multiple
Function conditions. It returns TRUE if any of the given condition is true.
Excel TRUE Excel TRUE function returns the logical value TRUE. It does not
Function take any input arguments.
Excel Functions – Lookup &
Reference
Excel Function Description
Excel COLUMN Excel COLUMN function can be used when you want to get the
Function column number of a specified cell.
Excel COLUMNS function can be used when you want to get the
Excel
number of columns in a specified range or array. It returns a number
COLUMNS
that represents the total number of columns in the specified range or
Function
array.
Excel HLOOKUP function is best suited for situations when you are
Excel
looking for a matching data point in a row, and when the matching
HLOOKUP
data point is found, you go down that column and fetch a value from
Function
a cell which is specified a number of rows below the top row.
Excel INDEX function can be used when you have the position (row
Excel INDEX number and column number) of a value in a table, and you want to
Function fetch that value. This is often use with the MATCH function and is a
powerful alternative to the VLOOKUP function.
Excel Excel INDIRECT function can be used when you have the references
INDIRECT as text and you want to get the values from those references. It
Function returns the reference specified by the text string.
Excel MATCH function can be used when you want to get the
Excel MATCH
relative position of a lookup value in a list or an array. It returns a
Function
number that represents the position of the lookup value in the array.
Excel Function Description
Excel OFFSET function can be used when you want to get a
Excel OFFSET reference which offsets specified number of rows and columns from
Function the starting point. It returns the reference that OFFSET function
points to.
Excel ROW Function function can be used when you want to get the
Excel ROW
row number of a cell reference. For example, =ROW(B4) would
Function
return 4, as it is in the fourth row.
Excel ROWS Function can be used when you want to get the number
Excel ROWS
of rows in a specified range or array. It returns a number that
Function
represents the total number of rows in the specified range or array.
Excel VLOOKUP function is best suited for situations when you are
Excel looking for a matching data point in a column, and when the
VLOOKUP matching data point is found, you go to the right in that row and fetch
Function a value from a cell which is a specified number of columns to the
right.
Excel XLOOKUP function is a new function for Office 365 users
Excel
and is an enhanced version of the VLOOKUP/HLOOKUP functions.
XLOOKUP
It can be used to lookup and fetch the value in a dataset, and can
Function
replace most of what we do with older lookup formulas.
Excel FILTER function is a new function for Office 365 users that
Excel FILTER
allows you to quickly filter and extract data based on the given
Function
condition (or multiple conditions).
Excel Functions – Math
Excel Function Description
Excel INT Function can be used when you want to get the
Excel INT Function
integer portion of a number.
Excel MOD function can be used when you want to get the
remainder when one number is divided by another. It returns
Excel MOD Function
a numerical value that represents the remainder when one
number is divided by another.
Excel RAND function can be used when you want to
Excel RAND Function generate evenly distributed random numbers between 0 and
1. It returns a number between 0 and 1
Excel RANDBETWEEN function can be used when you
Excel want to generate evenly distributed random numbers
RANDBETWEEN between a top and bottom range specified by the user. It
Function returns a number between the top and bottom range
specified by the user.
Excel ROUND Excel ROUND function can be used when you want to
Function return a number rounded to a specified number of digits.
Excel SUM function can be used to add all numbers in a
Excel SUM Function
range of cells.
Excel SUMIF function can be used when you want to add
Excel SUMIF Function
the values in a range if the specified condition is met.
Excel Function Description
Excel SUMIFS Excel SUMIFS function can be used when you want to add
Function the values in a range if multiple specified criteria are met.
Excel SUMPRODUCT function can be used when you want
Excel SUMPRODUCT
to first multiply two or more sets to arrays and then get its
Function
sum
Excel Functions – Statistics
Excel Function Description
Excel RANK function can be used when you want to rank a
number against a list of numbers. It returns a number that
Excel RANK Function
represents the relative rank of the number against the list of
numbers.
Excel AVERAGE function can be used when you want to
Excel AVERAGE
get the average (arithmetic mean) of the specified
Function
arguments.
Excel AVERAGEIF function can be used when you want to
Excel AVERAGEIF
get the average (arithmetic mean) of all the values in a range
Function
of cells that meet a given criteria.
Excel AVERAGEIFS function can be used when you want
Excel AVERAGEIFS
to get the average (arithmetic mean) of all the cells in a
Function
range that meets multiple criteria.
Excel COUNT Excel COUNT function can be used to count the number of
Function cells that contain numbers.
Excel Function Description
Excel COUNTA Excel COUNTA function can be used when you want to
Function count all the cells in a range that are not empty.
Excel COUNTBLANK Excel COUNTBALNK function can be used when you have
Function to count all the empty cells in a range.
Excel COUNTIF Excel COUNTIF function can be used when you want to
Function count the number of cells that meet a specified criterion.
Excel COUNTIFS function can be used when you want to
Excel COUNTIFS
count the number of cells that meet a single or multiple
Function
criteria.
Excel LARGE function can be used to get the Kth largest
Excel LARGE Function value from a range of cells or array. For example, you can
get the third largest value from a range of cells.
Excel MAX function can be used when you want to get the
Excel MAX Function
largest value from a set of values.
Excel MIN function can be used when you want to get the
Excel MIN Function
smallest value from a set of values.
Excel SMALL function can be used to get the Kth smallest
Excel SMALL Function value from a range of cells or array. For example, you can
get the third smallestvalue from a range of cells.
Excel Functions – Text Functions
Excel Function Description
Excel Excel CONCATENATE function can be used when you
CONCATENATE want to join 2 or more characters or strings. It can be used to
Function join text, numbers, cell references, or a combination of these.
Excel FIND function can be used when you want to locate a
text string within another text string and find its position. It
Excel FIND Function
returns a number that represents the starting position of the
string you are finding in another string. It is case-sensitive.
Excel LEFT function can be used to extract text from left of
Excel LEFT Function the string. It returns the specified number of characters from
the left of the string
Excel LEN function can be used when you want to get the
Excel LEN Function total number of characters in a specified string. This is useful
when you want to know the length of a string in a cell.
Excel LOWER function can be used when you want to
Excel LOWER convert all uppercase letter in a text string to lowercase.
Function Numbers, special characters, and punctuations are not
changed by the LOWER function.
Excel MID function can be used to extract a specified
Excel MID Function number of characters from a string. It returns the sub-string
from a string.
Excel PROPER
Function Excel PROPER function can be used when you want to
capitalize the first character of every word. Numbers, special
Excel Function Description
characters, and punctuations are not changed by the
PROPER function.
Excel REPLACE function can be used when you want to
Excel REPLACE replace a part of the text string with another string. It returns
Function a text string where a part of the text has been replaced by the
specified string.
Excel REPT function can be used when you want to repeat a
Excel REPT Function
specified text a certain number of times.
The RIGHT function can be used to extract text from the
Excel RIGHT Function right of the string. It returns the specified number of
characters from the right of the string
Excel SEARCH function can be used when you want to
locate a text string within another text string and find its
Excel SEARCH
position. It returns a number that represents the starting
Function
position of the string you are finding in another string. It is
NOT case-sensitive.
Excel SUBSTITUTE
Function Excel SUBSTITUTE function can be used when you want to
substitute text with new specified text in a string. It returns a
text string where an old text has been substituted by the new
one.
Excel TEXT function can be used when you want to convert
Excel TEXT Function
a number to text format and display it in a specified format.
Excel TRIM function can be used when you want to remove
Excel TRIM Function
leading, trailing, and double spaces in Excel.
Excel Function Description
Excel UPPER function can be used when you want to
convert all lowercase letters in a text string to uppercase.
Excel UPPER Function
Numbers, special characters, and punctuations are not
changed by the UPPER function.
Excel Functions – Info
Excel Function Description
Excel ISBLANK
Function
Excel ISERROR
Function
Excel ISNA Function Excel IS function returns TRUE when specified condition is
Excel ISNUMBER TRUE. For example, ISNA would return TRUE if the cell
Function has a #N/A! error.
Excel ISEVEN
Function
Excel ISODD Function
Excel ISLOGICAL
Function
Excel ISTEXT
Function
Excel Functions – Financial
Excel Function Description
Excel PMT function helps you calculate the payment you need to
Excel PMT
make for a loan when you know the total loan amount, interest rate,
Function
and the number of constant payments.
Excel NPV Excel NPV function allows you to calculate the Net Present Value of
Function all the cashflows when you know the discount rate
Excel IRR Excel IRR function allows you to calculate the Internal Rate of
Function Return when you have the cashflows data
VBA Functions
Excel Function Description
VBA TRIM function allows you to remove the leading and trailing
VBA TRIM
spaces from a text string in Excel. It can be a useful VBA function if
Function
you want to quickly clean the data.
VBA SPLIT function alllows you to split a text string based on the
VBA SPLIT
delimiter. For example, if you want to split text based on a comma
Function
or tab or colon, you can do that with the SPLIT function.
VBA MsgBox is a function that displays a dialog box that you can
VBA MsgBox
use to inform your users by showing a custom message or get some
Function
basic inputs (such as Yes/No or OK/Cancel).
VBA INSTR VBA InStr function finds the position of a specified substring within
Function the string and returns the first position of its occurrence.
VBA UCase Excel VBA UCASE function takes a string as the input and converts
Function all the lower case characters into upper case.
VBA LCase Excel VBA LCASE function takes a string as the input and converts
Function all the upper case characters into lower case.
VBA DIR Use VBA DIR function when you want to get the name of the file or
Function a folder, using their path name
Useful Excel Resources:
100+ Excel Interview Questions
200+ Excel Keyboard Shortcuts
Free Excel Templates
Free Online Excel Training
Best Excel Books
Excel Formulas Not Working: Possible Reasons and How to FIX IT!
20 Advanced Excel Functions and Formulas (for Excel Pros)
Formula vs Function in Excel – What’s the Difference?
