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 all of them.
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
- Dynamic Array: modern functions that spill into multiple cells
- Engineering: number-base conversion, complex numbers, bitwise math
- Web: pulling data from the web
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 |
|---|---|
| IF | Returns one value if a condition is TRUE and another if it is FALSE |
| IFS | Tests multiple conditions in order and returns the value for the first one that is TRUE |
| 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 |
| AND | Returns TRUE only if every argument is TRUE |
| OR | Returns TRUE if at least one argument is TRUE |
| NOT | Reverses the logic of its argument (TRUE becomes FALSE, FALSE becomes TRUE) |
| XOR | Returns TRUE if an odd number of its arguments are TRUE |
| SWITCH | Evaluates an expression against a list of values and returns the matching result |
| TRUE | Returns the logical value TRUE |
| FALSE | Returns the logical value FALSE |
| LET | Assigns names to calculation results so a formula can reuse them |
| LAMBDA | Creates a custom, reusable function from a formula |
—
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 |
|---|---|
| VLOOKUP | Looks up a value in the first column of a range and returns a value from another column in the same row |
| HLOOKUP | Like VLOOKUP but searches across the first row of a range instead of the first column |
| XLOOKUP | Modern replacement for VLOOKUP and HLOOKUP. Searches in any direction and handles errors built-in |
| LOOKUP | Older, simpler lookup function that searches a single row or column |
| INDEX | Returns the value at a given row and column position inside a range |
| MATCH | Returns the position of a value within a row or column |
| XMATCH | Modern replacement for MATCH with wildcard and reverse-search support |
| OFFSET | Returns a reference shifted by a given number of rows and columns from a starting cell |
| INDIRECT | Converts a text string into a real cell reference |
| CHOOSE | Returns one of a list of values based on an index number |
| CHOOSEROWS | Returns the specified rows from an array |
| CHOOSECOLS | Returns the specified columns from an array |
| ADDRESS | Builds a cell reference string from row and column numbers |
| ROW | Returns the row number of a reference |
| ROWS | Returns the number of rows in a reference |
| COLUMN | Returns the column number of a reference |
| COLUMNS | Returns the number of columns in a reference |
| AREAS | Returns the number of areas in a reference |
| FORMULATEXT | Returns the formula in a referenced cell as text |
| HYPERLINK | Creates a clickable link in a cell |
| TRANSPOSE | Flips a vertical range to horizontal or vice versa |
| GETPIVOTDATA | Pulls a specific value from a pivot table |
| RTD | Retrieves real-time data from a server program |
—
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 |
|---|---|
| CONCAT | Joins multiple text strings or ranges into one string |
| CONCATENATE | Older version of CONCAT, kept for backward compatibility |
| 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 |
| TEXTBEFORE | Returns the text before a given delimiter |
| TEXTAFTER | Returns the text after a given delimiter |
| LEFT | Returns the leftmost N characters of a text string |
| RIGHT | Returns the rightmost N characters of a text string |
| MID | Returns N characters from the middle of a text string starting at a given position |
| LEN | Returns the number of characters in a text string |
| FIND | Returns the position of one text string inside another (case-sensitive) |
| SEARCH | Returns the position of one text string inside another (not case-sensitive, supports wildcards) |
| REPLACE | Replaces part of a text string with new text, by position |
| SUBSTITUTE | Replaces specific text inside a string with new text |
| TRIM | Removes extra spaces from text, leaving single spaces between words |
| CLEAN | Removes non-printable characters from text |
| UPPER | Converts text to uppercase |
| LOWER | Converts text to lowercase |
| PROPER | Capitalizes the first letter of each word |
| TEXT | Converts a number to text using a chosen format string |
| VALUE | Converts a text string that looks like a number into a real number |
| NUMBERVALUE | Converts text to a number with locale-aware decimal and group separators |
| EXACT | Returns TRUE if two text strings are identical (case-sensitive) |
| REPT | Repeats a text string a given number of times |
| CHAR | Returns the character that matches a given ASCII number |
| UNICHAR | Returns the character that matches a given Unicode number |
| CODE | Returns the ASCII number of the first character in a string |
| UNICODE | Returns the Unicode number of the first character in a string |
| T | Returns the text value of an argument, or empty if it is not text |
| FIXED | Formats a number with fixed decimals and returns it as text |
| DOLLAR | Formats a number as currency and returns it as text |
| ARRAYTOTEXT | Returns an array of values as a single text string |
| VALUETOTEXT | Returns the text representation of any value |
| ASC | Converts full-width characters to half-width |
| JIS | Converts half-width characters to full-width |
| DBCS | Converts half-width English letters to full-width |
| PHONETIC | Extracts the phonetic characters from a Japanese text string |
| BAHTTEXT | Converts a number to Thai text with a baht suffix |
—
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 |
|---|---|
| TODAY | Returns the current date |
| NOW | Returns the current date and time |
| DATE | Builds a date from a year, month, and day |
| TIME | Builds a time from an hour, minute, and second |
| YEAR | Returns the year from a date |
| MONTH | Returns the month from a date |
| DAY | Returns the day of the month from a date |
| HOUR | Returns the hour from a time value |
| MINUTE | Returns the minute from a time value |
| SECOND | Returns the second from a time value |
| 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 |
| ISOWEEKNUM | Returns the ISO week number of a date |
| EOMONTH | Returns the last day of the month, N months before or after a date |
| EDATE | Returns the date N months before or after a given date |
| DATEDIF | Returns the difference between two dates in years, months, or days |
| NETWORKDAYS | Returns the number of working days between two dates |
| NETWORKDAYS.INTL | Like NETWORKDAYS but lets you customize which days count as weekends |
| WORKDAY | Returns the date N working days before or after a given date |
| WORKDAY.INTL | Like WORKDAY with custom weekend definitions |
| DAYS | Returns the number of days between two dates |
| DAYS360 | Returns the number of days between two dates assuming a 360-day year |
| DATEVALUE | Converts a date stored as text into a real date serial number |
| TIMEVALUE | Converts a time stored as text into a real time serial number |
| 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 |
|---|---|
| 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 |
| PRODUCT | Multiplies its arguments together |
| QUOTIENT | Returns the integer portion of a division |
| MOD | Returns the remainder after a division |
| ROUND | Rounds a number to a given number of decimal places |
| ROUNDUP | Rounds a number up, away from zero |
| ROUNDDOWN | Rounds a number down, toward zero |
| MROUND | Rounds a number to the nearest multiple of another number |
| INT | Rounds a number down to the nearest integer |
| TRUNC | Removes the decimal portion of a number |
| EVEN | Rounds a number up to the nearest even integer |
| ODD | Rounds a number up to the nearest odd integer |
| FLOOR | Rounds a number down to the nearest multiple |
| FLOOR.MATH | Rounds down with control over how negatives are handled |
| CEILING | Rounds a number up to the nearest multiple |
| CEILING.MATH | Rounds up with control over how negatives are handled |
| ABS | Returns the absolute value of a number |
| SIGN | Returns 1, 0, or -1 depending on the sign of the number |
| POWER | Raises a number to a given power |
| SQRT | Returns the square root of a number |
| SQRTPI | Returns the square root of a number multiplied by pi |
| EXP | Returns e raised to a given power |
| 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 |
| FACT | Returns the factorial of a number |
| FACTDOUBLE | Returns the double factorial of a number |
| GCD | Returns the greatest common divisor of a set of numbers |
| LCM | Returns the least common multiple of a set of numbers |
| COMBIN | Returns the number of combinations of a given size |
| COMBINA | Returns combinations with repetitions allowed |
| PERMUT | Returns the number of permutations of a given size |
| PERMUTATIONA | Returns permutations with repetitions allowed |
| RAND | Returns a random number between 0 and 1 |
| RANDBETWEEN | Returns a random integer between two bounds |
| RANDARRAY | Returns an array of random numbers |
| ROMAN | Converts an Arabic numeral to Roman numerals as text |
| ARABIC | Converts a Roman numeral string to an Arabic number |
| BASE | Converts a number to text in a given numeric base |
| DECIMAL | Converts text in a given base to a decimal number |
| SUBTOTAL | Returns a subtotal of a range, ignoring other subtotals |
| AGGREGATE | Like SUBTOTAL but with more functions and the ability to ignore errors |
| SEQUENCE | Returns an array of sequential numbers |
| MUNIT | Returns an identity matrix of a given size |
| MMULT | Returns the matrix product of two arrays |
| MINVERSE | Returns the matrix inverse of an array |
| MDETERM | Returns the matrix determinant of an array |
| SERIESSUM | Returns the sum of a power series |
| PI | Returns the value of pi |
| SIN | Returns the sine of an angle |
| COS | Returns the cosine of an angle |
| TAN | Returns the tangent of an angle |
| ASIN | Returns the arcsine of a number |
| ACOS | Returns the arccosine of a number |
| ATAN | Returns the arctangent of a number |
| ATAN2 | Returns the arctangent from x and y coordinates |
| SINH | Returns the hyperbolic sine of a number |
| COSH | Returns the hyperbolic cosine of a number |
| TANH | Returns the hyperbolic tangent of a number |
| ASINH | Returns the inverse hyperbolic sine of a number |
| ACOSH | Returns the inverse hyperbolic cosine of a number |
| ATANH | Returns the inverse hyperbolic tangent of a number |
| DEGREES | Converts radians to degrees |
| RADIANS | Converts degrees to radians |
—
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 |
|---|---|
| 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 |
| MEDIAN | Returns the middle value in a range |
| MODE | Returns the most frequently occurring value in a range |
| MODE.SNGL | Returns the single most common value in a range |
| MODE.MULT | Returns an array of the most common values in a range |
| 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 |
| 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 |
| 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 |
| LARGE | Returns the Nth largest value in a range |
| SMALL | Returns the Nth smallest value in a range |
| RANK.EQ | Returns the rank of a number in a list, with ties getting the same rank |
| RANK.AVG | Returns the average rank of a number when there are ties |
| PERCENTILE.INC | Returns the Nth percentile of a range, inclusive |
| PERCENTILE.EXC | Returns the Nth percentile of a range, exclusive |
| PERCENTRANK.INC | Returns the percentile rank of a value, inclusive |
| PERCENTRANK.EXC | Returns the percentile rank of a value, exclusive |
| QUARTILE.INC | Returns the quartile of a range, inclusive |
| QUARTILE.EXC | Returns the quartile of a range, exclusive |
| FREQUENCY | Returns a frequency distribution as an array |
| STDEV.S | Returns the sample standard deviation |
| STDEV.P | Returns the population standard deviation |
| STDEVA | Like STDEV.S but counts text and logical values |
| STDEVPA | Like STDEV.P but counts text and logical values |
| VAR.S | Returns the sample variance |
| VAR.P | Returns the population variance |
| VARA | Like VAR.S but counts text and logical values |
| VARPA | Like VAR.P but counts text and logical values |
| DEVSQ | Returns the sum of squared deviations from the mean |
| AVEDEV | Returns the average of absolute deviations from the mean |
| GEOMEAN | Returns the geometric mean of a range |
| HARMEAN | Returns the harmonic mean of a range |
| TRIMMEAN | Returns the mean of a range with the top and bottom percentages excluded |
| KURT | Returns the kurtosis of a distribution |
| SKEW | Returns the skewness of a distribution |
| SKEW.P | Returns the skewness of a population |
| CORREL | Returns the correlation coefficient between two ranges |
| PEARSON | Returns the Pearson correlation coefficient |
| RSQ | Returns the squared Pearson correlation coefficient |
| COVARIANCE.S | Returns the sample covariance of two ranges |
| COVARIANCE.P | Returns the population covariance of two ranges |
| SLOPE | Returns the slope of a linear regression line |
| INTERCEPT | Returns the y-intercept of a linear regression line |
| LINEST | Returns the parameters of a linear best-fit line |
| LOGEST | Returns the parameters of an exponential best-fit curve |
| FORECAST.LINEAR | Predicts a future value using linear regression |
| FORECAST.ETS | Predicts a future value using exponential smoothing |
| TREND | Returns predicted values along a linear trend |
| GROWTH | Returns predicted values along an exponential trend |
| STANDARDIZE | Returns a normalized z-score |
| CONFIDENCE.NORM | Returns the confidence interval for a normal distribution |
| 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 |
| T.DIST | Returns the Student t distribution |
| T.INV | Returns the inverse of the Student t distribution |
| CHISQ.DIST | Returns the chi-square distribution |
| CHISQ.INV | Returns the inverse of the chi-square distribution |
| F.DIST | Returns the F probability distribution |
| F.INV | Returns the inverse of the F distribution |
| BINOM.DIST | Returns the binomial distribution probability |
| POISSON.DIST | Returns the Poisson distribution probability |
| EXPON.DIST | Returns the exponential distribution probability |
| GAMMA.DIST | Returns the gamma distribution probability |
| BETA.DIST | Returns the beta distribution probability |
| HYPGEOM.DIST | Returns the hypergeometric distribution probability |
| PROB | Returns the probability that values fall within a given range |
—
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 |
|---|---|
| PMT | Returns the periodic payment for a loan or annuity |
| IPMT | Returns the interest portion of a loan payment for a given period |
| PPMT | Returns the principal portion of a loan payment for a given period |
| CUMIPMT | Returns the cumulative interest paid between two periods |
| CUMPRINC | Returns the cumulative principal paid between two periods |
| PV | Returns the present value of an investment |
| FV | Returns the future value of an investment |
| FVSCHEDULE | Returns the future value with a series of variable interest rates |
| NPV | Returns the net present value of a series of cash flows |
| XNPV | Returns the net present value for cash flows on irregular dates |
| IRR | Returns the internal rate of return for a series of cash flows |
| XIRR | Returns the IRR for cash flows on irregular dates |
| MIRR | Returns the modified internal rate of return |
| RATE | Returns the interest rate per period of a loan or annuity |
| NPER | Returns the number of periods in a loan or annuity |
| NOMINAL | Returns the nominal annual interest rate |
| EFFECT | Returns the effective annual interest rate |
| RRI | Returns the equivalent interest rate for the growth of an investment |
| PDURATION | Returns the number of periods required for an investment to reach a target value |
| DURATION | Returns the Macaulay duration of a security |
| MDURATION | Returns the modified Macaulay duration of a security |
| DB | Returns the depreciation of an asset using the fixed-declining-balance method |
| DDB | Returns the depreciation using the double-declining-balance method |
| SLN | Returns the straight-line depreciation of an asset |
| SYD | Returns the sum-of-years depreciation of an asset |
| VDB | Returns the depreciation using a variable declining-balance method |
| ACCRINT | Returns the accrued interest on a security that pays periodic interest |
| ACCRINTM | Returns the accrued interest on a security that pays at maturity |
| 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 |
| COUPPCD | Returns the previous coupon date before settlement |
| COUPNUM | Returns the number of coupons payable between settlement and maturity |
| DISC | Returns the discount rate for a security |
| INTRATE | Returns the interest rate for a fully invested security |
| RECEIVED | Returns the amount received at maturity for a fully invested security |
| 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 |
| 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 |
| 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 |
| 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 |
| DOLLARDE | Converts a dollar price expressed as a fraction to a decimal |
| DOLLARFR | Converts a dollar price expressed as a decimal to a fraction |
—
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 |
|---|---|
| ISBLANK | Returns TRUE if the cell is empty |
| ISERROR | Returns TRUE if the value is any error |
| ISERR | Returns TRUE if the value is any error except #N/A |
| ISNA | Returns TRUE if the value is the #N/A error |
| ISNUMBER | Returns TRUE if the value is a number |
| ISTEXT | Returns TRUE if the value is text |
| ISNONTEXT | Returns TRUE if the value is not text |
| ISLOGICAL | Returns TRUE if the value is a logical value |
| ISREF | Returns TRUE if the value is a reference |
| ISFORMULA | Returns TRUE if the cell contains a formula |
| ISEVEN | Returns TRUE if the number is even |
| ISODD | Returns TRUE if the number is odd |
| ISOMITTED | In LAMBDA, returns TRUE if the argument is missing |
| TYPE | Returns a number indicating the data type of a value |
| ERROR.TYPE | Returns a number indicating which error a value is |
| N | Converts a value to a number |
| NA | Returns the #N/A error |
| INFO | Returns information about the current operating environment |
| CELL | Returns information about a cell’s formatting, location, or contents |
| SHEET | Returns the sheet number of a referenced sheet |
| SHEETS | Returns the number of sheets in a reference |
—
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 |
|---|---|
| DSUM | Sums values in a database column that match given criteria |
| 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 |
| DVAR | Returns the sample variance of matching values |
| DVARP | Returns the population variance of matching values |
—
Dynamic Array Functions
Use these in Excel 365 and Excel 2021 onwards. They return a range of results that automatically spills into surrounding cells, no Ctrl+Shift+Enter required. If you are writing modern Excel formulas, you are probably already living in this section.
| Function | Description |
|---|---|
| FILTER | Returns the rows of a range that meet a condition |
| SORT | Sorts a range by one or more columns |
| SORTBY | Sorts a range by the values in another range |
| UNIQUE | Returns the unique values from a range |
| SEQUENCE | Returns an array of sequential numbers |
| RANDARRAY | Returns an array of random numbers |
| EXPAND | Pads an array with a given value to a target size |
| TOROW | Reshapes an array into a single row |
| TOCOL | Reshapes an array into a single column |
| TAKE | Returns a specified number of rows or columns from the start or end of an array |
| DROP | Drops a specified number of rows or columns from an array |
| CHOOSEROWS | Returns chosen rows from an array |
| CHOOSECOLS | Returns chosen columns from an array |
| HSTACK | Stacks arrays horizontally |
| VSTACK | Stacks arrays vertically |
| WRAPROWS | Wraps a 1D array into rows of a given length |
| WRAPCOLS | Wraps a 1D array into columns of a given length |
| BYROW | Applies a LAMBDA to each row of an array |
| BYCOL | Applies a LAMBDA to each column of an array |
| MAP | Applies a LAMBDA to each value of one or more arrays |
| REDUCE | Reduces an array to a single value with a LAMBDA |
| SCAN | Returns the running totals of applying a LAMBDA to an array |
| MAKEARRAY | Builds an array of a given size from a LAMBDA |
| GROUPBY | Groups rows of data and aggregates each group |
| PIVOTBY | Builds a pivot-table-style result inside a formula |
| PERCENTOF | Returns each value as a percent of the total |
—
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 |
|---|---|
| BIN2DEC | Converts a binary number to decimal |
| BIN2HEX | Converts a binary number to hexadecimal |
| BIN2OCT | Converts a binary number to octal |
| DEC2BIN | Converts a decimal number to binary |
| DEC2HEX | Converts a decimal number to hexadecimal |
| DEC2OCT | Converts a decimal number to octal |
| HEX2BIN | Converts a hexadecimal number to binary |
| HEX2DEC | Converts a hexadecimal number to decimal |
| HEX2OCT | Converts a hexadecimal number to octal |
| OCT2BIN | Converts an octal number to binary |
| OCT2DEC | Converts an octal number to decimal |
| OCT2HEX | Converts an octal number to hexadecimal |
| CONVERT | Converts a number from one unit of measure to another |
| BITAND | Bitwise AND of two numbers |
| BITOR | Bitwise OR of two numbers |
| BITXOR | Bitwise exclusive OR of two numbers |
| BITLSHIFT | Shifts a number left by a given number of bits |
| BITRSHIFT | Shifts a number right by a given number of bits |
| DELTA | Returns 1 if two numbers are equal, 0 otherwise |
| GESTEP | Returns 1 if a number is greater than or equal to a step, 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) |
| COMPLEX | Builds a complex number from real and imaginary parts |
| 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 |
| IMREAL | Returns the real part of a complex number |
| IMSUM | Sums complex numbers |
| IMSUB | Subtracts complex numbers |
| IMPRODUCT | Multiplies complex numbers |
| IMDIV | Divides complex numbers |
| IMPOWER | Raises a complex number to a power |
| IMSQRT | Returns the square root of a complex number |
| 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 |
| IMSIN | Returns the sine of a complex number |
| IMCOS | Returns the cosine of a complex number |
| IMTAN | Returns the tangent of a complex number |
| IMSINH | Returns the hyperbolic sine of a complex number |
| IMCOSH | Returns the hyperbolic cosine of a complex number |
| IMSEC | Returns the secant of a complex number |
| IMSECH | Returns the hyperbolic secant of a complex number |
| IMCSC | Returns the cosecant of a complex number |
| IMCSCH | Returns the hyperbolic cosecant of a complex number |
| IMCOT | Returns the cotangent of a complex number |
—
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 |
|---|---|
| WEBSERVICE | Returns data from a web service |
| FILTERXML | Returns specific data from XML content using XPath |
| ENCODEURL | Returns a URL-encoded string |
—
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.
