The Comprehensive Excel Dictionary of Functions
The Excel dictionary of functions below lists every major worksheet function in one place, 349 of them, grouped into eight categories with a plain-English description of what each one does. Whether you are just starting out or have used Excel for years, it is the quickest way to find the right function for a job.
Functions are what turn Excel from a grid of numbers into a tool that calculates, analyzes, and automates. The trouble is that there are hundreds of them, and most people only know a few. Knowing what else exists, even roughly, means you can reach for the right tool instead of building a complicated workaround.
Each category starts with a short guide to what the functions are for and which ones are worth learning first, with a worked example, followed by the complete list. Use the function finder to search every function by name or by task, and copy any list straight into Excel.
How to Use This Dictionary
You do not need to memorize this page. Most people use a few dozen functions regularly and look up the rest when a new problem comes along. Start with the category that matches your task, read the short guide at the top, and scan the list for a function whose description fits what you need.
Every function is written the same way: its name, an opening parenthesis, the inputs it needs separated by commas, and a closing parenthesis, all after an equals sign, as in =SUM(A2:A10). When you start typing a function name in Excel, a tooltip shows the inputs it expects, so you rarely need to remember them. Microsoft also keeps an official list of Excel functions by category with full details on every argument.
A few functions in this dictionary, including XLOOKUP, FILTER, SORT, UNIQUE, and TEXTJOIN, are available only in Microsoft 365 and other newer versions of Excel. If you share workbooks with people on older versions, check that the functions you use will work for them.
Search Every Excel Function
Excel Function Finder
Search all 349 functions on this page by name or by what you need to do, such as "loan," "round," "days," or "spaces." Pick a category to narrow the list. Matching rows stay visible in the tables below, and Copy to Excel copies exactly what is showing. Nothing you type is saved or sent anywhere.
Excel Financial Functions
Financial functions handle the math of money over time: loan payments, interest, investment returns, depreciation, and the pricing of bonds and other securities. They look intimidating because of their long argument lists, but most small businesses only ever need a handful of them.
The ones worth learning first are PMT, which calculates a loan payment, FV and PV, which move money forward and backward in time, NPV and IRR, which tell you whether an investment is worth making, and SLN for straight-line depreciation. For example, =PMT(6%/12, 60, -50000) returns the monthly payment on a $50,000 loan at 6% over five years, about $966.64.
The long tail of functions such as ACCRINT, COUPDAYS, and YIELDDISC exists mainly for bond and securities analysis. If you do not work with fixed-income investments, you can safely skip them. When you do use financial functions, keep the rate and the number of periods in the same units, dividing an annual rate by 12 for monthly payments, and enter money you pay out as a negative number.
| Function | What It Does |
|---|---|
| ACCRINT | Returns the accrued interest for a security that pays periodic interest. |
| ACCRINTM | Returns the accrued interest for a security that pays interest at maturity. |
| AMORDEGRC | Returns the depreciation for each accounting period by using a depreciation coefficient. |
| AMORLINC | Returns the depreciation for each accounting period. |
| COUPDAYBS | Returns the number of days from the beginning of the coupon period to the settlement date. |
| COUPDAYS | Returns the number of days in the coupon period that contains the settlement date. |
| COUPDAYSNC | Returns the number of days from the settlement date to the next coupon date. |
| COUPNCD | Returns the next coupon date after the settlement date. |
| COUPNUM | Returns the number of coupons payable between the settlement date and maturity date. |
| COUPPCD | Returns the previous coupon date before the settlement date. |
| CUMIPMT | Returns the cumulative interest paid between two periods. |
| CUMPRINC | Returns the cumulative principal paid on a loan between two periods. |
| DB | Returns the depreciation of an asset for a specified period by using the fixed-declining balance method. |
| DDB | Returns the depreciation of an asset for a particular period using the double-declining balance method. |
| DISC | Returns the discount rate for a security. |
| DOLLARDE | Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal. |
| DOLLARFR | Converts a dollar price, expressed as a decimal, into a dollar price, expressed as a fraction. |
| DURATION | Returns the annual duration of a security with periodic interest payments. |
| EFFECT | Returns the effective annual interest rate. |
| FV | Returns the future value of an investment. |
| FVSCHEDULE | Returns the future value of an initial principal after applying a series of compound interest rates. |
| INTRATE | Returns the interest rate for a fully invested security. |
| IPMT | Returns the interest payment for an investment for a given period. |
| IRR | Returns the internal rate of return for a series of cash flows. |
| ISPMT | Calculates the interest paid during a specific period of an investment. |
| MDURATION | Returns the modified duration for a security with an assumed par value of $100. |
| MIRR | Returns the internal rate of return where positive and negative cash flows are financed at different rates. |
| NOMINAL | Returns the annual nominal interest rate. |
| NPER | Returns the number of periods for an investment. |
| NPV | Returns the net present value of an investment based on a series of periodic cash flows and a discount rate. |
| 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 specified value. |
| PMT | Returns the periodic payment for an annuity. |
| PPMT | Returns the payment on the principal for an investment for a given period. |
| PRICE | Returns 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 | 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 an annuity. |
| RECEIVED | Returns the amount received at maturity for a fully invested security. |
| RRI | Returns an equivalent interest rate for the growth of an investment. |
| SLN | Returns the straight-line depreciation of an asset for one period. |
| SYD | Returns the sum-of-years' digits depreciation of an asset for a specified 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. |
| VDB | Returns the depreciation of an asset for a specified or partial period by using a declining balance method. |
| XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. |
| XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic. |
| 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. |
Logical Functions
Logical functions let a spreadsheet make decisions. They test whether something is true or false and return different results depending on the answer, which is how you turn business rules into automatic labels, flags, and calculations.
IF is the foundation: =IF(B2>=10000, "Target Met", "Below Target") labels every row based on a sales number. AND and OR combine several tests, IFS checks a list of conditions in order without stacking IF inside IF, and SWITCH matches one value against a list of options.
IFERROR and IFNA are the most practical functions in this group for everyday reports. Wrapping a lookup in IFNA, as in =IFNA(XLOOKUP(A2, B:B, C:C), "Not found"), replaces an ugly #N/A with a clear message while still letting other errors show, so real problems do not get hidden.
| Function | What It Does |
|---|---|
| AND | Returns TRUE if all arguments are TRUE. |
| FALSE | Returns the logical value FALSE. |
| IF | Returns one value if a condition is true and another if it is false. |
| IFERROR | Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. |
| IFNA | Returns the value you specify if the expression resolves to #N/A; otherwise, it returns the result of the expression. |
| IFS | Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. |
| NOT | Reverses the logic of its argument. |
| OR | Returns TRUE if any argument is TRUE. |
| SWITCH | Evaluates an expression against a list of values and returns the result corresponding to the first matching value. |
| TRUE | Returns the logical value TRUE. |
| XOR | Returns a logical exclusive OR of all arguments. |
Text Functions
Text functions clean, split, join, and reformat text. They are the tools you reach for when data arrives messy, with extra spaces, inconsistent capitalization, or codes that need to be broken into parts.
TRIM removes stray spaces that break lookups, PROPER, UPPER, and LOWER fix capitalization, and LEFT, RIGHT, and MID pull pieces out of a code such as a product SKU. To join text, TEXTJOIN is usually the best choice because it adds a separator and can skip blanks: =TEXTJOIN(", ", TRUE, A2:C2) turns three cells into one tidy line.
Two details trip people up. FIND is case sensitive while SEARCH is not, and functions such as LEFT and MID always return text, even when the result looks like a number. Wrap the result in VALUE, or use NUMBERVALUE, when you need to do math with it.
| Function | What It Does |
|---|---|
| BAHTTEXT | Converts a number to Thai text with the baht currency name. |
| CHAR | Returns the character specified by a number. |
| CLEAN | Removes all nonprintable characters from text. |
| CODE | Returns a numeric code for the first character in a text string. |
| CONCAT | Combines the text from multiple ranges and/or strings. |
| CONCATENATE | Joins two or more text strings. Newer versions of Excel replace it with CONCAT. |
| DOLLAR | Converts a number to text using currency format. |
| EXACT | Checks to see if two text values are identical. |
| FIND | Finds one text value within another (case-sensitive). |
| FIXED | Rounds a number to a specified number of decimals and returns the result as text. |
| LEFT | Returns the leftmost characters from a text value. |
| LEN | Returns the number of characters in a text string. |
| LOWER | Converts text to lowercase. |
| MID | Returns a specific number of characters from a text string starting at the position you specify. |
| NUMBERVALUE | Converts text to number in a locale-independent manner. |
| PROPER | Capitalizes the first letter of each word in a text string. |
| REPLACE | Replaces part of a text string with a different text string. |
| REPT | Repeats text a given number of times. |
| RIGHT | Returns the rightmost characters from a text value. |
| SEARCH | Finds one text value within another (not case-sensitive). |
| SUBSTITUTE | Substitutes new text for old text in a text string. |
| T | Returns the value if it is text, or empty text if it is not. |
| TEXT | Formats a number and converts it to text. |
| TEXTJOIN | Combines the text from multiple ranges and/or strings, with a delimiter separating the values. |
| TRIM | Removes extra spaces from text, leaving single spaces between words. |
| UNICHAR | Returns the Unicode character that is referenced by the given numeric value. |
| UNICODE | Returns the number (code point) that corresponds to the first character of the text. |
| UPPER | Converts text to uppercase. |
| VALUE | Converts a text argument to a number. |
Date and Time Functions
Excel stores every date as a serial number, counting days, and every time as a fraction of a day. That is why you can subtract one date from another to get the number of days between them, and why date functions can add months, find weekdays, and count working days so easily.
TODAY and NOW return the current date and time, EDATE adds whole months and handles month ends correctly, and EOMONTH returns the last day of a month, which is ideal for due dates. NETWORKDAYS counts business days between two dates, skipping weekends and an optional list of holidays, while WORKDAY finds a date a set number of business days ahead.
If a date calculation shows a strange five-digit number, the formula is working and the cell simply needs a date format. And if a date typed or imported as text will not calculate, DATEVALUE converts it into a real date that Excel can work with.
| Function | What It Does |
|---|---|
| DATE | Returns the serial number of a particular date. |
| DATEVALUE | Converts a date in the form of text to a serial number. |
| DAY | Converts a serial number to a day of the month. |
| DAYS | Returns the number of days between two dates. |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year. |
| EDATE | Returns the serial number of the date that is the indicated number of months before or after the start date. |
| EOMONTH | Returns the serial number of the last day of the month before or after a specified number of months. |
| HOUR | Converts a serial number to an hour. |
| ISOWEEKNUM | Returns the number of the ISO week number of the year for a given date. |
| MINUTE | Converts a serial number to a minute. |
| MONTH | Converts a serial number to a month. |
| NETWORKDAYS | Returns the number of whole workdays between two dates. |
| NETWORKDAYS.INTL | Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. |
| NOW | Returns the serial number of the current date and time. |
| SECOND | Converts a serial number to a second. |
| TIME | Returns the serial number of a particular time. |
| TIMEVALUE | Converts a time in the form of text to a serial number. |
| TODAY | Returns the serial number of today's date. |
| WEEKDAY | Converts a serial number to a day of the week. |
| WEEKNUM | Converts a serial number to a number representing where the week falls numerically within a year. |
| WORKDAY | Returns the serial number of the date before or after a specified number of workdays. |
| WORKDAY.INTL | Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days. |
| YEAR | Converts a serial number to a year. |
| YEARFRAC | Returns the year fraction representing the number of whole days between start_date and end_date. |
Lookup and Reference Functions
Lookup and reference functions find information in one place and bring it to another. They are the backbone of price lists, invoices, and reports that pull details from a master table instead of copying them by hand.
XLOOKUP is the modern choice for most lookups: =XLOOKUP(A2, Products!A:A, Products!C:C, "Not found") finds a product ID and returns its price, in any direction, with a built-in message for missing values. In older versions of Excel, INDEX combined with MATCH does the same job, and VLOOKUP remains common in existing workbooks.
This group also includes the dynamic array functions FILTER, SORT, SORTBY, and UNIQUE, which return whole lists that update automatically when the source data changes. They need empty space below and to the right to spill into, and they are available in Microsoft 365 and other newer versions of Excel.
| Function | What It Does |
|---|---|
| ADDRESS | Returns a reference as text to a single cell in a worksheet. |
| AREAS | Returns the number of areas in a reference. |
| CHOOSE | Chooses a value from a list of values. |
| COLUMN | Returns the column number of a reference. |
| COLUMNS | Returns the number of columns in a reference. |
| FILTER | Filters a range of data based on criteria you define. |
| FORMULATEXT | Returns the formula at the given reference as text. |
| HLOOKUP | Looks in the top row of an array and returns the value of the indicated cell. |
| HYPERLINK | Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. |
| INDEX | Uses an index to choose a value from a reference or array. |
| INDIRECT | Returns a reference indicated by a text value. |
| LOOKUP | Looks up a value in one row or column and returns the value in the same position in another. |
| MATCH | Returns the position of a value within a row or column. |
| OFFSET | Returns a reference offset from a given reference. |
| ROW | Returns the row number of a reference. |
| ROWS | Returns the number of rows in a reference. |
| SORT | Sorts the contents of a range or array. |
| SORTBY | Sorts the contents of a range or array based on the values in a corresponding range or array. |
| TRANSPOSE | Returns the transpose of an array. |
| UNIQUE | Returns a list of unique values in a list or range. |
| VLOOKUP | Looks in the first column of an array and moves across the row to return the value of a cell. |
| XLOOKUP | Searches a range for a value and returns the matching item from another range, in any direction. |
| XMATCH | Returns the position of a value in a range, with options for exact, approximate, and reverse searches. |
Math and Trigonometry Functions
Math and trigonometry functions cover everything from simple totals to rounding, random numbers, and matrix math. Despite the name, most of what business users need here has nothing to do with trigonometry.
SUM, SUMIF, and SUMIFS are the everyday workhorses. =SUMIFS(C:C, A:A, "North", B:B, ">500") totals only the sales from the North region over $500. ROUND, ROUNDUP, and ROUNDDOWN control decimals, MROUND rounds to a multiple such as the nearest quarter hour, and SUMPRODUCT multiplies and adds in one step, which is handy for weighted totals.
SUBTOTAL and AGGREGATE are worth knowing too, because they can ignore hidden or filtered rows, so a total under a filtered list shows only what you can see. The trigonometric and hyperbolic functions, from SIN to TANH, are mainly used in engineering and science work.
| Function | What It Does |
|---|---|
| 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 arccotangent of a number. |
| ACOTH | Returns the inverse hyperbolic cotangent of a number. |
| AGGREGATE | Returns an aggregate in a list or database. |
| ARABIC | Converts a Roman numeral to Arabic, as a 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 the x- and y-coordinates. |
| ATANH | Returns the inverse hyperbolic tangent of a number. |
| BASE | Converts a number into a text representation with the given radix (base). |
| CEILING | Rounds a number up to the nearest multiple of significance. |
| CEILING.MATH | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| CEILING.PRECISE | Rounds a number up, to the nearest integer or to the nearest multiple of significance. |
| COMBIN | Returns the number of combinations for a given number of objects. |
| COMBINA | Returns the number of combinations with repetitions for a given number of items. |
| COS | Returns the cosine of a number. |
| COSH | Returns the hyperbolic cosine of a number. |
| COT | Returns the cotangent of an angle. |
| COTH | Returns the hyperbolic cotangent of a number. |
| CSC | Returns the cosecant of an angle. |
| CSCH | Returns the hyperbolic cosecant of an angle. |
| DECIMAL | Converts a text representation of a number in a given base into a decimal number. |
| DEGREES | Converts radians to degrees. |
| EVEN | Rounds a number up to the nearest even integer. |
| EXP | Returns e raised to the power of a given number. |
| FACT | Returns the factorial of a number. |
| FACTDOUBLE | Returns the double factorial of a number. |
| FLOOR | Rounds a number down to the nearest multiple of significance. |
| FLOOR.MATH | Rounds a number down, to the nearest integer or to the nearest multiple of significance. |
| FLOOR.PRECISE | Rounds a number down, to the nearest integer or to the nearest multiple of significance. |
| GCD | Returns the greatest common divisor. |
| INT | Rounds a number down to the nearest integer. |
| ISO.CEILING | Rounds a number up to the nearest integer or to the nearest multiple of significance. |
| LCM | Returns the least common multiple. |
| LN | Returns the natural logarithm of a number. |
| LOG | Returns the logarithm of a number to a specified 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 from division. |
| MROUND | Returns a number rounded to the desired multiple. |
| MULTINOMIAL | Returns the multinomial of a set of numbers. |
| MUNIT | Returns the unit matrix for the specified dimension. |
| ODD | Rounds a number up to the nearest odd integer. |
| PI | Returns the value of pi. |
| POWER | Returns the result of a number raised to a power. |
| PRODUCT | Multiplies its arguments. |
| QUOTIENT | Returns the integer portion of a division. |
| RADIANS | Converts degrees to radians. |
| RAND | Returns a random number between 0 and 1. |
| RANDBETWEEN | Returns a random number between the numbers you specify. |
| ROMAN | Converts an Arabic numeral to Roman, as text. |
| ROUND | Rounds a number to a specified number of digits. |
| ROUNDDOWN | Rounds a number down, toward zero. |
| ROUNDUP | Rounds a number up, away from zero. |
| SEC | Returns the secant of an angle. |
| SECH | Returns the hyperbolic secant of an angle. |
| SERIESSUM | Returns the sum of a power series based on the formula. |
| SIGN | Returns the sign of a number. |
| SIN | Returns the sine of the given angle. |
| SINH | Returns the hyperbolic sine of a number. |
| SQRT | Returns a positive square root. |
| SQRTPI | Returns the square root of (number * pi). |
| SUBTOTAL | Returns a subtotal in a list or database. |
| SUM | Adds its arguments. |
| SUMIF | Adds the cells specified by a given condition or criteria. |
| SUMIFS | Adds the cells in a range that meet multiple criteria. |
| SUMPRODUCT | Returns the sum of the products of corresponding array components. |
| SUMSQ | Returns the sum of the squares of the arguments. |
| SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays. |
| SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays. |
| SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays. |
| TAN | Returns the tangent of a number. |
| TANH | Returns the hyperbolic tangent of a number. |
| TRUNC | Truncates a number to an integer. |
Statistical Functions
Statistical functions describe and analyze data: counting, averaging, finding the spread, ranking, forecasting, and running formal statistical tests. This is the largest category in Excel, but a small core covers most business needs.
COUNT, COUNTA, COUNTIF, and COUNTIFS count records, AVERAGE, MEDIAN, MIN, and MAX summarize them, and LARGE and SMALL find the top or bottom values. STDEV.S measures how consistent numbers are, RANK.EQ builds a leaderboard, and FORECAST.LINEAR projects a simple trend forward, such as next month's sales based on the past year.
Many functions in this group come in pairs ending in .S and .P. Use the .S version when your data is a sample of a larger group and the .P version when it is the entire population. Older names without a period, such as STDEV and VAR, still work for compatibility, but the newer versions are clearer about which calculation they perform.
| Function | What It Does |
|---|---|
| AVEDEV | Returns the average of the absolute deviations of data points from their mean. |
| AVERAGE | Returns the average of its arguments. |
| AVERAGEA | Returns the average of its arguments, including numbers, text, and logical values. |
| AVERAGEIF | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. |
| AVERAGEIFS | Returns the average (arithmetic mean) of all cells that meet multiple criteria. |
| BETA.DIST | Returns the beta distribution. |
| BETA.INV | Returns the inverse of the cumulative beta distribution. |
| BINOM.DIST | Returns the individual term binomial distribution probability. |
| BINOM.DIST.RANGE | Returns the probability of a trial result using a binomial distribution. |
| BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. |
| CHISQ.DIST | Returns the chi-squared distribution. |
| CHISQ.DIST.RT | Returns the right-tailed probability of the chi-squared distribution. |
| CHISQ.INV | Returns the inverse of the left-tailed probability of the chi-squared distribution. |
| CHISQ.INV.RT | Returns the inverse of the right-tailed probability of the chi-squared distribution. |
| CHISQ.TEST | Returns the test for independence. |
| CONFIDENCE.NORM | Returns the confidence interval for a population mean. |
| CONFIDENCE.T | Returns the confidence interval for a population mean, using a Student's t-distribution. |
| CORREL | Returns the correlation coefficient between two data sets. |
| COUNT | Counts the number of cells that contain numbers. |
| COUNTA | Counts the number of cells that are not empty. |
| COUNTBLANK | Counts the number of empty cells in a specified range of cells. |
| COUNTIF | Counts the number of cells within a range that meet a single criterion. |
| COUNTIFS | Counts the number of cells within a range that meet multiple criteria. |
| COVARIANCE.P | Returns the covariance, the average of the products of paired deviations in the data sets. |
| COVARIANCE.S | Returns the sample covariance, the average of the products of paired deviations in the data sets. |
| DEVSQ | Returns the sum of squares of deviations. |
| EXPON.DIST | Returns the exponential distribution. |
| F.DIST | Returns the F probability distribution. |
| F.DIST.RT | Returns the right-tailed F probability distribution. |
| F.INV | Returns the inverse of the F probability distribution. |
| F.INV.RT | Returns the inverse of the right-tailed F probability distribution. |
| F.TEST | Returns the result of an F-test. |
| FISHER | Returns the Fisher transformation. |
| FISHERINV | Returns the inverse of the Fisher transformation. |
| FORECAST.ETS | Returns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. |
| FORECAST.ETS.CONFINT | Returns a confidence interval for the forecast value at the specified target date. |
| FORECAST.ETS.SEASONALITY | Returns the length of the repetitive pattern Excel detects for the specified time series. |
| FORECAST.ETS.STAT | Returns a statistical value as a result of time series forecasting. |
| FORECAST.LINEAR | Returns a future value based on existing values. |
| FREQUENCY | Returns a frequency distribution as a vertical array. |
| GAMMA | Returns the gamma function value. |
| GAMMA.DIST | Returns the gamma distribution. |
| GAMMA.INV | Returns the inverse of the gamma cumulative distribution. |
| GAMMALN | Returns the natural logarithm of the gamma function, Γ(x). |
| GAMMALN.PRECISE | Returns the natural logarithm of the gamma function, Γ(x). |
| GAUSS | Returns 0.5 minus the standard normal cumulative distribution. |
| GEOMEAN | Returns the geometric mean. |
| GROWTH | Returns values along an exponential trend. |
| HARMEAN | Returns the harmonic mean. |
| HYPGEOM.DIST | Returns the hypergeometric distribution. |
| INTERCEPT | Returns the intercept of the linear regression line. |
| KURT | Returns the kurtosis of a data set. |
| LARGE | Returns the k-th largest value in a data set. |
| LINEST | Returns the parameters of a linear trend. |
| LOGEST | Returns the parameters of an exponential trend. |
| LOGNORM.DIST | Returns the cumulative lognormal distribution. |
| LOGNORM.INV | Returns the inverse of the lognormal cumulative distribution. |
| MAX | Returns the maximum value in a data set. |
| MAXA | Returns the maximum value in a data set, including numbers, text, and logical values. |
| MEDIAN | Returns the median of the given numbers. |
| MIN | Returns the minimum value in a data set. |
| MINA | Returns the smallest value in a data set, including numbers, text, and logical values. |
| MODE.MULT | Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. |
| MODE.SNGL | Returns the most frequently occurring value in a data set. |
| NEGBINOM.DIST | Returns the negative binomial distribution. |
| NORM.DIST | Returns the normal distribution for the specified mean and standard deviation. |
| NORM.INV | Returns the inverse of the normal cumulative distribution. |
| NORM.S.DIST | Returns the standard normal distribution. |
| NORM.S.INV | Returns the inverse of the standard normal cumulative distribution. |
| PEARSON | Returns the Pearson product moment correlation coefficient. |
| PERCENTILE.EXC | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. |
| PERCENTILE.INC | Returns the k-th percentile of values in a range. |
| PERCENTRANK.EXC | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. |
| PERCENTRANK.INC | Returns the rank of a value in a data set as a percentage of the data set. |
| PERMUT | Returns the number of permutations for a given number of objects. |
| PERMUTATIONA | Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. |
| PHI | Returns the value of the density function for a standard normal distribution. |
| POISSON.DIST | Returns the Poisson distribution. |
| PROB | Returns the probability that values in a range are between two limits. |
| QUARTILE.EXC | Returns the quartile of the data set, based on percentile values from 0..1, exclusive. |
| QUARTILE.INC | Returns the quartile of a data set. |
| RANK.AVG | Returns the rank of a number in a list; tied values receive the average rank. |
| RANK.EQ | Returns the rank of a number in a list; tied values receive the same, highest rank. |
| RSQ | Returns the square of the Pearson product moment correlation coefficient. |
| SKEW | Returns the skewness of a distribution. |
| SKEW.P | Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean. |
| SLOPE | Returns the slope of the linear regression line. |
| SMALL | Returns the k-th smallest value in a data set. |
| STANDARDIZE | Returns a normalized value. |
| STDEV.P | Calculates standard deviation based on the entire population. |
| STDEV.S | Estimates standard deviation based on a sample. |
| STDEVA | Estimates standard deviation based on a sample, including numbers, text, and logical values. |
| STDEVP | Calculates standard deviation based on the entire population. |
| STDEVPA | Calculates standard deviation based on the entire population, including numbers, text, and logical values. |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression. |
| T.DIST | Returns the left-tailed Student's t-distribution. |
| T.DIST.2T | Returns the two-tailed Student's t-distribution. |
| T.DIST.RT | Returns the right-tailed Student's t-distribution. |
| T.INV | Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom. |
| T.INV.2T | Returns the inverse of the Student's t-distribution. |
| T.TEST | Returns the probability associated with a Student's t-Test. |
| TREND | Returns values along a linear trend. |
| TRIMMEAN | Returns the mean of the interior of a data set. |
| VAR.P | Calculates variance based on the entire population. |
| VAR.S | Estimates variance based on a sample. |
| VARA | Estimates variance based on a sample, including numbers, text, and logical values. |
| VARP | Calculates variance based on the entire population. |
| VARPA | Calculates variance based on the entire population, including numbers, text, and logical values. |
| WEIBULL.DIST | Returns the Weibull distribution. |
| Z.TEST | Returns the one-tailed probability-value of a z-test. |
Information Functions
Information functions check what is in a cell rather than calculating with it. They answer questions such as whether a cell is blank, whether it holds a number or text, or whether a formula returned an error.
ISBLANK, ISNUMBER, and ISTEXT are the most useful, especially inside IF. For example, =IF(ISBLANK(C2), "Missing", C2*D2) only calculates a line total when a quantity has been entered. ISERROR and ISNA let you test for errors before deciding what to show.
ISFORMULA and FORMULATEXT, which sits in the lookup group, are handy when auditing a workbook, because they show which cells hold formulas and what those formulas are. CELL and INFO return details about formatting, file location, and the environment, which advanced users sometimes use to build self-documenting workbooks.
| Function | What It Does |
|---|---|
| CELL | Returns information about the formatting, location, or contents of a cell. |
| ERROR.TYPE | Returns a number corresponding to an error type. |
| INFO | Returns information about the current operating environment. |
| ISBLANK | Returns TRUE if the value is blank. |
| ISERR | Returns TRUE if the value is any error value except #N/A. |
| ISERROR | Returns TRUE if the value is any error value. |
| ISEVEN | Returns TRUE if the number is even. |
| ISFORMULA | Returns TRUE if there is a reference to a cell that contains a formula. |
| ISLOGICAL | Returns TRUE if the value is a logical value. |
| ISNA | Returns TRUE if the value is the #N/A error value. |
| 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. |
| ISREF | Returns TRUE if the value is a reference. |
| ISTEXT | Returns TRUE if the value is text. |
| N | Returns a value converted to a number. |
| NA | Returns the error value #N/A. |
| TYPE | Returns a number indicating the data type of a value. |
Excel Formula Tips
Use Named Ranges
Instead of referring to cells by address, you can give a range a name, such as TaxRate or SalesData, and use that name in formulas. =Subtotal*TaxRate is far easier to read and check than =B14*$F$2. To create a name, select the range and type it into the Name Box to the left of the formula bar.
Lock References With Dollar Signs
When you copy a formula, Excel shifts its cell references to match. Adding dollar signs, as in $A$1, locks a reference so it always points to the same cell, which is essential for things like a tax rate or exchange rate stored in one place. Pressing F4 while editing a reference cycles through the locking options.
Combine Functions
Functions can be nested inside each other to handle more complex logic. IF combined with AND or OR creates tests with several conditions, and IFNA wrapped around a lookup handles missing values gracefully. Build and test the inner function first, then wrap it.
Use Dynamic Arrays
In Microsoft 365 and newer versions, functions like FILTER, SORT, and UNIQUE return a whole list that spills into neighboring cells automatically. In older versions, formulas that work on arrays had to be entered with Ctrl, Shift, and Enter together. If you see curly braces around a formula in an older workbook, that is why.
Let AutoComplete Help
Start typing a function name after an equals sign, and Excel shows a list of matching functions with a short description of each. Press Tab to insert one, and the tooltip then shows each argument as you type, so you always know what comes next.
Break Complex Formulas Into Steps
A long formula is hard to check and harder to fix. Put intermediate calculations in their own cells, confirm each one is right, and then combine them, or use the LET function in newer versions to name the steps inside a single formula.
Handle Errors on Purpose
IFERROR and IFNA keep reports clean when data is missing, but they can also hide genuine mistakes. Use IFNA around lookups where a missing value is expected, and save IFERROR for cases where you truly want every error replaced.
Try Quick Analysis
Select a block of data and click the Quick Analysis button that appears at the bottom-right corner. It offers instant totals, charts, conditional formatting, and tables, which is a fast way to explore data before writing any formulas.
Format Long Formulas for Readability
In the formula bar, you can press Alt and Enter to add a line break inside a long formula, and expand the formula bar to see it all. Splitting a nested formula across lines, one function per line, makes it much easier for you and others to follow.
Document Your Work
Add notes to cells with complex formulas, or keep a short "How this works" sheet in the workbook. Anyone who opens the file later, including you, will understand what each calculation is doing and why.
Keep Learning Excel
If you want the most useful functions explained in depth with worked business examples, our Excel formulas cheat sheet covers 37 of them and includes a printable one-page reference. Our guide to 10 Excel functions every user should know is a good place to start, and our free Excel courses take you from the basics to building complete business tools.
When your spreadsheets start running the business, Updoot brings budgets, projects, time tracking, and invoicing into one place as business management software for $5 per user per month, and every report still exports to Excel.
Opens in Google Drive. View and download for free.
Frequently Asked Questions About Excel Functions
How many functions does Excel have?
Excel has several hundred worksheet functions, and Microsoft adds new ones over time. This dictionary covers 349 of the main ones across eight categories.
What is the difference between a formula and a function?
A formula is anything you type after an equals sign to calculate a result. A function is a built-in, named calculation, such as SUM or XLOOKUP, that you use inside a formula.
Which Excel functions should I learn first?
Start with SUM, AVERAGE, COUNTIF, IF, XLOOKUP or VLOOKUP, SUMIFS, IFERROR, TRIM, and ROUND. Together they cover most everyday spreadsheet tasks.
What are the main categories of Excel functions?
The main categories are financial, logical, text, date and time, lookup and reference, math and trigonometry, statistical, and information functions. Excel also has engineering, database, and web functions for specialized work.
Why does my function show #NAME?
#NAME? usually means the function name is misspelled, text is missing its quotation marks, or the function is not available in your version of Excel.
What does the .S or .P at the end of some functions mean?
Functions such as STDEV.S and STDEV.P come in pairs. Use .S when your data is a sample of a larger group and .P when your data includes the entire population.