Most Important Excel Formulas

Most Important Excel Formulas

Most Important Excel Formulas

Logical Functions Library

=FALSE()
Returns the logical value FALSE.
=TRUE()
Returns the logical value TRUE.
=NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE.
=AND(logical1, [logical2], ...)
Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.
=OR(logical1, [logical2], ...)
Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Return FALSE only if all arguments are FALSE.
=IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
=IFERROR(value, value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself otherwise.

Financial Functions Library

=PMT(rate, nper, pv, [fv], [type])
Calculates the payment for a loan based on constant payments and a constant interest rate.
=IPMT(rate, per, nper, pv, [fv], [type])
Returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate.
=PPMT(rate, per, nper, pv, [fv], [type])
Returns the payment on the principal for a given investment based on periodic, constant payments and a constant interest rate.
=PV(rate, nper, pmt, [fv], [type])
Returns the present value of an investment: the total amount that a series of future payment is worth now.
=FV(rate, nper, pmt, [pv], [type])
Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Text Functions Library

=CHAR(number)
Returns the character specified by the code number from the character set for your computer.
=CODE(text)
Returns a numberic code for the first character in a text string, in the character set used by your computer.
=CLEAN(text)
Removes all nonprintable characters from text.
=TRIM(text)
Removes all spaces from a text string except for single spaces between words.
=CONCATENATE(text1, [text2], ...)
Joins several text strings into one text string.
=DOLLAR(number, [decimals])
Converts a number to text, using currency format.
=EXACT(text1, text2)
Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive.
=FIXED(number, [decimals], [no_commas])
Rounds a number to the specified number of decimals and returns the result as text with or without commas.
=LEN(text)
Returns the number of characters in a text string.
=LOWER(text)
Converts all letters in a text string to lowercase.
=PROPER(text)
Converts a text string to proper case; the first letter in each word in uppercase, and all other letters to lowercase.
=UPPER(text)
Converts a text string to all uppercase letters.
=LEFT(text, [num_chars])
Returns the specified number of characters from the start of a text string.
=MID(text, start_num, num_char)
Returns the characters from the middle of a text string, given a starting position and length.
=RIGHT(text, [num_chars])
Returns the specified number of characters from the end of a text string.
=FIND(find_text, within_text, [start_num])
Returns the starting position of one text string within another text string. FIND is case-sensitive.
=SEARCH(find_text, within_text, [start_num])
Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).
=REPT(text, number_times)
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of text string.
=REPLACE(old_text, start_num, num_chars, new_text)
Replaces part of a text string with a different text string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Replaces existing text with new text in a text string.
=T(value)
Checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it is not.
=TEXT(value, format_text)
Converts a value to text in a specific number format.
=VALUE(text)
Converts a text string that represents a number to a number.

Date & Time Library

=NOW()
Returns the current date and time formatted as a date and time.
=TODAY()
Returns the current date formatted as a date.
=DATE(year, month, day)
Returns the number that represents the date in Microsoft Office Excel date-time code.
=DATEVALUE(date_text)
Converts a date in the form of text to a number that represents the date in Microsoft Office Excel date-time code.
=DAY(serial_number)
Returns the day of the month, a number from 1 to 31.
=MONTH(serial_number)
Returns the month, a number from 1 (January) to 12 (December).
=YEAR(serial_number)
Returns the year of a date, an integer in the range 1900-9999.
=TIME(hour, minute, second)
Converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format.
=TIMEVALUE(time_text)
Converts a text time to an Excel serial number for a time, a number from 0 (12:00 AM) to 0.999988426 (11:59:59 PM). Format the number with a time format after entering the formula.
=HOUR(serial_number)
Returns the hour as a number from 0 (12:00 AM) to 23 (11:00 PM).
=MINUTE(serial_number)
Returns the minute, a number from 0 to 59.
=SECOND(serial_number)
Returns the second, a number from 0 to 59.
=EDATE(start_date, months)
Returns the serial number of the date that is the indicated number of months before or after the start date.
=EOMONTH(start_date, months)
Returns the serial number of the last day of the month before or after a specified number of months.
=DAYS360(start_date, end_date, [method])
Returns the number of days between two dates based on a 360-day year (twelve 30-day months).
=NETWORKDAY(start_date, end_date, holidays)
Returns the number of whole workdays between two dates.
=WORKDAY(start_date, days, holidays)
Returns the serial number of the date before or after a specified number of workdays.
=WEEKDAY(serial_number, return_type)
Returns a number from 1 to 7 identifying the day of the week of a date. Serial_number is a number that represents a date.
=WEEKNUM(serial_number, return_type)
Returns the week number in the year.
=YEARFRAC(start_date, end_date, [basis])
Returns the year fraction representing the number of whole days between start_date and end_date.

Lookup & Reference Library

=COLUMN(reference)
Returns the column number of a reference.
=COLUMNS(array)
Returns the number of columns in an array or reference.
=ROW(reference)
Returns the row number of a reference.
=ROWS(array)
Returns the number of rows in an array or reference.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Creates a cell reference as text, given specified row and column numbers.
=CHOOSE(index_num, value1, [value2], ...)
Chooses a value or action to perform from a list of values, based on an index number.
=INDIRECT(ref_text, [a1])
Returns the reference specified by a text string.
=OFFSET(reference, rows, cols, [height], [width])
Returns a reference to a range that is a given number of rows and columns from a given reference.
=HYPERLINK(link_location, [friendly_name])
Creates a shortcut or jump that opens a document stored on your hard drive, a network server, or on the Internet.
=INDEX(array, row_num, [column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
=MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specified order.
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Looks up a value either from a one-row or one-column range or from an array.
=HLOOKUP(lookup_value, table_array, row_index_num, [range-lookup])
Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify.
=VLOOKUP(lookup_value, table_array, col_index_num, [range-lookup])
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted.
=TRANSPOSE(array)
Converts a vertical range of cells to a horizontal range, or vice-versa. (Use Ctrl+Shift+Enter)

Math Functions Library

=ABS(number)
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
=CEILING(number, significance)
Rounds a number up, to the nearest integer or to the nearest multiple of significance.
=FLOOR(number, significance)
Rounds a number down, toward zero, to the nearest multiple of significance.
=EVEN(number)
Rounds a positive number up and negative number down to the nearest even integer.
=ODD(number)
Rounds a positive number up and negative number down to the nearest odd integer.
=INT(number)
Rounds a number down to the nearest integer.
=LOG10(number)
Returns the base-10 logarithm of a number.
=ROUND(number, num_digits)
Rounds a number to a specified number of digits.
=ROUNDDOWN(number, [num_digits])
Rounds a number down, toward zero.
=ROUNDUP(number, [num_digits])
Rounds a number up, away from zero.
=MROUND(number, multiple)
Returns a number rounded to the desired multiple.
=FACT(number)
Returns the factorial of a number, equal to 1*2*3*...*number.
=FACTDOUBLE(number)
Returns the double factorial of a number.
=GCD(number1, [number2], ...)
Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.
=LCM(number1, [number2], ...)
Returns the least common multiple of integers.
=MOD(number, divisor)
Returns the remainder after a number is divided by a divisor.
=PI()
Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
=POWER(number, power)
Returns the result of a number raised to a power.
=PRODUCT(number1, [number2], ...)
Multiplies all the numbers given as arguments.
=QUOTIENT(numerator, denominator)
Returns the integer portion of a divison.
=RAND()
Returns a random number greater than or equal to 0 and less than 1, evenly distributed (changes on recalculation).
=RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify.
=ROMAN(number, [form])
Converts an Arabic numerals to Roman, as text.
=SIGN(number)
Returns the sign of a number. 1 if the number is positive, zero if the number is zero, or -1 if the number is negative.
=SQRT(number)
Returns the square root of a number.
=SUBTOTAL(function_num, ref1, ...)
Returns a subtotal in a list or database.
=SUM(number1, [number2], ...)
Adds all the numbers in a range of cells.
=SUMIF(range, criteria, [sum_range])
Adds the cells specified by a given condition or criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Adds the cells specified by a given set of condition or criteria.
=SUMPRODUCT(array1, [array2], ...)
Returns the sum of the products of corresponding ranges or arrays.
=SUMSQ(number1, [number2], ...)
Returns the sum of the squares of the arguments. The arguments can be numbers, arrays, names, or references to cells that contain numbers.
=TRUNC(number, [num_digits])
Truncates a number to an integer by removing the decimal, or fractional, part of the number.

More Functions Library

=AVERAGE(number1, [number2], ...)
Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers. Ignores logical values and text.
=AVERAGEA(value1, [value2], ...)
Returns the average of its arguments, evaluating text and FALSE in arguments as 0; TRUE evaluates as 1. Arguments can be numbers, names, arrays or references.
=AVERAGEIF(range, criteria, [average_range])
Finds average for the cells specified by a given condition or criteria.
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Finds average for the cells specified by a given set of conditions or criteria.
=COUNT(value1, [value2], ...)
Counts the number of cells in a range that contain numbers.
=COUNTA(value1, [value2], ...)
Counts the number of cells in a range that are not empty.
=COUNTBLANK(range)
Counts the number of empty cells in a specified range of cells.
=COUNTIF(range, criteria)
Counts the number of cells within a range that meet the given condition.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Counts the number of cells specified by a given set of conditions or criteria.
=LARGE(array, k)
Returns the k-th largest value in a data set. For example, the fifth largest number.
=SMALL(array, k)
Returns the k-th smallest value in a data set. For example, the fifth smallest number.
=MAX(number1, [number2], ...)
Returns the largest value in a set of values. Ignores logical values and text.
=MAXA(value1, [value2], ...)
Returns the largest value in a set of values. Does not ignore logical values and text.
=MEDIAN(number1, [number2], ...)
Returns the median, or the number in the middle of the set of given numbers.
=MIN(number1, [number2], ...)
Returns the smallest number in a set of values. Ignores logical value and text.
=MINA(value1, [value2], ...)
Returns the smallest number in a set of values. Does not ignore logical values and text.
=MODE(number1, [number2], ...)
Returns the most frequently occurring, or repetitive, value in an array or range of data.
=PERMUT(number, number_chosen)
Returns the number of permutations of a given number of objects that can be selected from the total objects.
=COMBIN(number, number_chosen)
Returns the number of combinations for a given number of items.
=RANK(number, ref, [order])
Returns the rank of a number in a list of numbers: Its size relative to other values in the list.
=ISBLANK(value)
Checks whether a reference is to an empty cell, and returns TRUE or FALSE.
=ISERR(value)
Checks whether a value is an error (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) excluding #N/A, and returns TRUE or FALSE.
=ISERROR(value)
Checks whether a value is an error (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) including #N/A, and returns TRUE or FALSE.
=ISREF(value)
Checks whether a value is a reference, and returns TRUE or FALSE.
=ISEVEN(number)
Returns TRUE if the number is even.
=ISODD(number)
Returns TRUE if the number is odd.
=ISLOGICAL(value)
Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE.
=ISNA(value)
Checks whether a value is #N/A, and returns TRUE or FALSE.
=ISNONTEXT(value)
Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE.
=ISNUMBER(value)
Checks whether a value is a number, and returns TRUE or FALSE.
=ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE.
=N(value)
Converts non-number value to a number, dates to serial number. TRUE to 1, anything else to 0 (zero).
=NA()
Returns the error value #N/A (value not available)
=TYPE(value)
Returns an integer representing the data type of a value: number => 1; text => 2; logical value => 4; error value => 16; array => 64; compound => 128.
=INFO(type_text)
Returns information about the current operating environment.
type_text: Text that specifies what type of information you want returned. Such as: "numfile", "directory", "origin", "osversion", "recalc", "release", "system".