Exceljet

Quick, clean, and to the point

Excel Function Guide

A streamlined guide to Excel functions. Required arguments in dark gray, optional arguments in white. Also see our huge list of example formulas.

Date and time

Create a valid date from year, month, and day
yearmonthday
Get days, months, or years between two dates
start_dateend_dateunit
Convert a date in text format to a valid date
date_text
Get the day as a number (1-31) from a date
date
Get days between dates
end_datestart_date
Get days between 2 dates in a 360-day year
start_dateend_datemethod
Get the same date in future or past months
start_datemonths
Get the last day of the month in future or past months
start_datemonths
Get the hour as a number (0-23) from a Time
serial_number
Get ISO week number for a given date
date
Get the minute as a number (0-59) from a time
serial_number
Get the month as a number (1-12) from a date
date
Get the number of working days between two dates
start_dateend_dateholidays
Get work days between two dates
start_dateend_dateweekendholidays
Get the current date and time
Get the Second as a number (0-59) from a Time
serial_number
Create a time with hours, minutes, and seconds
hourminutesecond
Get a valid time from a text string
time_text
Get the current date
Get the day of the week as a number
serial_numberreturn_type
Get the week number for a given date
serial_numreturn_type
Get a date n working days in the future or past
start_datedaysholidays
Get date n working days in future or past
start_datedaysweekendholidays
Get the year from a date
date
Get the fraction of a year between two dates
start_dateend_datebasis

Engineering

Convert measurement units
numberfrom_unitto_unit

Financial

Get cumulative interest paid on a loan
ratenperpvstart_periodend_periodtype
Get cumulative principal paid on a loan
ratenperpvstart_periodend_periodtype
Get the future value of an investment
ratenperpmtpvtype
Get principal for given period
rateperpvfvtype
Get number of periods for loan or investment
ratepmtpvfvtype
Get the periodic payment for a loan
ratenperpvfvtype
Get principal for given period
rateperpvfvtype
Get the present value of an investment
ratenperpmtfvtype
Get the interest rate per period of an annuity
nperpmtpvfvtypeguess

Information

Get information about a cell
info_typereference
Test for a specific error value
error_val
Get information about current environment
type_text
Test if a cell is empty
value
Test for any error but #N/A
value
Test for any error
value
Test if a value is even
value
Test if cell contains a formula
reference
Test if a value is logical
value
Test for the #N/A error
value
Test for a non-text value
value
Test for numeric value
value
Test if a value is odd
value
Test for a reference
value
Test for a text value
value
Convert a value to a number
value
Create an #N/A error
Get the type of value in a cell
value

Logical

Test multiple conditions with AND
logical1logical2...
Generate the logical value FALSE
Test for a specific condition
logical_testvalue_if_truevalue_if_false
Trap and handle errors
valuevalue_if_error
Test multiple conditions, return first true
test1value1test2, value2...
Reverse arguments or results
logical
Test multiple conditions with OR
logical1logical2...
Generate the logical value TRUE

Lookup and reference

Create a cell address from a given row and column
row_numcol_numabs_numa1sheet
Get the number of areas in a reference.
reference
Get a value from a list based on position
index_numvalue1value2...
Get the column number of a reference.
reference
Get the number of columns in an array or reference.
array
Get the formula in a cell
reference
Look up a value in a table by matching on the first row
valuetablerow_indexrange_lookup
Create a clickable link.
link_locationfriendly_name
Get a value in a list or table based on location
arrayrow_numcol_numarea_num
Create a reference from text
ref_texta1
Look up a value in a one-column range
lookup_valuelookup_vectorresult_vector
Get the position of an item in an array
lookup_valuelookup_arraymatch_type
Create a reference offset from given starting point
referencerowscolsheightwidth
Get the row number of a reference
reference
Get the number of rows in an array or reference.
array
Flip the orientation of a range of cells
array
Lookup a value in a table by matching on the first column
valuetablecol_indexrange_lookup

Math

Find the absolute value of a number
number
Round a number up to the nearest specified multiple
numbermultiple
Get the cosine of an angle
number
Converts an angle into degrees
angle
Round a number up to the next even integer
number
Find the value of e raised to the power of a number
number
Find the factorial of a number
number
Round a number down to the nearest specified multiple
numbermultiple
Get the greatest common divisor of two or more numbers
number1number2...
Get the integer part of a decimal by rounding down
number
Get the least common multiple or two or more numbers
number1number2...
Get the logarithm of a number
numberbase
Get the base-10 logarithm of a number
number
Get the remainder from division
numberdivisor
Round a number to the nearest specified multiple
numbermultiple
Round a number up to the next odd integer
number
Get the value of π
Raise a number to a power
numberpower
Converts an angle into radians
angle
Get a random number between 0 and 1
Get a random integer between two values
bottomtop
Round a number to a given number of digits
numbernum_digits
Round a number down to a given number of digits
numbernum_digits
Round a number up to a given number of digits
numbernum_digits
Get the sign of a number.
number
Get the sine of an angle
number
Find the positive square root of a number
number
Get a subtotal in a list or database
function_numref1ref2...
Add numbers together
number1number2number3...
Sum numbers in a range that meet supplied criteria
rangecriteriasum_range
Sum cells that match multiple criteria
sum_rangerange1criteria1range2criteria2...
Multiply, then sum arrays
array1array2...
Get the tangent of an angle.
number
Truncate a number to a given precision
numbernum_digits

Statistical

Get the average of a group of numbers
number1number2...
Get the average of a group of numbers and text
value1value2...
Get the average of numbers that meet criteria
rangecriteriaaverage_range
Average cells that match multiple criteria
avg_rngrange1criteria1range2criteria2...
Count numbers
value1value2...
Count the number of non-blank cells
value1value2...
Count cells that are blank
range
Count cells that match criteria
rangecriteria
Count cells that match multiple criteria
range1criteria1range2criteria2...
Get the frequency of values in a data set
data_arraybins_array
Get the nth largest value
arrayn
Get the largest value
array
Get maximum value with criteria
max_rangerange1criteria1range2criteria2...
Get the median of a group of numbers
number1number2...
Get the smallest value.
array
Get minimum value with criteria
min_rangerange1criteria1range2criteria2...
Get the mode of a group of numbers
number1number2...
Get the quartile in a data set
arrayquart
Rank a number against a range of numbers
numberarrayorder
Get the nth smallest value
arrayn
Get the standard deviation in a sample
number1number2...
Get standard deviation of population
number1number2...
Get the standard deviation in a sample
number1number2...
Get standard deviation of population
number1number2...

Text

Get a character from a number
number
Strip non-printable characters from text
text
Get the code for a character
text
Join text values without delimiter
text1text2...
Join text together
text1text2text3...
Convert a number to text in currency format
numberdecimals
Compare two text strings
text1text2
Get the location of text in a string
find_textwithin_textstart_num
Extract text from the left of a string
textnum_chars
Get the length of text.
text
Convert text to lower case
text
Extract text from inside a string
textstart_numnum_chars
Capitalize the first letter in each word
text
Replace text based on location
old_textstart_numnum_charsnew_text
Repeat text as specified
textnumber_times
Extract text from the right of a string
textnum_chars
Get the location of text in a string
find_textwithin_textstart_num
Replace text based on content
textold_textnew_textinstance
Convert a number to text in a number format
valueformat_text
Join text values with a delimiter
delimiterignore_emptytext1text2...
Remove extra spaces from text
text
Convert text to upper case
text
Convert text to a number
text

Other

Retrieve data from a pivot table in a formula
data_fieldpivot_tablefield1, item1...
Thank you, and thank you so very much for your videos, they are sooooo helpful. - Elizabeth
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course