Excel ships with more than 450 built-in functions. Most people only ever use 10 or 15. The rest sit there, quietly capable of saving you hours, if you know they exist.
This page lists current Excel functions and the older ones we cover.
Every function is grouped by category, with a one-line description so you can scan, find what you need, and jump to the full tutorial.
New to Excel functions? Start with the short primer below. Already know the basics? Skip to whichever category you need.
What is an Excel function?
A function is a built-in formula that takes one or more inputs and returns a result. You type an equals sign, the function name, and the inputs in parentheses.
=SUM(A1:A10)
=IF(B2>100, "High", "Low")
=VLOOKUP("Apple", A:C, 3, FALSE)
Functions are the core of what makes Excel a spreadsheet rather than a glorified table.
Once you start combining them, you can pull data from another sheet, clean a messy text column, calculate a loan payment, or build a forecast. All without writing a single line of code.
How to use this page
The functions are organized by purpose, the same way Microsoft groups them in the Formulas tab:
- Logical: if-this-then-that decisions
- Lookup & Reference: finding values across tables
- Text: cleaning, splitting, joining, reformatting text
- Date & Time: date math and calendar calculations
- Math & Trig: arithmetic, rounding, trigonometry
- Statistical: averages, counts, ranks, regressions
- Financial: loans, investments, depreciation
- Information: checking what a cell contains
- Database: query-style aggregates over a structured range
- Engineering: number-base conversion, complex numbers, bitwise math
- Web: pulling data from the web
- Cube: pulling values from a data model
- Compatibility: older functions kept for existing workbooks
Each section starts with a short note on when you would reach for that family. After that, a table with the function name and a one-line description.
—
Logical Functions
Use these when your formula needs to make a decision. The most common pattern is “if this condition is true, do A, otherwise do B”. Almost everything more advanced is a variation on that idea.
| Function | Description |
|---|---|
| AND | Returns TRUE only if every argument is TRUE |
| BYCOL | Applies a LAMBDA to each column of an array |
| BYROW | Applies a LAMBDA to each row of an array |
| FALSE | Returns the logical value FALSE |
| IF | Returns one value if a condition is TRUE and another if it is FALSE |
| IFERROR | Returns a custom result if a formula errors, otherwise returns the formula’s result |
| IFNA | Returns a custom result if a formula returns #N/A, otherwise returns the formula’s result |
| IFS | Tests multiple conditions in order and returns the value for the first one that is TRUE |
| LAMBDA | Creates a custom, reusable function from a formula |
| LET | Assigns names to calculation results so a formula can reuse them |
| MAKEARRAY | Builds an array of a given size from a LAMBDA |
| MAP | Applies a LAMBDA to each value of one or more arrays |
| NOT | Reverses the logic of its argument (TRUE becomes FALSE, FALSE becomes TRUE) |
| OR | Returns TRUE if at least one argument is TRUE |
| REDUCE | Reduces an array to a single value with a LAMBDA |
| SCAN | Returns the running totals of applying a LAMBDA to an array |
| SWITCH | Evaluates an expression against a list of values and returns the matching result |
| TRUE | Returns the logical value TRUE |
| XOR | Returns TRUE if an odd number of its arguments are TRUE |
—
Lookup & Reference Functions
Use these when the value you need is in another row, another sheet, or another file, and you want Excel to fetch it for you. VLOOKUP is the famous one, but XLOOKUP has largely replaced it for new work.
| Function | Description |
|---|---|
| ADDRESS | Builds a cell reference string from row and column numbers |
| AREAS | Returns the number of areas in a reference |
| CHOOSE | Returns one of a list of values based on an index number |
| CHOOSECOLS | Returns the specified columns from an array |
| CHOOSEROWS | Returns the specified rows from an array |
| COLUMN | Returns the column number of a reference |
| COLUMNS | Returns the number of columns in a reference |
| DROP | Drops a specified number of rows or columns from an array |
| EXPAND | Pads an array with a given value to a target size |
| FILTER | Returns the rows of a range that meet a condition |
| FORMULATEXT | Returns the formula in a referenced cell as text |
| GETPIVOTDATA | Pulls a specific value from a pivot table |
| GROUPBY | Groups rows of data and aggregates each group |
| HLOOKUP | Like VLOOKUP but searches across the first row of a range instead of the first column |
| HSTACK | Stacks arrays horizontally |
| HYPERLINK | Creates a clickable link in a cell |
| IMAGE | Displays an image from an HTTPS URL inside a cell |
| INDEX | Returns the value at a given row and column position inside a range |
| INDIRECT | Converts a text string into a real cell reference |
| LOOKUP | Older, simpler lookup function that searches a single row or column |
| MATCH | Returns the position of a value within a row or column |
| OFFSET | Returns a reference shifted by a given number of rows and columns from a starting cell |
| PIVOTBY | Builds a pivot-table-style result inside a formula |
| ROW | Returns the row number of a reference |
| ROWS | Returns the number of rows in a reference |
| RTD | Retrieves real-time data from a server program |
| SORT | Sorts a range by one or more columns |
| SORTBY | Sorts a range by the values in another range |
| TAKE | Returns a specified number of rows or columns from the start or end of an array |
| TOCOL | Reshapes an array into a single column |
| TOROW | Reshapes an array into a single row |
| TRANSPOSE | Flips a vertical range to horizontal or vice versa |
| TRIMRANGE | Removes blank outer rows and columns from a range or array |
| UNIQUE | Returns the unique values from a range |
| VLOOKUP | Looks up a value in the first column of a range and returns a value from another column in the same row |
| VSTACK | Stacks arrays vertically |
| WRAPCOLS | Wraps a 1D array into columns of a given length |
| WRAPROWS | Wraps a 1D array into rows of a given length |
| XLOOKUP | Modern replacement for VLOOKUP and HLOOKUP. Searches in any direction and handles errors built-in |
| XMATCH | Modern replacement for MATCH with wildcard and reverse-search support |
—
Text Functions
Use these when you need to clean, parse, combine, or reformat text. Almost every “the data is messy and I need to fix it” problem ends here.
| Function | Description |
|---|---|
| ARRAYTOTEXT | Returns an array of values as a single text string |
| ASC | Converts full-width characters to half-width |
| BAHTTEXT | Converts a number to Thai text with a baht suffix |
| CHAR | Returns the character that matches a given ASCII number |
| CLEAN | Removes non-printable characters from text |
| CODE | Returns the ASCII number of the first character in a string |
| CONCAT | Joins multiple text strings or ranges into one string |
| CONCATENATE | Older version of CONCAT, kept for backward compatibility |
| DBCS | Converts half-width English letters to full-width |
| DETECTLANGUAGE | Identifies the language used in a text value |
| DOLLAR | Formats a number as currency and returns it as text |
| EXACT | Returns TRUE if two text strings are identical (case-sensitive) |
| FIND | Returns the position of one text string inside another (case-sensitive) |
| FINDB | Finds case-sensitive text by byte position in double-byte languages |
| FIXED | Formats a number with fixed decimals and returns it as text |
| JIS | Converts half-width characters to full-width |
| LEFT | Returns the leftmost N characters of a text string |
| LEFTB | Returns characters from the left, counting bytes for double-byte languages |
| LEN | Returns the number of characters in a text string |
| LENB | Counts bytes in text when working with double-byte languages |
| LOWER | Converts text to lowercase |
| MID | Returns N characters from the middle of a text string starting at a given position |
| MIDB | Returns text from a byte position in double-byte languages |
| NUMBERVALUE | Converts text to a number with locale-aware decimal and group separators |
| PHONETIC | Extracts the phonetic characters from a Japanese text string |
| PROPER | Capitalizes the first letter of each word |
| REGEXEXTRACT | Extracts text that matches a regular expression pattern |
| REGEXREPLACE | Replaces text that matches a regular expression pattern |
| REGEXTEST | Returns TRUE if text matches a regular expression pattern |
| REPLACE | Replaces part of a text string with new text, by position |
| REPLACEB | Replaces text by byte position when using double-byte languages |
| REPT | Repeats a text string a given number of times |
| RIGHT | Returns the rightmost N characters of a text string |
| RIGHTB | Returns characters from the right, measured by bytes in double-byte languages |
| SEARCH | Returns the position of one text string inside another (not case-sensitive, supports wildcards) |
| SEARCHB | Finds text without case sensitivity using byte positions for double-byte languages |
| SUBSTITUTE | Replaces specific text inside a string with new text |
| T | Returns the text value of an argument, or empty if it is not text |
| TEXT | Converts a number to text using a chosen format string |
| TEXTAFTER | Returns the text after a given delimiter |
| TEXTBEFORE | Returns the text before a given delimiter |
| TEXTJOIN | Joins text values with a chosen delimiter, with the option to skip empty cells |
| TEXTSPLIT | Splits text into rows or columns by a delimiter |
| TRANSLATE | Converts text from one language to another using language codes |
| TRIM | Removes extra spaces from text, leaving single spaces between words |
| UNICHAR | Returns the character that matches a given Unicode number |
| UNICODE | Returns the Unicode number of the first character in a string |
| UPPER | Converts text to uppercase |
| VALUE | Converts a text string that looks like a number into a real number |
| VALUETOTEXT | Returns the text representation of any value |
—
Date & Time Functions
Use these for any calendar math: aging, deadlines, business-day calculations, age from a birthdate, date differences, day-of-week logic.
| Function | Description |
|---|---|
| DATE | Builds a date from a year, month, and day |
| DATEDIF | Returns the difference between two dates in years, months, or days |
| DATEVALUE | Converts a date stored as text into a real date serial number |
| DAY | Returns the day of the month from a date |
| DAYS | Returns the number of days between two dates |
| DAYS360 | Returns the number of days between two dates assuming a 360-day year |
| EDATE | Returns the date N months before or after a given date |
| EOMONTH | Returns the last day of the month, N months before or after a date |
| HOUR | Returns the hour from a time value |
| ISOWEEKNUM | Returns the ISO week number of a date |
| MINUTE | Returns the minute from a time value |
| MONTH | Returns the month from a date |
| NETWORKDAYS | Returns the number of working days between two dates |
| NETWORKDAYS.INTL | Like NETWORKDAYS but lets you customize which days count as weekends |
| NOW | Returns the current date and time |
| SECOND | Returns the second from a time value |
| TIME | Builds a time from an hour, minute, and second |
| TIMEVALUE | Converts a time stored as text into a real time serial number |
| TODAY | Returns the current date |
| WEEKDAY | Returns the day of the week as a number from 1 to 7 |
| WEEKNUM | Returns the week number of a date in the year |
| WORKDAY | Returns the date N working days before or after a given date |
| WORKDAY.INTL | Like WORKDAY with custom weekend definitions |
| YEAR | Returns the year from a date |
| YEARFRAC | Returns the fraction of a year between two dates |
—
Math & Trig Functions
Use these for arithmetic, rounding, randomization, and trigonometry. SUM is the famous one, but there are dozens of rounding and randomization helpers most people never discover.
| Function | Description |
|---|---|
| ABS | Returns the absolute value of a number |
| ACOS | Returns the arccosine of a number |
| ACOSH | Returns the inverse hyperbolic cosine of a number |
| ACOT | Returns the inverse cotangent of a number |
| ACOTH | Returns the inverse hyperbolic cotangent of a number |
| AGGREGATE | Like SUBTOTAL but with more functions and the ability to ignore errors |
| ARABIC | Converts a Roman numeral string to an Arabic number |
| ASIN | Returns the arcsine of a number |
| ASINH | Returns the inverse hyperbolic sine of a number |
| ATAN | Returns the arctangent of a number |
| ATAN2 | Returns the arctangent from x and y coordinates |
| ATANH | Returns the inverse hyperbolic tangent of a number |
| BASE | Converts a number to text in a given numeric base |
| CEILING | Rounds a number up to the nearest multiple |
| CEILING.MATH | Rounds up with control over how negatives are handled |
| CEILING.PRECISE | Rounds up to a multiple, even when the number is negative |
| COMBIN | Returns the number of combinations of a given size |
| COMBINA | Returns combinations with repetitions allowed |
| COS | Returns the cosine of an angle |
| COSH | Returns the hyperbolic cosine of a number |
| COT | Returns the cotangent of an angle in radians |
| COTH | Returns the hyperbolic cotangent of a number |
| CSC | Returns the cosecant of an angle in radians |
| CSCH | Returns the hyperbolic cosecant of a number |
| DECIMAL | Converts text in a given base to a decimal number |
| DEGREES | Converts radians to degrees |
| EVEN | Rounds a number up to the nearest even integer |
| EXP | Returns e raised to a given power |
| FACT | Returns the factorial of a number |
| FACTDOUBLE | Returns the double factorial of a number |
| FLOOR | Rounds a number down to the nearest multiple |
| FLOOR.MATH | Rounds down with control over how negatives are handled |
| FLOOR.PRECISE | Rounds down to a multiple, even when the number is negative |
| GCD | Returns the greatest common divisor of a set of numbers |
| INT | Rounds a number down to the nearest integer |
| ISO.CEILING | Matches CEILING.PRECISE for compatibility with the ISO standard |
| LCM | Returns the least common multiple of a set of numbers |
| LN | Returns the natural logarithm of a number |
| LOG | Returns the logarithm of a number to a given base |
| LOG10 | Returns the base-10 logarithm of a number |
| MDETERM | Returns the matrix determinant of an array |
| MINVERSE | Returns the matrix inverse of an array |
| MMULT | Returns the matrix product of two arrays |
| MOD | Returns the remainder after a division |
| MROUND | Rounds a number to the nearest multiple of another number |
| MULTINOMIAL | Returns the multinomial value for a group of numbers |
| MUNIT | Returns an identity matrix of a given size |
| ODD | Rounds a number up to the nearest odd integer |
| PERCENTOF | Returns each value as a percent of the total |
| PI | Returns the value of pi |
| POWER | Raises a number to a given power |
| PRODUCT | Multiplies its arguments together |
| QUOTIENT | Returns the integer portion of a division |
| RADIANS | Converts degrees to radians |
| RAND | Returns a random number between 0 and 1 |
| RANDARRAY | Returns an array of random numbers |
| RANDBETWEEN | Returns a random integer between two bounds |
| ROMAN | Converts an Arabic numeral to Roman numerals as text |
| ROUND | Rounds a number to a given number of decimal places |
| ROUNDDOWN | Rounds a number down, toward zero |
| ROUNDUP | Rounds a number up, away from zero |
| SEC | Returns the secant of an angle in radians |
| SECH | Returns the hyperbolic secant of a number |
| SEQUENCE | Returns an array of sequential numbers |
| SERIESSUM | Returns the sum of a power series |
| SIGN | Returns 1, 0, or -1 depending on the sign of the number |
| SIN | Returns the sine of an angle |
| SINH | Returns the hyperbolic sine of a number |
| SQRT | Returns the square root of a number |
| SQRTPI | Returns the square root of a number multiplied by pi |
| SUBTOTAL | Returns a subtotal of a range, ignoring other subtotals |
| SUM | Adds up numbers in a range |
| SUMIF | Adds numbers that meet a single condition |
| SUMIFS | Adds numbers that meet multiple conditions |
| SUMPRODUCT | Multiplies arrays element by element and returns the sum of the products |
| SUMSQ | Returns the sum of the squares of its arguments |
| SUMX2MY2 | Returns the sum of the difference of squares of two arrays |
| SUMX2PY2 | Returns the sum of the sum of squares of two arrays |
| SUMXMY2 | Returns the sum of squares of differences of two arrays |
| TAN | Returns the tangent of an angle |
| TANH | Returns the hyperbolic tangent of a number |
| TRUNC | Removes the decimal portion of a number |
—
Statistical Functions
Use these whenever you need to summarize a column of numbers. Averages, counts, ranks, percentiles, regressions, distributions. The IF and IFS variants like COUNTIF and AVERAGEIFS are the everyday workhorses.
| Function | Description |
|---|---|
| AVEDEV | Returns the average of absolute deviations from the mean |
| AVERAGE | Returns the arithmetic mean of a range |
| AVERAGEA | Like AVERAGE but counts text and logical values as zero |
| AVERAGEIF | Returns the average of cells that meet a single condition |
| AVERAGEIFS | Returns the average of cells that meet multiple conditions |
| BETA.DIST | Returns the beta distribution probability |
| BETA.INV | Returns the beta distribution value for a given cumulative probability |
| BINOM.DIST | Returns the binomial distribution probability |
| BINOM.DIST.RANGE | Returns the probability of successes falling within a specified range |
| BINOM.INV | Returns the fewest successes whose cumulative binomial probability meets a threshold |
| CHISQ.DIST | Returns the chi-square distribution |
| CHISQ.DIST.RT | Returns the right-tailed probability for a chi-square distribution |
| CHISQ.INV | Returns the inverse of the chi-square distribution |
| CHISQ.INV.RT | Returns the chi-square value for a given right-tailed probability |
| CHISQ.TEST | Returns the probability from a chi-square test for independence |
| CONFIDENCE.NORM | Returns the confidence interval for a normal distribution |
| CONFIDENCE.T | Returns a confidence interval margin using Student’s t distribution |
| CORREL | Returns the correlation coefficient between two ranges |
| COUNT | Counts the cells in a range that contain numbers |
| COUNTA | Counts non-empty cells in a range |
| COUNTBLANK | Counts empty cells in a range |
| COUNTIF | Counts cells that meet a single condition |
| COUNTIFS | Counts cells that meet multiple conditions |
| COVARIANCE.P | Returns the population covariance of two ranges |
| COVARIANCE.S | Returns the sample covariance of two ranges |
| DEVSQ | Returns the sum of squared deviations from the mean |
| EXPON.DIST | Returns the exponential distribution probability |
| F.DIST | Returns the F probability distribution |
| F.DIST.RT | Returns the right-tailed probability for an F distribution |
| F.INV | Returns the inverse of the F distribution |
| F.INV.RT | Returns the F value for a given right-tailed probability |
| F.TEST | Returns the probability that two data sets have equal variances |
| FISHER | Returns the Fisher transformation of a correlation value |
| FISHERINV | Returns the inverse Fisher transformation of a value |
| FORECAST | Legacy function that predicts a value using linear regression; replaced by FORECAST.LINEAR |
| FORECAST.ETS | Predicts a future value using exponential smoothing |
| FORECAST.ETS.CONFINT | Returns the confidence interval for an ETS forecast value |
| FORECAST.ETS.SEASONALITY | Returns the repeating pattern length detected in a time series |
| FORECAST.ETS.STAT | Returns a requested statistic from an ETS time-series forecast |
| FORECAST.LINEAR | Predicts a future value using linear regression |
| FREQUENCY | Returns a frequency distribution as an array |
| GAMMA | Returns the gamma function value for a number |
| GAMMA.DIST | Returns the gamma distribution probability |
| GAMMA.INV | Returns the gamma distribution value for a given cumulative probability |
| GAMMALN | Returns the natural logarithm of a number’s gamma function |
| GAMMALN.PRECISE | Returns a more precise natural logarithm of a number’s gamma function |
| GAUSS | Returns the standard normal cumulative probability minus 0.5 |
| GEOMEAN | Returns the geometric mean of a range |
| GROWTH | Returns predicted values along an exponential trend |
| HARMEAN | Returns the harmonic mean of a range |
| HYPGEOM.DIST | Returns the hypergeometric distribution probability |
| INTERCEPT | Returns the y-intercept of a linear regression line |
| KURT | Returns the kurtosis of a distribution |
| LARGE | Returns the Nth largest value in a range |
| LINEST | Returns the parameters of a linear best-fit line |
| LOGEST | Returns the parameters of an exponential best-fit curve |
| LOGNORM.DIST | Returns the cumulative or density probability for a lognormal distribution |
| LOGNORM.INV | Returns the lognormal value for a given cumulative probability |
| MAX | Returns the largest number in a range |
| MAXA | Like MAX but counts text and logical values |
| MAXIFS | Returns the maximum of cells that meet multiple conditions |
| MEDIAN | Returns the middle value in a range |
| MIN | Returns the smallest number in a range |
| MINA | Like MIN but counts text and logical values |
| MINIFS | Returns the minimum of cells that meet multiple conditions |
| MODE.MULT | Returns an array of the most common values in a range |
| MODE.SNGL | Returns the single most common value in a range |
| NEGBINOM.DIST | Returns the probability of failures before a specified number of successes |
| NORM.DIST | Returns the normal distribution probability |
| NORM.INV | Returns the inverse of the normal distribution |
| NORM.S.DIST | Returns the standard normal distribution |
| NORM.S.INV | Returns the inverse of the standard normal distribution |
| PEARSON | Returns the Pearson correlation coefficient |
| PERCENTILE.EXC | Returns the Nth percentile of a range, exclusive |
| PERCENTILE.INC | Returns the Nth percentile of a range, inclusive |
| PERCENTRANK.EXC | Returns the percentile rank of a value, exclusive |
| PERCENTRANK.INC | Returns the percentile rank of a value, inclusive |
| PERMUT | Returns the number of permutations of a given size |
| PERMUTATIONA | Returns permutations with repetitions allowed |
| PHI | Returns the density value for the standard normal distribution |
| POISSON.DIST | Returns the Poisson distribution probability |
| PROB | Returns the probability that values fall within a given range |
| QUARTILE.EXC | Returns the quartile of a range, exclusive |
| QUARTILE.INC | Returns the quartile of a range, inclusive |
| RANK.AVG | Returns the average rank of a number when there are ties |
| RANK.EQ | Returns the rank of a number in a list, with ties getting the same rank |
| RSQ | Returns the squared Pearson correlation coefficient |
| SKEW | Returns the skewness of a distribution |
| SKEW.P | Returns the skewness of a population |
| SLOPE | Returns the slope of a linear regression line |
| SMALL | Returns the Nth smallest value in a range |
| STANDARDIZE | Returns a normalized z-score |
| STDEV.P | Returns the population standard deviation |
| STDEV.S | Returns the sample standard deviation |
| STDEVA | Like STDEV.S but counts text and logical values |
| STDEVPA | Like STDEV.P but counts text and logical values |
| STEYX | Returns the standard error of predicted y-values in a regression |
| T.DIST | Returns the Student t distribution |
| T.DIST.2T | Returns the two-tailed probability for Student’s t distribution |
| T.DIST.RT | Returns the right-tailed probability for Student’s t distribution |
| T.INV | Returns the inverse of the Student t distribution |
| T.INV.2T | Returns the t value for a given two-tailed probability |
| T.TEST | Returns the probability associated with a Student’s t-Test |
| TREND | Returns predicted values along a linear trend |
| TRIMMEAN | Returns the mean of a range with the top and bottom percentages excluded |
| VAR.P | Returns the population variance |
| VAR.S | Returns the sample variance |
| VARA | Like VAR.S but counts text and logical values |
| VARPA | Like VAR.P but counts text and logical values |
| WEIBULL.DIST | Returns the cumulative or density probability for a Weibull distribution |
| Z.TEST | Returns the one-tailed probability from a z-test |
—
Financial Functions
Use these for loans, savings, investments, depreciation, and bond pricing. PMT is the most-used; the rest cover the long tail of finance work.
| Function | Description |
|---|---|
| ACCRINT | Returns the accrued interest on a security that pays periodic interest |
| ACCRINTM | Returns the accrued interest on a security that pays at maturity |
| AMORDEGRC | Returns accounting-period depreciation using a coefficient based on asset life |
| AMORLINC | Returns straight-line depreciation for each accounting period |
| COUPDAYBS | Returns the days from the start of the coupon period to the settlement date |
| COUPDAYS | Returns the days in the coupon period containing the settlement date |
| COUPDAYSNC | Returns the days from settlement to the next coupon date |
| COUPNCD | Returns the next coupon date after settlement |
| COUPNUM | Returns the number of coupons payable between settlement and maturity |
| COUPPCD | Returns the previous coupon date before settlement |
| CUMIPMT | Returns the cumulative interest paid between two periods |
| CUMPRINC | Returns the cumulative principal paid between two periods |
| DB | Returns the depreciation of an asset using the fixed-declining-balance method |
| DDB | Returns the depreciation using the double-declining-balance method |
| DISC | Returns the discount rate for a security |
| DOLLARDE | Converts a dollar price expressed as a fraction to a decimal |
| DOLLARFR | Converts a dollar price expressed as a decimal to a fraction |
| DURATION | Returns the Macaulay duration of a security |
| EFFECT | Returns the effective annual interest rate |
| FV | Returns the future value of an investment |
| FVSCHEDULE | Returns the future value with a series of variable interest rates |
| INTRATE | Returns the interest rate for a fully invested security |
| IPMT | Returns the interest portion of a loan payment for a given period |
| IRR | Returns the internal rate of return for a series of cash flows |
| ISPMT | Returns interest paid during a specific investment period |
| MDURATION | Returns the modified Macaulay duration of a security |
| MIRR | Returns the modified internal rate of return |
| NOMINAL | Returns the nominal annual interest rate |
| NPER | Returns the number of periods in a loan or annuity |
| NPV | Returns the net present value of a series of cash flows |
| ODDFPRICE | Returns the price per $100 face value of a security with an odd first period |
| ODDFYIELD | Returns 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 |
| ODDLYIELD | Returns the yield of a security with an odd last period |
| PDURATION | Returns the number of periods required for an investment to reach a target value |
| PMT | Returns the periodic payment for a loan or annuity |
| PPMT | Returns the principal portion of a loan payment for a given period |
| PRICE | Returns the price per $100 face value of a security with periodic interest |
| PRICEDISC | Returns the price per $100 face value of a discounted security |
| PRICEMAT | Returns the price per $100 face value of a security that pays interest at maturity |
| PV | Returns the present value of an investment |
| RATE | Returns the interest rate per period of a loan or annuity |
| RECEIVED | Returns the amount received at maturity for a fully invested security |
| RRI | Returns the equivalent interest rate for the growth of an investment |
| SLN | Returns the straight-line depreciation of an asset |
| SYD | Returns the sum-of-years depreciation of an asset |
| TBILLEQ | Returns the bond-equivalent yield for a Treasury bill |
| TBILLPRICE | Returns the price per $100 face value for a Treasury bill |
| TBILLYIELD | Returns the yield for a Treasury bill |
| VDB | Returns the depreciation using a variable declining-balance method |
| XIRR | Returns the IRR for cash flows on irregular dates |
| XNPV | Returns the net present value for cash flows on irregular dates |
| YIELD | Returns the yield on a security that pays periodic interest |
| YIELDDISC | Returns the annual yield for a discounted security |
| YIELDMAT | Returns the annual yield of a security that pays interest at maturity |
—
Information Functions
Use these to check what is in a cell before doing something with it. The IS-prefixed family is the bread and butter. Almost always paired with IF.
| Function | Description |
|---|---|
| CELL | Returns information about a cell’s formatting, location, or contents |
| ERROR.TYPE | Returns a number indicating which error a value is |
| INFO | Returns information about the current operating environment |
| ISBLANK | Returns TRUE if the cell is empty |
| ISERR | Returns TRUE if the value is any error except #N/A |
| ISERROR | Returns TRUE if the value is any error |
| ISEVEN | Returns TRUE if the number is even |
| ISFORMULA | Returns TRUE if the cell contains a formula |
| ISLOGICAL | Returns TRUE if the value is a logical value |
| ISNA | Returns TRUE if the value is the #N/A error |
| ISNONTEXT | Returns TRUE if the value is not text |
| ISNUMBER | Returns TRUE if the value is a number |
| ISODD | Returns TRUE if the number is odd |
| ISOMITTED | In LAMBDA, returns TRUE if the argument is missing |
| ISREF | Returns TRUE if the value is a reference |
| ISTEXT | Returns TRUE if the value is text |
| N | Converts a value to a number |
| NA | Returns the #N/A error |
| SHEET | Returns the sheet number of a referenced sheet |
| SHEETS | Returns the number of sheets in a reference |
| STOCKHISTORY | Returns historical price data for a financial instrument over a date range |
| TYPE | Returns a number indicating the data type of a value |
—
Database Functions
Use these when you have a table with field headers and need to run a quick query against it. They are not commonly used today. Pivot tables and FILTER have largely replaced them, but they remain in Excel for backward compatibility.
| Function | Description |
|---|---|
| DAVERAGE | Averages values in a database column that match given criteria |
| DCOUNT | Counts numeric values in a database column that match given criteria |
| DCOUNTA | Counts non-empty cells in a database column that match given criteria |
| DGET | Returns a single value from a database column that matches the criteria |
| DMAX | Returns the maximum value in a database column that matches the criteria |
| DMIN | Returns the minimum value in a database column that matches the criteria |
| DPRODUCT | Multiplies values in a database column that match the criteria |
| DSTDEV | Returns the sample standard deviation of matching values |
| DSTDEVP | Returns the population standard deviation of matching values |
| DSUM | Sums values in a database column that match given criteria |
| DVAR | Returns the sample variance of matching values |
| DVARP | Returns the population variance of matching values |
—
Engineering Functions
Use these for number-base conversion, complex numbers, bitwise math, and unit conversion. Most spreadsheets never need them, but if you work in engineering or hardware, they save real time.
| Function | Description |
|---|---|
| BESSELI | Returns the modified Bessel function I for a number and order |
| BESSELJ | Returns the Bessel function J for a number and order |
| BESSELK | Returns the modified Bessel function K for a number and order |
| BESSELY | Returns the Bessel function Y for a number and order |
| BIN2DEC | Converts a binary number to decimal |
| BIN2HEX | Converts a binary number to hexadecimal |
| BIN2OCT | Converts a binary number to octal |
| BITAND | Bitwise AND of two numbers |
| BITLSHIFT | Shifts a number left by a given number of bits |
| BITOR | Bitwise OR of two numbers |
| BITRSHIFT | Shifts a number right by a given number of bits |
| BITXOR | Bitwise exclusive OR of two numbers |
| COMPLEX | Builds a complex number from real and imaginary parts |
| CONVERT | Converts a number from one unit of measure to another |
| DEC2BIN | Converts a decimal number to binary |
| DEC2HEX | Converts a decimal number to hexadecimal |
| DEC2OCT | Converts a decimal number to octal |
| DELTA | Returns 1 if two numbers are equal, 0 otherwise |
| ERF | Returns the error function |
| ERF.PRECISE | Returns the error function (more precise) |
| ERFC | Returns the complementary error function |
| ERFC.PRECISE | Returns the complementary error function (more precise) |
| GESTEP | Returns 1 if a number is greater than or equal to a step, 0 otherwise |
| HEX2BIN | Converts a hexadecimal number to binary |
| HEX2DEC | Converts a hexadecimal number to decimal |
| HEX2OCT | Converts a hexadecimal number to octal |
| IMABS | Returns the absolute value of a complex number |
| IMAGINARY | Returns the imaginary part of a complex number |
| IMARGUMENT | Returns the angle of a complex number in radians |
| IMCONJUGATE | Returns the complex conjugate of a complex number |
| IMCOS | Returns the cosine of a complex number |
| IMCOSH | Returns the hyperbolic cosine of a complex number |
| IMCOT | Returns the cotangent of a complex number |
| IMCSC | Returns the cosecant of a complex number |
| IMCSCH | Returns the hyperbolic cosecant of a complex number |
| IMDIV | Divides complex numbers |
| IMEXP | Returns the exponential of a complex number |
| IMLN | Returns the natural log of a complex number |
| IMLOG10 | Returns the base-10 log of a complex number |
| IMLOG2 | Returns the base-2 log of a complex number |
| IMPOWER | Raises a complex number to a power |
| IMPRODUCT | Multiplies complex numbers |
| IMREAL | Returns the real part of a complex number |
| IMSEC | Returns the secant of a complex number |
| IMSECH | Returns the hyperbolic secant of a complex number |
| IMSIN | Returns the sine of a complex number |
| IMSINH | Returns the hyperbolic sine of a complex number |
| IMSQRT | Returns the square root of a complex number |
| IMSUB | Subtracts complex numbers |
| IMSUM | Sums complex numbers |
| IMTAN | Returns the tangent of a complex number |
| OCT2BIN | Converts an octal number to binary |
| OCT2DEC | Converts an octal number to decimal |
| OCT2HEX | Converts an octal number to hexadecimal |
—
Web Functions
Use these to pull live data from the internet directly into a worksheet. WEBSERVICE is the most flexible; FILTERXML pairs with it to parse the response.
| Function | Description |
|---|---|
| ENCODEURL | Returns a URL-encoded string |
| FILTERXML | Returns specific data from XML content using XPath |
| WEBSERVICE | Returns data from a web service |
—
Cube Functions
Use these to retrieve values and details from Analysis Services or Power Pivot. You only need them when your workbook connects to one of those sources.
| Function | Description |
|---|---|
| CUBEKPIMEMBER | Returns a KPI property and displays its name from a data model |
| CUBEMEMBER | Returns a member or tuple from a data model |
| CUBEMEMBERPROPERTY | Returns a specified property for a data model member |
| CUBERANKEDMEMBER | Returns the member at a specified rank within a data model set |
| CUBESET | Creates a set of members or tuples from a data model |
| CUBESETCOUNT | Counts the items in a data model set |
| CUBEVALUE | Returns an aggregated value from a data model |
—
Compatibility Functions
These older functions still work, but Microsoft has replaced them with newer versions. You will find the replacements in their regular categories above. Other replaced functions are left off this page because we do not have tutorials for them.
| Function | Description |
|---|---|
| PERCENTILE | Legacy function that returns a percentile value; replaced by PERCENTILE.INC |
| RANK | Legacy function that returns a number’s rank in a list; replaced by RANK.EQ |
| STDEV | Legacy function that returns the sample standard deviation; replaced by STDEV.S |
| VAR | Legacy function that returns the sample variance; replaced by VAR.S |
—
Frequently asked questions
How many functions does Excel have? There are over 450 built-in functions in Excel, depending on which version you have. Excel 365 has the most because Microsoft keeps adding dynamic-array and lambda-related functions. Older versions like Excel 2016 and 2019 are missing the dynamic-array family.
Which Excel functions should I learn first? Start with SUM, AVERAGE, COUNT, IF, VLOOKUP (or XLOOKUP), CONCATENATE (or CONCAT), LEFT, RIGHT, MID, and TODAY. These ten cover most everyday spreadsheet work. After that, learn SUMIF, COUNTIF, INDEX/MATCH, and the IS-family of information functions.
What is the difference between a function and a formula in Excel? A formula is anything that starts with an equals sign and produces a result. A function is a built-in operation Excel provides, like SUM or VLOOKUP. So =A1+B1 is a formula but not a function. =SUM(A1:B1) is a formula that uses a function.
Are some Excel functions only available in newer versions? Yes. Dynamic-array functions like FILTER, SORT, UNIQUE, SEQUENCE, and the LAMBDA-related family are only available in Excel 365 and Excel 2021. If you open a workbook that uses these in older Excel, you will see the result locked in place but you cannot edit the formula.
What is the most useful Excel function? For most people, IF or VLOOKUP. IF lets you build conditional logic, and VLOOKUP (or its modern replacement XLOOKUP) lets you pull data from other tables. Together they cover an enormous percentage of practical spreadsheet work.
How do I know which function to use? Open the Formulas tab in the ribbon and browse by category. Each category lists the functions and Excel shows a tooltip describing what each one does. The list on this page is grouped the same way, so once you find the category, you can scan for the function you need.
