Microsoft Excel Function List

Listing of the most useful Microsoft Excel functions. Explanations on how to write each function in Excel and examples of its use are provided.

Mathematical Functions

SUM - Adds up all the values in a range

SUMIF - Adds all the values in a range that meet specific critera

SUMIFS (2007+) - Adds values in a range based on multiple criteria

SUMPRODUCT - Sum a range of cells that meet multiple criteria

ROUND - Round a number to a specified number of digits

ROUNDUP - Round a number up to a specified number of digits

ROUNDDOWN - Round a number down to a specified number of digits

CEILING - Round a number up to a multiple of significance

FLOOR - Round a number down to a multiple of significance

Statistical Functions

COUNT - Counts all the values in a range

AVERAGE - Calculates the average number from a range of values

MAX - Finds the maximum value in a range

MIN - Finds the minimum value in a range

COUNTA - Counts all non-empty cells in a range

COUNTBLANK - Counts all blank cells in a range

COUNTIF - Counts all the cells in a range that meet specific critera

COUNTIFS (2007+) - Counts all the cells in a range that meet multiple criteria

AVERAGEIF (2007+) - Calculates the average of a range of values that meet specific criteria

AVERAGEIFS (2007+) - Calculates the average of a range of values that meet multiple criteria

LARGE - Return a value dependent upon its ranking in a range of values in descending order

SMALL - Return a value dependent upon its ranking in a range of values in ascending order

RANK - Returns the rank or position of a number within a range of numbers

Text Functions

LEN - Returns the length, in number of characters, of the contents of a cell

REPT - Repeats a character a specified number of times

TRIM - Remove unwanted spaces from cells

LEFT - Extracts a specific number of characters from the start of a cell

RIGHT - Extracts a specific number of characters from the end of a cell

MID - Extracts a specific number of characters from the middle of a cell

UPPER - Converts the contents of a cell to uppercase

LOWER - Converts the contents of a cell to lowercase

PROPER - Converts the contents of a cell to proper case

REPLACE - Replace existing characters in a cell with a different set of characters

SUBSTITUTE - Replace existing characters with a different set of characters

Financial Functions

PMT - Calculates loan repayments based on constant payments and a constant interest rate

RATE - Returns the interest rate per period of a loan or investment

PV - Returns the present value of an investment based on a constant interest rate and payments

FV - Returns the future value of an investment based on constant payments and a constant interest rate

IPMT - Calculates the interest paid during a period of a loan or investment

PPMT - Calculates the principal payment made in a period of an investment

IRR - Returns the internal rate of return on a series of regular investments

XIRR - Returns the internal rate of return on a series of irregular payments on an investment

NPV - Returns the net present value of an investment based on a series of cash flows and a discount rate

XNPV - Returns the net present value of an investment based on a series of cash flows, the dates of the cash flows and a discount rate

Lookup and Reference Functions

VLOOKUP - Looks vertically down a list to find a record and returns information related to that record

HLOOKUP - Looks horizontally across a list to find a record and returns information related to that record

MATCH - Returns the position of a value in a list

INDEX - Returns an item from a specific position in a list

INDIRECT - Allows you to use a cell reference entered as a text string

OFFSET - Returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell

CHOOSE - Returns a value from a list of values based on a specified position

ADDRESS - Returns a text representation of a cell address from specified row and column numbers

Logical Functions

IF - Tests a condition and takes an alternative action depending on the result

AND - Test up to 30 conditions using logical And

OR - Test up to 30 conditions using logical Or

IFERROR - Performs a specified action if a formula evaluates to an error, and displays the formula result if not

Date & Time Functions

TODAY - Returns the current date

NOW - Returns the current date and time

DATE - Returns the sequential serial number for the specified date and formats the result as a date

DAY - Returns the day corresponding to a date represented by a number between 1 and 31

MONTH - Returns the month corresponding to a date represented by a number between 1 and 12

YEAR - Returns the year corresponding to a date represented by a number in the range 1900 to 9999

WORKDAY - Returns the date a specified number of workings days before or after a date

WEEKDAY - Returns the day of the week corresponding to a specified date

NETWORKDAYS - Returns the number of workdays between two dates

EOMONTH - Calculates the last day of the month a specified number of months before or after a date