Excel Functions

Excel functions list

Below are over 200 Excel functions to help you become a world-class financial analyst.

 

Date and Time

DATE Create a valid date from year, month, and day
year
month
day
DATEDIF Get days, months, or years between two dates
start_date
end_date
unit
DATEVALUE Convert a date in text format to a valid date
date_text
DAY Get the day as a number (1-31) from a date
date
DAYS Get days between dates
end_date
start_date
DAYS360 Get days between 2 dates in a 360-day year
start_date
end_date
method
EDATE Get the same date in future or past months
start_date
months
EOMONTH Get the last day of the month in future or past months
start_date
months
HOUR Get the hour as a number (0-23) from a time
serial_number
ISOWEEKNUM Get ISO week number for a given date
date
MINUTE Get the minute as a number (0-59) from a time
serial_number
MONTH Get the month as a number (1-12) from a date
date
NETWORKDAYS Get the number of working days between two dates
start_date
end_date
holidays
NETWORKDAYS.INTL Get work days between two dates
start_date
end_date
weekend
holidays
NOW Get the current date and time
-
SECOND Get the second as a number (0-59) from a time
serial_number
TIME Create a time with hours, minutes, and seconds
hour
minute
second
TIMEVALUE Get a valid time from a text string
time_text
TODAY Get the current date
-
WEEKDAY Get the day of the week as a number
serial_number
return_type
WEEKNUM Get the week number for a given date
serial_number
return_type
WORKDAY Get a date n working days in the future or past
start_date
days
holidays
WORKDAY.INTL Get date n working days in future or past
start_date
days
weekend
holidays
YEAR Get the year from a date
date
YEARFRAC Get the fraction of a year between two dates
start_date
end_date
basis

 

Financial

CUMIPMT Get the cumulative interest paid on a loan
rate
nper
pv
start_period
end_period
type
CUMPRINC Get the cumulative principal paid on a loan
rate
nper
pv
start_period
end_period
type
DURATION Get the duration of a security
settlement
maturity
coupon
yield
frequency
basis
FV Get the future value of an investment
rate
nper
pmt
pv
type
INTRATE Get the interest rate for a security
settlement
maturity
investment
redemption
basis
IPMT Get principal for given period
rate
per
nper
pv
fv
type
NPER Get number of periods for loan or investment
rate
pmt
pv
fv
type
PMT Get the periodic payment for a loan
rate
nper
pv
fv
type
PPMT Get principal for given period
rate
per
pv
fv
type
PV Get the present value of an investment
rate
nper
pmt
fv
type
RATE Get the interest rate per period of an annuity
nper
pmt
pv
fv
type
guess
TBILLPRICE Get the fair market value of a Treasury bill
settlement
maturity
discount
TBILLYIELD Get the yield on a Treasury bill
settlement
maturity
pr
YIELDMAT Get the annual yield on a security
settlement
maturity
issue
rate
pr
basis

 

Engineering

CONVERT Convert measurement units
number
from_unit
to_unit

 

Information

CELL Get information about a cell
info_type
reference
ERROR.TYPE Test for a specific error value
error_val
INFO Get information about current environment
type_text
ISBLANK Test if a cell is blank
value
ISERR Test for any error but #N/A
value
ISERROR Test for any error
value
ISEVEN Test if a value is even
value
ISFORMULA Test if cell contains a formula
reference
ISLOGICAL Test if a value is logical
value
ISNA Test for the #N/A error
value
ISNONTEXT Test for a non-text value
value
ISNUMBER Test for numeric value
value
ISODD Test if a value is odd
value
ISREF Test for a reference
value
ISTEXT Test for a text value
value
N Convert a value to a number
value
NA Create an #N/A error
-
TYPE Get the type of value in a cell
value

 

Logical

AND Test multiple conditions with AND
logical1
logical2
...
FALSE Generate the logical value FALSE
-
IF Test for a specific condition
logical_test
value_if_true
value_if_false
IFERROR Trap and handle errors
value
value_if_error
IFS Test multiple conditions, return first true
test1
value1
test2,
value2
...
NOT Reverse arguments or results
logical
OR Test multiple conditions with OR
logical1
logical2
...
TRUE Generate the logical value TRUE
-
XOR Perform exclusive OR
logical1
logical2
...

 

Lookup and Reference

ADDRESS Create a cell address from a given row and column
row_num
col_num
abs_num
a1
sheet
AREAS Get the number of areas in a reference
reference
CHOOSE Get a value from a list based on position
index_num
value1
value2
COLUMN Get the column number of a reference
-
reference
COLUMNS Get the number of columns in an array or reference
array
FORMULATEXT Get the formula in a cell
reference
HLOOKUP Look up a value in a table by matching on the first row
value
table
row_index
range_lookup
HYPERLINK Create a clickable link
link_locaation
friendly_name
INDEX Get a value in a list or table based on location
array
row_num
col_num
area_num
INDIRECT Create a reference from text
ref_text
a1
LOOKUP Look up a value in a one-column range
lookup_value
lookup_vector
result_vector
MATCH Get the position of an item in an array
lookup_value
lookup_array
match_type
MMULT Perform matrix multiplication
array1
array2
OFFSET Create a reference offset from given starting point
reference
rows
cols
height
width
ROW Get the row number of a reference
-
reference
ROWS Get the number of rows in an array or reference
array
RTD Get real-time data from a COM automation server
ProgID
server
topic1
topic2
...
TRANSPOSE Flip the orientation of a range of cells
array
VLOOKUP Lookup a value in a table by matching on the first column
value
table
col_index
range_lookup

 

Math

ABS Find the absolute value of a number
number
ARABIC Converts a Roman numerals to an Arabic numerals
roman_text
CEILING Round a number up to the nearest specified multiple
number
multiple
COS Get the cosine of an angle
number
DEGREES Converts radians to degrees
angle
EVEN Round a number up to the next even integer
number
EXP Find the value of e raised to the power of a number
number
FACT Find the factorial of a number
number
FLOOR Round a number down to the nearest specified multiple
number
multiple
GCD Get the greatest common divisor of two or more numbers
number1
number2
...
INT Get the integer part of a decimal by rounding down
number
LCM Get the least common multiple or two or more numbers
number1
number2
...
LOG Get the logarithm of a number
number
base
LOG10 Get the base-10 logarithm of a number
number
MOD Get the remainder from division
number
divisor
MROUND Round a number to the nearest specified multiple
number
multiple
ODD Round a number up to the next odd integer
number
PI Get the value of π
-
POWER Raise a number to a power
number
power
RADIANS Converts degrees into radians
angle
RAND Get a random number between 0 and 1
-
RANDBETWEEN Get a random integer between two values
bottom
top
ROMAN Converts numbers to Roman numerals
number
form
ROUND Round a number to a given number of digits
number
num_digits
ROUNDDOWN Round a number down to a given number of digits
number
num_digits
ROUNDUP Round a number up to a given number of digits
number
num_digits
SIGN Get the sign of a number
number
SIN Get the sine of an angle
number
SQRT Find the positive square root of a number
number
SUBTOTAL Get a subtotal in a list or database
function_num
ref1
ref2
...
SUM Add numbers together
number1
number2
number3
...
SUMIF Sum numbers in a range that meet supplied criteria
range
criteria
sum_range
SUMIFS Sum cells that match multiple criteria
sum_range
range1
criteria1
range2
criteria2
...
SUMPRODUCT Multiply, then sum arrays
array1
array2
...
TAN Get the tangent of an angle
number
TRUNC Truncate a number to a given precision
number
num_digits

 

Statistical

AVERAGE Get the average of a group of numbers
number1
number2
...
AVERAGEA Get the average of a group of numbers and text
value1
value2
...
AVERAGEIF Get the average of numbers that meet criteria
range
criteria
average-criteria
AVERAGEIFS Average cells that match multiple criteria
avg_rng
range1
criteria1
range2
criteria2
...
COMBIN Get total number of combinations
number
number_chosen
COUNT Count numbers
value1
value2
...
COUNTA Count the number of non-blank cells
value1
value2
...
COUNTBLANK Count cells that are blank
range
COUNTIF Count cells that match criteria
range
criteria
COUNTIFS Count cells that match multiple criteria
range1
criteria1
range2
criteria2
...
FREQUENCY Get the frequency of values in a data set
data_array
bins_array
LARGE Get the nth largest value
array
n
MAX Get the largest value
number1
number2
...
MAXA Return largest value
value1
value2
...
MAXIFS Get maximum value with criteria
max_range
range1
criteria1
range2
criteria2
...
MEDIAN Get the median of a group of numbers
number1
number2
...
MIN Get the smallest value
number1
number2
...
MINA Return smallest value
value1
value2
...
MINIFS Get minimum value with criteria
min_range
range1
criteria1
range2
criteria2
...
MODE Get the mode of a group of numbers
number1
number2
...
PERMUT Get total number of permutations
number
number_chosen
PERMUTATIONA Get total number of permutations, with repetitions
number
number_chosen
QUARTILE Get the quartile in a data set
array
quart
RANK Rank a number against a range of numbers
number
array
order
SMALL Get the nth smallest value
array
k
STDEV Get the standard deviation in a sample
number1
number2
...
STDEV.P Get the standard deviation in a population
number1
number2
...
STDEV.S Get the standard deviation in a sample
number1
number2
...

 

Text

CHAR Get a character from a number
number
CLEAN Strip non-printable characters from text
text
CODE Get the code for a character
text
CONCAT Join text values without delimiter
text1
text2
...
CONCATENATE Join text together
text1
text2
text3
...
DOLLAR Convert a number to text in currency format
number
decimals
EXACT Compare two text strings
text1
text2
FIND Get the location of text in a string
find_text
within_text
start_num
FIXED Rounds and converts a supplied number to text
number
decimal
no_commas
LEFT Extract text from the left of a string
text
num_chars
LEN Get the length of text
text
LOWER Convert text to lower case
text
MID Extract text from inside a string
text
start_num
num_chars
PROPER Capitalize the first letter in each word
text
REPLACE Replace text based on location
old_text
start_num
num_chars
new_text
REPT Repeat text as specified
text
number_times
RIGHT Extract text from the right of a string
text
num_chars
SEARCH Get the location of text in a string
find_text
within_text
start_num
SUBSTITUTE Replace text based on content
text
old_text
new_text
instance
TEXT Convert a number to text in a number format
value
format_text
TEXTJOIN Join text values with a delimiter
delimiter
ignore_empty
text1
text2
...
TRIM Remove extra spaces from text
text
UPPER Convert text to upper case
text
VALUE Convert text to a number
text

 

Other

GETPIVOTDATA Retrieve data from a pivot table in a formula
data_field
pivot_table
field1
item1
...