This is a comprehensive Excel functions list complete with explanations on how to write each function in Excel and examples of their use.
This comprehensive list of Excel formulas and functions can be thought of as an Excel functions cheat sheet. A list that you can regularly return to for help on how to use functions in Excel.
These in-depth Excel tutorials show multiple practical examples of each function to understand them completely. Check out our free downloads for more Excel tips.
Contents
- New Excel functions
- Mathematical functions
- Logical functions
- Lookup and reference functions
- Date and time functions
- Statistical functions
- Text functions
- Financial functions
- Information Functions
New Excel Functions
These are new functions released by Microsoft within the last year or two.
LAMBDA – Create your own functions in Excel. How awesome is that? The Excel function to create functions yourself.
IMAGE – Insert images on a worksheet dynamically from a given URL.
PERCENTOF – Calculates the percentage one number is of another.
GROUPBY – Create summary reports from a single Excel formula. Can perform 17 different aggregation functions including LAMBDA.
Mathematical Functions in Excel
AGGREGATE – Perform an aggregation such as sum, average or max and ignore specific rows such as those that contain errors, subtotals, or have been filtered in the range.
CEILING – Round a number up to a multiple of significance.
FLOOR – Round a number down to a multiple of significance.
MOD – Returns the remainder after a number is divided by a divisor
ROUND – Round a number to a specified number of digits.
ROUNDDOWN – Round a number down to a specified number of digits.
ROUNDUP – Round a number up to a specified number of digits.
SEQUENCE – Returns a sequence of numbers.
SUM – Add up all values in a range. The most used and best Excel function of them all. In modern Excel, this is even more powerful than people realise.
SUMIF – Add all the values in a range that meet specific criteria.
SUMIFS – Add values in a range that meet one or more criteria.
SUMPRODUCT – Returns the sum of products from corresponding ranges or arrays. This function is very powerful and has some special uses.
Logical Excel Functions
These functions introduce conditional logic to a spreadsheet. Test values stored in a cell, or returned by another Excel formula, and take the required action dependent on the result of that test.
IF – Tests a condition and takes an alternative action depending on the result. The most important logical function in Excel to know, and one of the best Excel functions.
IFERROR – Performs a specified action if a formula evaluates to an error, and displays the formula result if not
AND – Test up to 30 conditions using logical AND.
OR – Test up to 30 conditions using logical OR.
SWITCH – Compares an expression against a list of values, and performs the result for the matching value. The SWITCH function in Excel is a great alternative to nested IF functions when performing multiple conditional tests.
Lookup and Reference Functions in Excel
These are some of the most commonly used and exciting functions in this Excel functions list. They are used to combine data from different sheets, compare lists, and create dynamic reports.
XLOOKUP – Complete lookup formula to look up and return values
VLOOKUP – Looks vertically down a list to find a matching value and returns the corresponding value from a specified column. This is a very commonly used function in Excel, so very important to learn.
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. Another of the best Excel functions. A big favourite of mine.
FILTER – Returns an array that meets specific criteria. One of the best Excel functions.
SORT – Dynamically sort one or more columns of an array.
UNIQUE – Returns the unique or distinct list of values from a given range or array. This top Excel function is a favourite for many.
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.
GETPIVOTDATA – Lookup and return data from a PivotTable.
Date & Time Functions
Excel has many very useful date functions. These Excel functions are used for a variety of date and time calculations.
These include, but are not limited to, calculating the difference in dates Excel compares in days, working days, months or years. Or to calculate future or past dates a given number of months from another date.
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 and WORKDAY.INTL – Returns the date a specified number of workings days before or after a given 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.
Statistical Excel 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 criteria.
COUNTIFS – Counts all the cells in a range that meet multiple criteria. One of the most useful Excel functions there is.
AVERAGEIF – Calculates the average of a range of values that meet specific criteria.
AVERAGEIFS – Calculates the average of a range of values that meet multiple criteria.
MAXIFS – Return the maximum value based on specified 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
CONCATENATE – Combine multiple text strings together. Possibly the most well-known text function in Excel. Has been succeeded by the CONCAT function in modern versions of Excel.
LEFT – Extracts a specific number of characters from the start of a cell.
LEN – Returns the length, in number of characters, of the contents of a cell.
LOWER – Converts the contents of a cell to lowercase.
MID – Extracts a specific number of characters from the middle of a cell.
PROPER – Converts the contents of a cell to proper case.
REPLACE – Replace existing characters in a cell with a different set of characters.
REPT – Repeats a character a specified number of times.
RIGHT – Extracts a specific number of characters from the end of a cell.
SUBSTITUTE – Replace existing characters with a different set of characters.
TEXTBEFORE and TEXTAFTER – Extract characters before or after a specified delimiter.
TRIM – Remove unwanted spaces from cells.
UPPER – Converts the contents of a cell to uppercase.
Financial Excel 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
Information Functions in Excel
CELL – Returns information about a given cell such as its locked status, cell colour, column number or file path.
N function – Return a number from a given value. This tutorial shows two examples of how N can be used.
