Here is the complete Excel Functions list, including lookup, logical, date and time, text, information, math functions with examples.

**Each function refers to an example.**

## What is an Excel function?

Let’s see the definition of the Excel function.

**Excel Functions are built-in presets, so there are hard-coded formulas** in Excel. With its help, you can avoid manual data entry, and it has small, user-friendly names. 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**.

## 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 | Runs a logical test and returns one value for a TRUE result and another for a FALSE result. |

IFS | Runs multiple tests and return 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 OR operator |

SWITCH | Match multiple values, return the first match |

TRUE | Returns the Boolean value TRUE |

XOR | Performs exclusive OR |

### LOOKUP and Reference

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 |

GETPIVOTDATA | Retrieve data from a pivot table in a formula |

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. |

### 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 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 lower case |

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 UNICODE character from a number |

UNICODE | Get number from a UNICODE character |

UPPER | Convert text to upper case |

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 | 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 |

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 version, OS version, 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 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 number of combinations (repetitions does not allowed) |

COMBINA | Get 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 nearest multiple (support for rounding negative numbers) |

FLOOR.PRECISE | Round number down to nearest multiple (always rounds 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 two numbers after 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 number 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 coupon period (includes the settlement date) |

COUPDAYSNC | Calculate the number of days between the settlement date and the next coupon date |

COUPNCD | Get next coupon date after settlement date |

COUPNUM | Get the number of coupons payable between the settlement date and 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 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 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 evaluate logical values) |

MAXIFS | Calculate 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 number of permutations (does not allow repetitions) |

PERMUTATIONA | Get 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 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 secant 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 hexadecimal equivalent |

BIN2OCT | Converts a binary number to 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 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 standard deviation of sample in matching records |

DSUM | Calculate sum from matching records |

DVAR | Get sample variance for matching records based on criteria. |