101 Excel Functions you should know
Below is a brief overview of about 100 important Excel functions you should know, with links to detailed examples. We also have a large list of example formulas, a more complete list of Excel functions, and video training. If you are new to Excel formulas, see this introduction.
Date and Time Functions
NOW and TODAY
You can get the current date with the TODAY function and the current date and time with the NOW Function. Technically, the NOW function returns the current date and time, but you can format as time only, as seen below:
DAY, MONTH, YEAR, and DATE
HOUR, MINUTE, SECOND, and TIME
Excel provides a set of parallel functions for times. You can use the HOUR, MINUTE, and SECOND functions to extract pieces of a time, and you can assemble a TIME from individual components with the TIME function.
DATEDIF and YEARFRAC
You can use the DATEDIF function to get time between dates in years, months, or days. DATEDIF can also be configured to get total time in "normalized" denominations, i.e. "2 years and 6 months and 27 days".
Use YEARFRAC to get fractional years:
=YEARFRAC("14-Nov-2018","10-Jun-2021") // returns 2.57
EDATE and EOMONTH
A common task with dates is to shift a date forward (or backward) by a given number of months. You can use the EDATE and EOMONTH functions for this. EDATE moves by month and retains the day. EOMONTH works the same way, but always returns the last day of the month.
WORKDAY and NETWORKDAYS
WORKDAY(start,n,holidays) // date n workdays in future
NETWORKDAYS(start,end,holidays) // number of workdays between dates
Note: Both functions automatically skip weekends (Saturday and Sunday) and will also skip holidays, if provided. If you need more flexibility on what days are considered weekends, see the WORKDAY.INTL function and NETWORKDAYS.INTL function.
WEEKDAY and WEEKNUM
To figure out the day of week from a date, Excel provides the WEEKDAY function. WEEKDAY returns a number between 1-7 that indicates Sunday, Monday, Tuesday, etc. Use the WEEKNUM function to get the week number in a given year.
Most Engineering functions are pretty technical...you'll find a lot of functions for complex numbers in this section. However, the CONVERT function is quite useful for everyday unit conversions. You can use CONVERT to change units for distance, weight, temperature, and much more.
=CONVERT(72,"F","C") // returns 22.2
ISBLANK, ISERROR, ISNUMBER, and ISFORMULA
Excel provides many functions for checking the value in a cell, including ISNUMBER, ISTEXT, ISLOGICAL, ISBLANK, ISERROR, and ISFORMULA These functions are sometimes called the "IS" functions, and they all return TRUE or FALSE based on a cell's contents.
By the way, the green fill in the screenshot above is applied automatically with a conditional formatting formula.
Excel's logical functions are a key building block of many advanced formulas. Logical functions return the boolean values TRUE or FALSE. If you need a primer on logical formulas, this video goes through many examples.
AND, OR and NOT
IFERROR and IFNA
The IFERROR function and IFNA function can be used as a simple way to trap and handle errors. In the screen below, VLOOKUP is used to retrieve cost from a menu item. Column F contains just a VLOOKUP function, with no error handling. Column G shows how to use IFNA with VLOOKUP to display a custom message when an unrecognized item is entered.
Whereas IFNA only catches an #N/A error, the IFERROR function will catch any formula error.
IF and IFS functions
The IF function is one of the most used functions in Excel. In the screen below, IF checks test scores and assigns "pass" or "fail":
Multiple IF functions can be nested together to perform more complex logical tests.
Lookup and Reference Functions
VLOOKUP and HLOOKUP
Excel offers a number of functions to lookup and retrieve data. Most famous of all is VLOOKUP:
INDEX and MATCH
The LOOKUP function has default behaviors that make it useful when solving certain problems. LOOKUP assumes values are sorted in ascending order and always performs an approximate match. When LOOKUP can't find a match, it will match the next smallest value. In the example below we are using LOOKUP to find the last entry in a column:
ROW and COLUMN
The row function also shows up often in advanced formulas that process data with relative row numbers.
ROWS and COLUMNS
Note ROWS returns a count of data rows in a table, excluding the header row. By the way, here are 23 things to know about Excel Tables.
You can use the HYPERLINK function to construct a link with a formula. Note HYPERLINK lets you build both external links and internal links:
The GETPIVOTDATA function is useful for retrieving information from existing pivot tables.
The CHOOSE function is handy any time you need to make a choice based on a number:
=CHOOSE(2,"red","blue","green") // returns "blue"
The TRANSPOSE function gives you an easy way to transpose vertical data to horizontal, and vice versa.
Note: TRANSPOSE is a formula and is, therefore, dynamic. If you just need to do a one-time transpose operation, use Paste Special instead.
The OFFSET function is useful for all kinds of dynamic ranges. From a starting location, it lets you specify row and column offsets, and also the final row and column size. The result is a range that can respond dynamically to changing conditions and inputs. You can feed this range to other functions, as in the screen below, where OFFSET builds a range that is fed to the SUM function:
The INDIRECT function allows you to build references as text. This concept is a bit tricky to understand at first, but it can be useful in many situations. Below, we are using INDIRECT to get values from cell A1 in 5 different worksheets. Each reference is dynamic. If a sheet name changes, the reference will update.
=INDIRECT(B5&"!A1") // =Sheet1!A1
The INDIRECT function is also used to "lock" references so they won't change, when rows or columns are added or deleted. For more details, see linked examples at the bottom of the INDIRECT function page.
Caution: both OFFSET and INDIRECT are volatile functions and can slow down large or complicated spreadsheets.
COUNT and COUNTA
You can count numbers with the COUNT function and non-empty cells with COUNTA. You can count blank cells with COUNTBLANK, but in the screen below we are counting blank cells with COUNTIF, which is more generally useful.
COUNTIF and COUNTIFS
SUM, SUMIF, SUMIFS
To sum everything, use the SUM function. To sum conditionally, use SUMIF or SUMIFS. Following the same pattern as the counting functions, the SUMIF function can apply only one criteria while the SUMIFS function can apply multiple criteria.
AVERAGE, AVERAGEIF, and AVERAGEIFS
MIN, MAX, LARGE, SMALL
Note: MINIFS and MAXIFS are new in Excel via Office 365 and Excel 2019.
The MODE function returns the most commonly occurring number in a range:
=MODE(B5:G5) // returns 1
To rank values largest to smallest, or smallest to largest, use the RANK function:
To change negative values to positive use the ABS function.
=ABS(-134.50) // returns 134.50
RAND and RANDBETWEEN
Both the RAND function and RANDBETWEEN function can generate random numbers on the fly. RAND creates long decimal numbers between zero and 1. RANDBETWEEN generates random integers between two given numbers.
ROUND, ROUNDUP, ROUNDDOWN, INT
To round values up or down, use the ROUND function. To force rounding up to a given number of digits, use ROUNDUP. To force rounding down, use ROUNDDOWN. To discard the decimal part of a number altogether, use the INT function.
MROUND, CEILING, FLOOR
The MOD function returns the remainder after division. This sounds boring and geeky, but MOD turns up in all kinds of formulas, especially formulas that need to do something "every nth time". In the screen below, you can see how MOD returns zero every third number when the divisor is 3:
The SUMPRODUCT function is a powerful and versatile tool when dealing with all kinds of data. You can use SUMPRODUCT to easily count and sum based on criteria, and you can use it in elegant ways that just don't work with COUNTIFS and SUMIFS. In the screen below, we are using SUMPRODUCT to count and sum orders in March. See the SUMPRODUCT page for details and links to many examples.
The SUBTOTAL function is an "aggregate function" that can perform a number of operations on a set of data. All told, SUBTOTAL can perform 11 operations, including SUM, AVERAGE, COUNT, MAX, MIN, etc. (see this page for the full list). The key feature of SUBTOTAL is that it will ignore rows that have been "filtered out" of an Excel Table, and, optionally, rows that have been manually hidden. In the screen below, SUBTOTAL is used to count and sum only the 7 visible rows in the table:
Like SUBTOTAL, the AGGREGATE function can also run a number of aggregate operations on a set of data and can optionally ignore hidden rows. The key differences are that AGGREGATE can run more operations (19 total) and can also ignore errors.
In the screen below, AGGREGATE is used to perform MIN, MAX, LARGE and SMALL operations while ignoring errors. Normally, the error in cell B9 would prevent these functions from returning a result. See this page for a full list of operations AGGREGATE can perform.
LEFT, RIGHT, MID
To look for specific text in a cell, use the FIND function or SEARCH function. These functions return the numeric position of matching text, but SEARCH allows wildcards and FIND is case-sensitive. Both functions will throw an error when text is not found, so wrap in the ISNUMBER function to return TRUE or FALSE (example here).
To replace text by position, use the REPLACE function. To replace text by matching, use the SUBSTITUTE function. In the first example, REPLACE removes the two asterisks (**) by replacing the first two characters with an empty string (""). In the second example, SUBSTITUTE removes all hash characters (#) by replacing "#" with "".
To figure out the numeric code for a character, use the CODE function. To translate the numeric code back to a character, use the CHAR function. In the example below, CODE translates each character in column B to its corresponding code. In column F, CHAR translates the code back to a character.
CONCAT, TEXTJOIN, CONCATENATE
New in Excel via Office 365 are CONCAT and TEXTJOIN. The CONCAT function lets you concatenate (join) multiple values, including a range of values without a delimiter. The TEXTJOIN function does the same thing, but allows you to specify a delimiter and can also ignore empty values.
The EXACT function allows you to compare two text strings in a case-sensitive manner.
UPPER, LOWER, PROPER
Last but definitely not least is the TEXT function. The text function lets you apply number formatting to numbers (including dates, times, etc.) as text. This is especially useful when you need to embed a formatted number in a message, like "Sale ends on [date]".
Dynamic Array functions
Dynamic arrays are new in Excel 365, and are a major upgrade to Excel's formula engine. As part of the dynamic array update, Excel includes new functions which directly leverage dynamic arrays to solve problems that are traditionally hard to solve with conventional formulas. If you are using Excel 365, make sure you are aware of these new functions:
|FILTER||Filter data and return matching records|
|RANDARRAY||Generate array of random numbers|
|SEQUENCE||Generate array of sequential numbers|
|SORT||Sort range by column|
|SORTBY||Sort range by another range or array|
|UNIQUE||Extract unique values from a list or range|
|XLOOKUP||Modern replacement for VLOOKUP|
|XMATCH||Modern replacement for the MATCH function|
Video: New dynamic array functions in Excel (about 3 minutes).
ABS, AGGREGATE, AND, AVERAGE, AVERAGEIF, AVERAGEIFS, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, CONCAT, CONCATENATE, CONVERT, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEDIF, DAY, EDATE, EOMONTH, EXACT, FIND, FLOOR, GETPIVOTDATA, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IFNA, IFS, INDEX, INDIRECT, INT, ISBLANK, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNUMBER, ISODD, ISTEXT, LARGE, LEFT, LEN, LOOKUP, LOWER, MATCH, MAX, MAXIFS, MID, MIN, MINIFS, MINUTE, MOD, MODE, MONTH, MROUND, NETWORKDAYS, NOT, NOW, OFFSET, OR, PROPER, RAND, RANDBETWEEN, RANK, REPLACE, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SMALL, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, TEXT, TEXTJOIN, TIME, TODAY, TRANSPOSE, TRIM, UPPER, VLOOKUP, WEEKDAY, WEEKNUM, WORKDAY, YEAR, YEARFRAC