Date and Time Functions
DATE
Returns the DATEVALUE serial number.
=DATE(year,month,day)
DATEVALUE
Converts date text to a DATEVALUE serial number.
=DATEVALUE(date_text)
DAY
Returns the corresponding day of the month serial number, from 1 to 31.
=DAY(serial_number)
DAYS360
Returns the number of days between dates that you specify, based on a 360-day year (twelve 30
day months).
=DAYS360(start_date,end_date)
EDATE
Returns the value or serial number of the date specified by you and the number of months before or
after the specified date. Use EDATE to calculate the maturity date or date due that falls on the same
day of the month as the date of issue.
=EDATE(start_date,months)
EOMONTH
Returns the serial number for the last day of the month (as specified by months) before or
after start_date. Used to calculate maturity dates or dates due that fall on the last day of the month.
=EOMONTH(start_date,months)
HOUR
Returns the hour as a serial number integer between 0 (12:00 A.M.) and 23 (11:00 P.M.).
=HOUR(serial_number)
MINUTE
Returns the serial number integer from 0 to 59 that corresponds to the minute.
=MINUTE(serial_number)
MONTH
Returns the corresponding serial number of the month between 1 and 12.
=MONTH(serial_number)
NETWORKDAYS
Returns the number of working days between two dates. Excludes weekends and specified holidays.
=NETWORKDAYS(start_date,end_date,holidays)
NOW
Returns the current date and time in the form of a serial number. When entered in a cell, Excel
formats the number as a date and time. There are no arguments for this function.
=NOW()
SECOND
Returns the corresponding serial number of seconds as an integer between 0 and 59.
=SECOND(serial_number)
TIME
Returns the corresponding serial number of time as a decimal between 0 and 0.99999999.
=TIME(hour,minute,second)
TIMEVALUE
Returns the serial number represented by text as time.
=TIMEVALUE(time_text)
TODAY
Returns the current date as a serial number. When entered in a cell, Excel formats the number as a
date. There are no arguments for this function.
=TODAY()
WEEKDAY
Returns the corresponding day of the week (from 1 to 7) as a serial number.
=WEEKDAY(serial_number,return_type)
WORKDAY
Returns a number representing the date that is the number of days (specified by days) before or
afterstart_date. You can exclude dates with the holidays argument.
=WORKDAY(start_date,days,holidays).
YEAR
Returns the corresponding year as a serial number in the form of an integer from 19009999.
=YEAR(serial_number)
Information Functions
CELL
Returns information about a cell's location, formatting, or contents in the upper-left cell in a
reference.
=CELL(info_type,reference)
COUNTBLANK
Counts the number of empty cells in a specified range.
=COUNTBLANK(range)
INFO
Returns operating environment information.
=INFO(type_text)
ISBLANK
Returns TRUE if value refers to an empty cell.
=ISBLANK(value)
ISERR
Returns TRUE if value refers to any error value in Microsoft Excel except #NA.
=ISERR(value)
ISERROR
Returns TRUE if value refers to any error value in Microsoft Excel.
=ISERROR(value)
ISEVEN
Returns TRUE or FALSE if the number is even or odd, TRUE being even and FALSE being odd.
=ISEVEN(number)
ISLOGICAL
Returns TRUE if the value is logical.
=ISLOGICAL(value)
ISNA
Returns TRUE if value is associated with the error type #NA.
=ISNA(value)
ISNONTEXT
Returns TRUE if value refers to any item that isn't text. Returns TRUE if value refers to a blank cell.
=ISNONTEXT(value)
ISNUMBER
Returns TRUE if value refers to a number.
=ISNUMBER(value)
ISODD
Returns TRUE if number is odd and FALSE if the number is even.
=ISODD(number)
ISREF
Returns TRUE if value refers to a reference.
=ISREF(value)
ISTEXT
Returns TRUE if value refers to text.
=ISTEXT(value)
N
Returns value converted to a number.
=N(value)
NA
Returns the error value associated with #NA.
=NA()
TYPE
Returns the type of value, for example, number = 1, text = 2, logical value = 4, error value = 16, and
array = 64. Use TYPE when the behavior of another function depends on the type of value in a
particular cell.
=TYPE(value)
Logical Functions
AND
Returns TRUE if all arguments in the formula are true, and FALSE if any one argument is false.
=AND(logical1,logical2,...)
FALSE
Returns the value FALSE. There are no arguments associated with this function.
=FALSE()
IF
Returns a value if one condition is true, and returns another value if the condition is false.
=IF(logical_test,value_if_true,value_if_false)
NOT
Returns the reverse value of its arguments.
=NOT(logical)
OR
Returns TRUE if any one argument is true. Returns FALSE if all arguments are false.
=OR(logical1,logical2,...)
TRUE
Returns the value TRUE. There are no arguments associated with this function.
=TRUE()
Lookup and Reference Functions
ADDRESS
Given specified row and column numbers, creates a cell address as text.
=ADDRESS(row_num,column_num,abs_num,A1,sheet_text)
AREAS
Returns the number of areas in reference.
=AREAS(reference)
CHOOSE
Returns the index number from a list of arguments.
=CHOOSE(index_num,value1,value2,...)
COLUMN
Based on a given reference, returns the column number.
=COLUMN(reference)
COLUMNS
Based on an array or reference, returns the number of columns.
=COLUMNS(array)
HLOOKUP
Searches for a specified value in an array or tables, based on the value found in the first row.
=HLOOKUP(lookup_value,table_array,row_index_number,range_lookup)
HYPERLINK
Creates a shortcut or jump that switches to another location within the workbook, or opens a
document stored on a local drive, a network server, an intranet, or the Internet.
=HYPERLINK(link_location,cell_contents)
INDEX (Array Form)
Based on a table or array, returns the value of an element selected by the row number and column
letter indexes.
=INDEX(array,row_num,column_num)
INDEX (Reference Form)
Based on the intersection of a particular row and column, returns the reference of the cell.
=INDEX(reference,row_num,column_num,area_num)
INDIRECT
Returns the reference based on a text string.
=INDIRECT(ref_text,A1)
LOOKUP (Vector Form)
Based on a range of one row or one column, returns the value from the same position in a second
row or column.
=LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP (Array Form)
Looks in the first row or column of an array, and returns the specified value from the same position in
the last row or column of the array.
=LOOKUP(lookup_value,array)
MATCH
Returns the position of an item in an array that matches a specified value and order.
=MATCH(lookup_value,lookup_array,match_type)
OFFSET
Returns a reference to a range that is a specific number of rows and columns from a cell or range of
cells.
=OFFSET(reference,rows,columns,height,width)
ROW
Based on a reference, returns the row number.
=ROW(reference)
ROWS
Based on a reference or array, returns the number of rows.
=ROWS(array)
TRANSPOSE
Returns a horizontal range of cells as vertical or vice versa.
=TRANSPOSE(array)
VLOOKUP
Looks for a value in the left most column of a table and returns a value from the column number you
specify.
=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)
Math Functions
ABS
Returns the absolute value of number.
=ABS(number)
CEILING
Returns number rounded up to the nearest integer, or to the nearest multiple of significance (for
example, to the nearest nickel).
=CEILING(number,significance)
COUNTIF
Counts the number of cells in a specified range that meet the criteria you specify.
=COUNTIF(range,criteria)
EVEN
Returns number rounded up to the nearest integer.
=EVEN(number)
EXP
Returns e raised to the power of number.
=EXP(number)
FACT
Returns the factorial of number.
=FACT(number)
FLOOR
Rounds number down, toward zero, to the nearest multiple of significance.
=FLOOR(number,significance)
INT
Rounds number down to the nearest integer.
=INT(number)
LN
Returns the natural logarithm of a number.
=LN(number)
MOD
Returns the remainder after number is divided by divisor.
=MOD(number,divisor)
MROUND
Returns a number rounded to the desired multiple.
=MROUND(number,multiple)
MULTINOMIAL
Returns the ratio of the factorial of a sum of values to the product of factorials.
=MULTINOMIAL(number1,number2,...)
ODD
Returns number rounded to the nearest odd integer.
=ODD(number)
PI
Returns the number 3.14159265358979, the mathematical constant Pi, accurate to 15 digits. There
are no arguments associated with this function.
=PI()
POWER
Returns the result of a number raised to a power.
=POWER(number,power)
PRODUCT
Multiplies all the numbers given as arguments and returns the product.
=PRODUCT(number1,number2,...)
QUOTIENT
Returns the integer portion of a division. Use this function to discard the remainder of a division.
=QUOTIENT(numerator,denominator)
RAND
Returns an evenly distributed number greater than or equal to 0 and less than 1. There are no
arguments associated with this function.
=RAND()
RANDBETWEEN
Returns a random number between the numbers you specify.
=RANDBETWEEN(bottom,top)
ROUND
Rounds a number to a specified number of digits.
=ROUND(number,num_digits)
ROUNDDOWN
Rounds a number down toward 0.
=ROUNDDOWN(number,num_digits)
ROUNDUP
Rounds a number up away from 0.
=ROUNDUP(number,num_digits)
SIGN
Determines the sign (positive or negative) of number.
=SIGN(number)
SQRT
Returns the positive square root of number.
=SQRT(number)
SUBTOTAL
Returns a subtotal from a list or database.
=SUBTOTAL(function_num,ref1,ref2,...)
SUM
Adds the numbers in a range of cells.
=Sum(number1,number2,...)
SUMIF
Adds the cells specified by criteria.
=SUMIF(range,criteria,sum_range)
SUMPRODUCT
Multiplies corresponding components in the given array, and returns the sum of those products.
=SUMPRODUCT(array1,array2,array3,...)
TRUNC
Truncates number to an integer, removing the fractional part of the number. Note that this function
does not round number.
=TRUNC(number,num_digits)
Statistical Functions
AVERAGE
Returns the average of the arguments.
=AVERAGE(number1,number2,...)
COUNT
Counts the number of cells that contain numbers within the list of arguments.
=COUNT(value1,value2,...)
COUNTA
Counts the number of cells that are not empty within the list of arguments.
=COUNTA(value1,value2,...)
LARGE
Returns the k-th largest value in a data set.
=LARGE(array,k)
MAX
Returns the largest value in a set of values.
=MAX(number1,number2,...)
MAXA
Returns the largest value in a list of arguments.
=MAXA(value1,value2,...)
MIN
Returns the smallest number in a set of values. Nonnumeric values are ignored.
=MIN(number1,number2,...)
MINA
Returns the smallest value in a list of arguments including text and logical values such as "No
Grade", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=MINA(value1,value2,...)
RANK
Returns the rank of a number in a list of numbers.
=RANK(number,ref,order)
Text Functions
CHAR
Returns the character specified by a number.
=CHAR(number)
CLEAN
Removes all nonprintable characters from text.
=CLEAN(text)
CODE
Returns a numeric code from the first character in a text string.
=CODE(text)
CONCATENATE
Joins several text strings into one text string.
=CONCATENATE(text1,text2,...)
DOLLAR
Converts a number to text using Currency format, with the decimals rounded to the specified place.
The format used is $#,##0.00_);($#,##0.00).
=DOLLAR(number,decimals)
EXACT
Compares two text strings and returns TRUE if they're exactly the same, and FALSE otherwise.
EXACT is case sensitive.
=EXACT(text1,text2)
FIND
Finds one text string with another text string, and returns the number of the starting position
of find_text, from the leftmost character of within_text. FIND is case sensitive and cannot include
wildcard characters.
=FIND(find_text,within_text,start_num)
FINDB
Finds one text string with another text string, and returns the number of the starting position
of find_text, from the leftmost character of within_text. FINDB is case sensitive and cannot include
wildcard characters. This function is for use with double-byte characters.
=FINDB(find_text,within_text,start_num)
FIXED
Rounds a number to a specified number of decimals, formats the number in decimal format using a
period and commas, and returns the result as text.
=FIXED(number,decimals,no_commas)
LEFT
Returns the first character or characters in a text string.
=LEFT(text,num_char)
LEN
Returns the number of characters in a text string.
=LEN(text)
LOWER
Converts all uppercase letters in a text string to lowercase.
=LOWER(text)
MID
Returns a specific number of characters from a text string, starting at the position you specify.
=MID(text,start_num,num_char)
PROPER
Capitalizes the first letter in a text string and any other letters in text that follow any character other
than a letter.
=PROPER(text)
REPLACE
Replaces part of a text string with a different text string based on the number of characters you
specify.
=REPLACE(old_text,start_num,num_chars,new_text)
REPLACEB
Replaces part of a text string with a different text string, based on the number of bytes you specify.
=REPLACEB(old_text,start_num,num_bytes,new_text)
REPT
Repeats text a given number of times.
=REPT(text,number_times)
RIGHT
Returns the last character or characters in a text string.
=RIGHT(text,num_chars)
SEARCH
Returns the number of the character at which a specific character or text string is first found, reading
from left to right. SEARCH is not case sensitive and can include wildcard characters.
=SEARCH(find_text,within_text,start_num)
SEARCHB
Returns the number of the double-byte character at which a specific character or text string is first
found, reading from left to right. SEARCHB is not case sensitive and can include wildcard
characters.
=SEARCHB(find_text,within_text,start_num)
SUBSTITUTE
Substitutes new_text for old_text in a text string.
=SUBSTITUTE(text,old_text,new_text,instance_num)
T
Returns the text referred to by value.
=T(value)
TEXT
Converts a value to text in a specific number format.
=TEXT(value,format_text)
TRIM
Removes all spaces from text except for single spaces between words.
=TRIM(text)
UPPER
Converts text to uppercase.
=UPPER(text)
VALUE
Converts a text string that represents a number to a number.
=VALUE(text)