The complete Excel Functions list includes lookup, logic, date and time, text, information, and math functions with examples. Excel Functions are built-in presets, so Excel functions are hard-coded formulas with short, user-friendly names.
The list contains 300+ built-in Excel functions. Furthermore, look at the library of useful advanced functions (UDFs). If you want to jump to a specific category, use the list below.
- User-defined functions (must-have)
- Logical
- Text
- Lookup
- Date and Time
Excel Function List
Logical
The functions below use logical tests and logical operators to evaluate a formula. Learn more about Boolean expressions.
| Function | How it works |
|---|---|
| AND | Test multiple conditions using AND operator |
| FALSE | Returns the Boolean value FALSE |
| IF | It runs a logical test and returns one value for a TRUE result and another for a FALSE result. |
| IFS | Runs multiple tests and returns a value corresponding to the first TRUE result |
| IFNA | Manage #N/A errors |
| IFERROR | Manage all formula errors |
| NOT | Get the opposite of a given logical or Boolean value |
| OR | Test multiple conditions using the OR operator |
| SWITCH | Match multiple values, return the first match |
| TRUE | Returns the Boolean value TRUE |
| XOR | Performs exclusive OR |
LOOKUP
Here is the list of lookup functions in Excel. Of course, we already know that XLOOKUP changes everything. But it is worth keeping in mind the older lookup functions too.
| Function | How it works |
|---|---|
| ADDRESS | Create a cell reference as text from a row and column number |
| AREAS | Get the number of areas in a reference. |
| CHOOSE | Get a value from a list based on position |
| COLUMN | Get the column number of a reference |
| COLUMNS | Get the number of columns in an array or reference |
| FORMULATEXT | Show the formula in a cell |
| HLOOKUP | Lookup a value in a horizontally arranged table |
| HYPERLINK | Create a link in a cell |
| INDEX | Returns the value at a given location in a range or array |
| INDIRECT | Create a cell reference from a given text string |
| LOOKUP | Lookup a value in a one-column range (an approximate match) |
| MATCH | Returns the relative position of an item in an array that matches a specified value in a specified order. |
| OFFSET | Returns a dynamic range constructed with five inputs: starting point, row offset, column offset, row heights, column widths |
| ROW | Get the row number for a reference |
| ROWS | Count the rows in a given range |
| TRANSPOSE | Switch rows to columns and vice versa |
| VLOOKUP | Lookup a value in a table by matching on the first column |
| XLOOKUP | Look up data in a vertically organized table |
| XMATCH | Return an item’s relative position in an array. By default, an exact match is required. |
User-defined functions
UDFs are advanced functions.
| Function | How it works |
|---|---|
| ABBREVIATE | Abbreviate names or words |
| BETWEEN | Calculate SUM, COUNT, or AVERAGE between two numbers. |
| COMPARE | Compare two lists and get the common or different values. |
| COUNTBETWEEN | Counts the number of cells between two numbers. |
| COUNTTEXT | Count the number of cells that contain one or more strings. |
| COUNTX | Count the number of cells that contain various types of data. |
| GETNTHNUMBER | Get the nth number from a text string. |
| GETLASTWORD | Extract the last word from a text string. |
| INRANGE | Checks if a specific value or a range exists within another range |
| MLOOKUP | Lookup and return multiple values in one cell |
| NVLOOKUP | Lookup and return the nth value. |
| RTOTAL | Calculate running total |
| SUMBETWEEN | Sum if between two numbers |
| SUMCOLUMNS | Sum the last or first n columns in a range. |
| SUMHIGH | Sum the top N value in a range. |
| SUMN_ROWS | Sum every N rows |
| SUMVISIBLE | Sum visible rows only. |
| ZFILL | Add leading zeros. |
Date and Time
Date and Time functions help you create calculations based on dates and times.
| Function | How it works |
|---|---|
| DATE | Create a date from the individual year, month, and day components |
| DATEDIF | Returns the difference between two date values in years, months, or days |
| DATEVALUE | Converts a date defined as a text string into a valid Excel date |
| DAY | Return the day as a number (1-31) from a date |
| DAYS | Get the number of days between two dates |
| DAYS360 | Get days between 2 dates in a 360-day year |
| EDATE | Return the date on the same day of the month (past or future) |
| EOMONTH | Returns last day of month n months in the past or future |
| HOUR | Get the hour component of a time (0-23) |
| ISOWEEKNUM | Returns a week number (1-54) that follows ISO standards |
| MINUTE | Extracts the minute component of a time (0-59) |
| MONTH | Get a month from a date as a number (1-12) |
| NETWORKDAYS | Returns the number of working days between two dates |
| NETWORKDAYS.INTL | Calculates the number of working days between two dates |
| NOW | Return the current date and time |
| SECOND | Return the Second part as a number (0-59) from a time |
| TIME | Create a time using hour, minute, and second parts |
| TIMEVALUE | Converts text-format time into a proper Excel time |
| TODAY | Get the current date (updated automatically when a worksheet is changed) |
| WEEKDAY | Returns a number that represents the day of the week (1-7) |
| WEEKNUM | Get the week number for a given date |
| WORKDAY | Returns a date n working days in the future or past |
| WORKDAY.INTL | Get the week number for a given date (1-54) |
| YEAR | Returns the year component from a date (4-digit) |
| YEARFRAC | Returns the fraction of a year between two dates |
Text
Text functions in Excel enable you to perform various calculations using strings.
| Function | How it works |
|---|---|
| CHAR | Get a character from a number |
| CLEAN | Remove the non-printable characters from the text |
| CODE | Get a numeric code from a character |
| CONCAT | Joins separated text values into one without delimiter |
| CONCATENATE | Joins values and returns the result as text |
| DOLLAR | Converts a number to text using the Currency format |
| EXACT | Compare two text strings |
| FIND | Get the position (number) of text in a string |
| FIXED | Convert numbers to text with fixed decimals |
| LEFT | Extracts N characters from the left side of a text string |
| LEN | Get the number of characters in a given text string |
| LOWER | Convert text to lowercase |
| MID | Extract n characters from inside a string |
| NUMBERVALUE | Converts a number in text format to a numeric value using separators |
| PROPER | Capitalize the first letter in each word in a given string |
| REPLACE | Replaces characters specified by location in a text string with another text string |
| REPT | Repeats characters a given number of times |
| RIGHT | Extracts n characters from the right side of a selected text string |
| SEARCH | Get the location of text in a string (the position of the first character) |
| SUBSTITUTE | Replaces text in a given string |
| TEXT | Convert a number to text but keep the format |
| TEXTJOIN | Join text values using a delimiter |
| TRIM | Strips extra spaces from text |
| UNICHAR | Get a UNICODE character from a number |
| UNICODE | Get a number from a UNICODE character |
| UPPER | Convert text to uppercase |
| VALUE | Convert text to numeric format |
Dynamic Array
Dynamic Arrays are resizable arrays. From now, Excel calculates the array automatically and returns values into multiple cells based on a formula entered in a single cell.
| Function | How it works |
|---|---|
| BYCOL | Applies a LAMBDA function to each column |
| BYROW | Applies a LAMBDA function to row |
| FILTER | Filters a range using single or multiple criteria |
| LAMBDA | Create a custom function |
| LET | It lets you define named variables in a formula |
| MAKEARRAY | Create an array using calculated values |
| MAP | Maps a custom LAMBDA function |
| RANDARRAY | Generates an array of random numbers between |
| REDUCE | Reduce an array to a single value using the custom LAMBDA function |
| SCAN | Generate running totals using LAMBDA |
| SEQUENCE | Returns a list of sequential numbers in an array |
| SINGLE | Returns a single value using an implicit intersection |
| SORT | Sorts the contents of a range or array (asc. or desc.) |
| SORTBY | Sorts range or array by column |
| TOCOL | Transforms an array into a single column |
| UNIQUE | Returns a list of unique values in a list or range |
Information
Take a quick overview of information functions in Excel.
| Function | How it works |
|---|---|
| CELL | Return information about a cell |
| ERROR.TYPE | Returns a number that, according to a specific error value |
| INFO | Get information about the current environment (Office, OS, etc.) |
| ISBLANK | Returns with TRUE if a cell is empty |
| ISERR | Returns TRUE for any error type except the #N/A |
| ISERROR | Get TRUE result for any error type in a cell |
| ISEVEN | Test if a value is even |
| ISFORMULA | Returns TRUE if a cell contains a formula |
| ISLOGICAL | Test if a value is logical |
| ISNA | Returns TRUE when a cell has the #N/A error |
| ISNONTEXT | Returns TRUE when a cell contains any value except text |
| ISNUMBER | Get TRUE result when a cell has a number |
| ISODD | Returns TRUE when a value in a cell is an odd number |
| ISREF | Returns TRUE when a cell contains a reference |
| ISTEXT | Test for a text value |
| N | Convert a value to a number |
| NA | Display the #N/A error when information is missing |
| SHEET | Returns the index number of a Worksheet |
| SHEETS | Count the total number of Worksheets in the Workbook. |
| T | Filter text values only in a range |
| TYPE | Returns a numeric code (type of value in a cell) |
Math
Math Functions are great.
| Function | How it works |
|---|---|
| ABS | Converts negative numbers to positive numbers |
| AGGREGATE | Performs an aggregate calculation |
| ARABIC | Converts Roman numbers to Arabic numbers |
| BASE | Convert the number to another base |
| CEILING | Round a number up to the nearest specified multiple (always rounds up) |
| CEILING.MATH | Round a number up to the nearest multiple (control over rounding direction for negative numbers) |
| CEILING.PRECISE | Round a number up to the nearest specified multiple (rounds negative numbers toward zero) |
| COMBIN | Get the number of combinations (repetitions does not allow) |
| COMBINA | Get the number of combinations (repetitions allowed) |
| DECIMAL | Convert a number in a different base to a decimal number (for example, binary to decimal) |
| EVEN | Rounds numbers up to the next even integer |
| EXP | Get the value of e raised to the power of a number |
| FACT | Calculate the factorial of a number |
| FACTDOUBLE | Calculate the double factorial of a number |
| FLOOR | Rounds a number down to the nearest specified multiple (always rounds down) |
| FLOOR.MATH | Rounds a number down to the nearest multiple (support for rounding negative numbers) |
| FLOOR.PRECISE | Round the number down to the nearest multiple (always round negative numbers away from zero) |
| GCD | Calculate the greatest common divisor of numbers |
| INT | Get the integer part of a number (rounds down) |
| LCM | Calculate the least common multiple of selected integers |
| LN | Return the number’s neutral logarithm |
| LOG | Get the logarithm of a number |
| LOG10 | Return the base-10 logarithm of a number |
| MDETERM | Get the determinant of a matrix |
| MINVERSE | Calculate the inverse matrix of the given matrix |
| MMULT | Multiplication matrices |
| MOD | Get the remainder part of the two numbers after the division |
| MROUND | Round a number to the nearest specified multiple |
| MUNIT | Create a matrix based on the given dimension |
Math – Part 2
| Function | How it works |
|---|---|
| ODD | Round a number up to the next odd integer |
| PI | Return the value of π |
| POWER | Raise a number to a given power |
| PRODUCT | Multiply numbers in a given range |
| QUOTIENT | Calculate the integer part of a division |
| RAND | Generate a random number between 0 and 1 |
| RANDBETWEEN | Get a random integer between two values |
| ROMAN | Converts Arabic numbers to Roman numbers |
| ROUND | Round a number to “n” digits |
| ROUNDDOWN | Round down a number to “n” digits |
| ROUNDUP | Round up a number to “n” digits |
| SIGN | Returns the sign of a number (+1, 0, -1) |
| SQRT | Calculate the square root of a positive number |
| SUBTOTAL | Get a subtotal |
| SUM | Return sum of values |
| SUMIF | Sum of cells based on a single criteria |
| SUMIFS | Sum of cells based on multiple criteria |
| SUMPRODUCT | Multiply, then sum arrays, then return the sum of products |
| SUMSQ | Get the sum of the squares |
| SUMX2MY2 | Calculate the sum of squares (based on the difference of values in two arrays) |
| SUMX2PY2 | Get the sum of squares of corresponding values in two arrays |
| SUMXMY2 | Calculate the sum of squares of differences between corresponding values in two arrays |
| TRUNC | Truncate numbers based on “n” digits |
Financial
Use financial functions if you want.
| Function | How it works |
|---|---|
| ACCRINT | Get accrued interest periodic |
| ACCRINTM | Get accrued interest at maturity |
| AMORDEGRC | Calculate the depreciation for a given accounting |
| AMORLINC | Get depreciation for each accounting period |
| COUPDAYBS | Calculate the number of days between the beginning of the coupon period and the settlement date |
| COUPDAYS | Get days in the coupon period (including the settlement date) |
| COUPDAYSNC | Calculate the number of days between the settlement date and the next coupon date |
| COUPNCD | Get the next coupon date after the settlement date |
| COUPNUM | Get the number of coupons payable between the settlement date and the maturity date |
| COUPPCD | Get the previous coupon date before the settlement date (for a coupon bond) |
| CUMIPMT | Calculate the cumulative interest paid on loan between the start and end period |
| CUMPRINC | Get the cumulative principal paid on loan between the start and end period |
| DB | Returns Depreciation – fixed-declining balance |
| DDB | Depreciation – double-declining balance |
| DISC | Returns the discount rate for a security |
| DOLLARDE | Convert fractional notation to a decimal value (for example, 1 1/4 to 1.25) |
| DOLLARFR | Convert a decimal dollar to a decimal value |
| DURATION | Calculate annual duration using periodic interest |
| EFFECT | Return effective annual interest rate |
| FV | Get the future value of an asset |
| FVSCHEDULE | Get the future value of principal compound interest |
| INTRATE | Returns the interest rate for a fully invested security |
| IPMT | Get the interest portion of loan payment in a given payment period |
| IRR | Calculate the internal rate of return |
| ISPMT | Get the interest in a given period where principal payments are equal |
| MDURATION | Return the Macauley modified duration par value of $100 |
| MIRR | Get the modified internal rate of return for a series of cash flows |
Financial – Part 2
| Function | How it works |
|---|---|
| NOMINAL | Get the nominal annual interest rate |
| NPER | Determine loan payment periods |
| NPV | Calculate the net present value of an investment |
| ODDFPRICE | Returns the price per $100 face value of a security with an odd first period |
| ODDFYIELD | Get the yield of a security with an odd first period |
| ODDLPRICE | Returns the price per $100 face value of a security with an odd last period |
| PDURATION | Get the number of periods required for an investment to reach a given value |
| PMT | Get the periodic payment for a loan |
| PPMT | Calculate the principal portion of a given loan payment |
| PRICE | Get the price per $100 face value of a security that pays periodic interest |
| PRICEDISC | Returns the price per $100 face value of a discounted security |
| PRICEMAT | Get Price per $100 interest at maturity |
| PV | Calculate the present value of an investment |
| RATE | Get the interest rate of a loan |
| RECEIVED | Get the amount received at maturity |
| RRI | Get an equivalent interest rate for the growth of an investment |
| SLN | Returns the straight-line depreciation of an asset for one period |
| SYD | Depreciation – sum-of-years |
| TBILLEQ | Get bond-equivalent yield for a Treasury bill |
| TBILLPRICE | Get the price per $100 face value for a Treasury bill |
| TBILLYIELD | Get the yield for a Treasury bill |
| VDB | Calculate depreciation with a variable double-declining method |
| XIRR | Get the internal rate of return for a schedule of cash flows |
| XNPV | Returns the NPV for a schedule of cash flows |
| YIELD | Get the yield on a security that pays periodic interest |
| YIELDDISC | Calculate the annual yield for a discounted security |
| YIELDMAT | Return annual yield of security interest at maturity |
Statistical
| Function | How it works |
|---|---|
| AVEDEV | Calculate variability |
| AVERAGE | Calculate the average of supplied numbers |
| AVERAGEA | Get the average of supplied numbers (evaluate the logical values also) |
| AVERAGEIF | Get the average of numbers in a range that meets the criteria |
| AVERAGEIFS | Get the average of numbers in a range that meets one or more criteria |
| BINOM.DIST | Return binomial distribution probability |
| COUNT | Count numbers in a list or range |
| COUNTA | Count the number in a list or range (ignore blank cells) |
| COUNTBLANK | Count blank cells |
| COUNTIF | Count cells based on a criteria |
| COUNTIFS | Count cells based on one or more criteria |
| DEVSQ | Return sum of squared deviations |
| FORECAST | Predict value based on existing values along with a linear trend |
| FORECAST.ETS | Predict value based on existing values along with a seasonal trend |
| FORECAST.LINEAR | Predict value along with a linear trend (from Excel 2016 FORECAST function was replaced) |
| FREQUENCY | Returns a frequency distribution |
| GEOMEAN | Get geometric mean for a set of numeric values |
| HARMEAN | Get harmonic mean for a set of numeric values |
| INTERCEPT | Returns the point at which a regression line will intersect the y-axis |
| LARGE | Get the nth largest value |
| LINEST | Get parameters of linear trend |
| MAX | Return the largest numeric value (ignores empty cells) |
| MAXA | Return the largest numeric value (ignores empty cells and evaluates logical values) |
| MAXIFS | Calculate the maximum value with one or more criteria |
Statistical – Part 2
| Function | How it works |
|---|---|
| MEDIAN | Get the middle number (median) of a group of numbers |
| MIN | Get the smallest value of a list or range |
| MINA | Get the smallest value (evaluates logical values as 1 and 0) |
| MINIFS | Return minimum value based on one or more criteria |
| MODE | Get the most frequently occurring number in a list of numeric values |
| NORM.DIST | Get values and areas for the normal distribution |
| PERCENTILE | Return the kth percentile for a set of data |
| PERCENTRANK | Get the rank of a value in a data set as a percentage of the data |
| PERMUT | Get the number of permutations (does not allow repetitions) |
| PERMUTATIONA | Get the number of permutations (allow repetitions) |
| PROB | Calculate probability |
| QUARTILE | Returns the quartile for a data set (split the range into 4 parts) |
| RANK | Get the rank of a numeric value (compared to a list of other numeric values) |
| SKEW | Returns the skewness of distribution (a measure of symmetry) |
| SLOPE | Get the slope of the linear regression line |
| SMALL | Get a numeric value based on its position |
| STANDARDIZE | Calculate z-score |
| STDEV | Return the standard deviation in a sample |
| TRIMMEAN | Calculate the average (excluding outliers) |
| VAR | Estimates the variance of a sample of data |
Trigonometry
| Function | How it works |
|---|---|
| ACOS | Get the inverse cosine of a number |
| ASIN | Get the inverse sine of a number |
| ATAN | Return the arctangent of a number |
| ATAN2 | Get the arctangent from x- and y-coordinates |
| COS | Get the cosine of an angle given in radians |
| COSH | Get the hyperbolic cosine of a number |
| COT | Returns the cotangent of an angle |
| CSC | Get the cosecant of an angle |
| DEGREES | Converts radians to degrees |
| RADIANS | Converts degrees to radians |
| SEC | Returns the second of an angle |
| SIN | Get the sine of an angle |
| SINH | Return hyperbolic sine of a number |
| TAN | Return the tangent of an angle |
Engineering
| Function | How it works |
|---|---|
| BIN2DEC | Converts a binary number to the decimal equivalent |
| BIN2HEX | Converts a binary number to a hexadecimal equivalent |
| BIN2OCT | Converts a binary number to an octal equivalent |
| BITAND | Get a ‘Bitwise AND’ of two numeric values |
| BITLSHIFT | Shifts a number by the specified number of bits |
| BITOR | Get a ‘Bitwise OR’ of two numbers |
| BITXOR | Get a ‘Bitwise XOR’ of two numbers |
| COMPLEX | Creates a complex number |
| CONVERT | Converts numbers between the measurement system |
| DEC2BIN | Converts a decimal number to a binary number |
| DEC2HEX | Converts a decimal number to a hexadecimal number |
| DEC2OCT | Converts a decimal number to an octal number |
| DELTA | Test if two numeric values are equal |
| HEX2BIN | Converts a hexadecimal number to a binary number |
| HEX2DEC | Converts a hexadecimal number to a decimal number |
| HEX2OCT | Converts a hexadecimal number to an octal number |
| IMABS | Returns the absolute value of a complex number |
| IMAGINARY | Get the imaginary coefficient of a complex number |
| IMPOWER | Raise a complex number to a given power |
| IMPRODUCT | Return product of complex numbers |
| IMREAL | Calculate the real coefficient of a complex number |
| IMSUB | Calculate the difference between two complex numbers |
| IMSUM | Return the sum of complex numbers |
Database
| Function | How it works |
|---|---|
| DAVERAGE | Return average from matching records |
| DCOUNT | Count records in a database based on criteria (only numeric values) |
| DCOUNTA | Count records in a database based on criteria (counts numeric and text values) |
| DGET | Get a single value from a matching record |
| DMAX | Get the maximum from matching records |
| DMIN | Get the maximum from matching records |
| DPRODUCT | Return the product of values from matching records |
| DSTDEV | Get the standard deviation of the sample in matching records |
| DSUM | Calculate the sum from matching records |
| DVAR | Get sample variance for matching records based on criteria. |
How to use Excel Functions?
To use a function, type an equal sign (=) in the formula bar or the cell to enter a function you want to use. After that, enter the function’s name and the arguments. There are custom functions in Excel, user-defined functions. If you want to learn more about how to solve complex tasks using UDFs, read our guide.