Excel formula finder and maker

Search every Excel function (523 of them) with plain English help and examples, or describe what you need and get a ready formula you can copy.

523 Excel functions
Formula maker
Free, no account

Loading the formula finder…

Lookup and reference functions

ADDRESS: Builds a cell address as text from a row and column number. Often combined with INDIRECT or used to report where something was found.
AREAS: Counts how many separate areas, such as ranges or single cells, are in a reference. Mainly used in advanced formulas with multi area references.
CHOOSE: Returns a value from a list based on a position number. Useful for turning numbers into labels such as day or quarter names.
CHOOSECOLS: Returns only the columns you ask for from a range, in the order you list them. Great for reordering or trimming down a table.
CHOOSEROWS: Returns only the rows you ask for from a range, in the order you list them. Handy for pulling specific records or the last row.
COLUMN: Returns the column number of a cell. Often used to create counters that change as a formula is copied across.
COLUMNS: Counts the number of columns in a range or array.
DROP: Removes a number of rows or columns from the start or end of a range. Useful for dropping headers or totals.
EXPAND: Enlarges an array to a set number of rows and columns, filling the new cells with a value you choose.
FIELDVALUE: Pulls a single field, such as population or price, out of a linked data type like Stocks or Geography. The cell must hold a linked data type.
FILTER: Returns only the rows or columns that meet your conditions, and the results update automatically. A formula version of AutoFilter.
FORMULATEXT: Shows the formula in a cell as text. Useful for documenting or checking how a spreadsheet works.
GETPIVOTDATA: Pulls a specific value out of a PivotTable. The reference keeps working even when the PivotTable layout changes.
GROUPBY: Groups your data by one or more fields and summarises the values, like a PivotTable built from a single formula. Results update automatically.
HLOOKUP: Looks for a value in the top row of a table and returns a value from the same column in a row you choose. The horizontal version of VLOOKUP.
HSTACK: Joins ranges or arrays side by side into one wider array.
HYPERLINK: Creates a clickable link to a web page, file or place in the workbook. You can show friendly text instead of the full address.
IMAGE: Puts a picture from a web address inside a cell. Useful for product images or logos in a list.
INDEX: Returns the value at a given row and column position in a range. Often paired with MATCH for flexible lookups.
INDIRECT: Turns text into a cell reference. Lets you build references from cell contents, such as a sheet name typed in a cell.
LOOKUP: Looks up a value in a sorted row or column and returns the matching value from another row or column. An older, simpler lookup.
MATCH: Finds the position of a value in a row or column. Usually combined with INDEX to return a matching value.
OFFSET: Returns a range that is a set number of rows and columns away from a starting cell, with an optional size. Used for moving or growing ranges.
PIVOTBY: Summarises data by groups across rows and columns, like a PivotTable built from a single formula. Results update automatically.
ROW: Returns the row number of a cell. Often used to create numbering that updates as rows are added.
ROWS: Counts the number of rows in a range or array.
RTD: Pulls real time data, such as live prices, from a program that supports COM automation. Needs a compatible RTD server installed.
SORT: Sorts the contents of a range or array and returns the sorted results, which update automatically.
SORTBY: Sorts a range based on the values in another range or array. Lets you sort by several columns, including ones you do not return.
TAKE: Returns a set number of rows or columns from the start or end of a range. Handy for top 10 lists or the latest entries.
TOCOL: Turns a range or array into a single column. Useful for listing everything in a grid in one list.
TOROW: Turns a range or array into a single row.
TRANSPOSE: Flips a range so rows become columns and columns become rows.
TRIMRANGE: Cuts empty rows and columns off the edges of a range, so whole column references only cover the cells in use. Keeps formulas fast and avoids long lists of zeros.
UNIQUE: Returns a list of unique values from a range, removing duplicates. Results update automatically as the data changes.
VLOOKUP: Looks for a value in the first column of a table and returns a value from the same row in a column you choose. The classic lookup formula.
VSTACK: Stacks ranges or arrays on top of each other into one taller array. Ideal for combining lists from several sheets.
WRAPCOLS: Splits a single row or column into several columns of a set length.
WRAPROWS: Splits a single row or column into several rows of a set length.
XLOOKUP: Looks for a value in one range and returns the matching item from another range. The modern replacement for VLOOKUP and HLOOKUP.
XMATCH: Finds the position of a value in a row or column. A more flexible version of MATCH that defaults to an exact match.

Logical functions

AND: Checks whether all conditions are true. Returns TRUE only if every test passes, often used inside IF.
BYCOL: Applies a calculation to each column of a range and returns one result per column. Useful for column totals or maximums in a single formula.
BYROW: Applies a calculation to each row of a range and returns one result per row. Handy for row totals that spill down automatically.
FALSE: Returns the logical value FALSE. You can also just type FALSE directly into a cell or formula.
IF: Tests a condition and returns one value if it is true and another if it is false. The most common way to make decisions in a spreadsheet.
IFERROR: Returns a value you choose if a formula gives an error, otherwise returns the formula's result. Used to hide errors such as #DIV/0! or #N/A.
IFNA: Returns a value you choose if a formula gives #N/A, otherwise returns the formula's result. Ideal for tidying up lookups that find no match.
IFS: Checks several conditions in order and returns the value for the first one that is true. A tidier alternative to nested IF formulas.
LAMBDA: Creates your own reusable function from a formula. Name it in Name Manager and you can call it like any built in function.
LET: Assigns names to values or calculations inside a formula so you can reuse them. Makes long formulas shorter, easier to read and faster.
MAKEARRAY: Builds an array of a given size by running a calculation for each row and column position. Good for creating grids and tables from a rule.
MAP: Runs a calculation on each value in one or more arrays and returns an array of the results. Lets you apply custom logic item by item.
NOT: Reverses a logical value, turning TRUE into FALSE and FALSE into TRUE. Useful when you want to test that something is not the case.
OR: Checks whether at least one condition is true. Returns FALSE only when every test fails.
REDUCE: Works through an array one value at a time, building up a single running result. Useful for custom totals and step by step calculations.
SCAN: Works through an array and returns every intermediate result of a running calculation. Ideal for running totals.
SWITCH: Compares one value against a list and returns the result for the first match. A neat replacement for nested IFs that test the same cell.
TRUE: Returns the logical value TRUE. You can also just type TRUE directly into a cell or formula.
XOR: Returns TRUE when an odd number of conditions are true. With two conditions it means one or the other, but not both.

Math and trigonometry functions

ABS: Returns the absolute value of a number, which is the number without its plus or minus sign. Useful for working out differences or variances regardless of direction.
ACOS: Returns the arccosine (inverse cosine) of a number, as an angle in radians between 0 and pi. Used in geometry and engineering to find an angle from a cosine value.
ACOSH: Returns the inverse hyperbolic cosine of a number. Mainly used in maths, physics and engineering calculations.
ACOT: Returns the arccotangent (inverse cotangent) of a number, as an angle in radians between 0 and pi.
ACOTH: Returns the inverse hyperbolic cotangent of a number. Used in specialist maths and engineering work.
AGGREGATE: Runs a summary calculation such as SUM, AVERAGE, MAX or LARGE while optionally ignoring errors, hidden rows and other subtotals. Handy when a range contains #N/A or #DIV/0! values that would break a normal SUM.
ARABIC: Converts a Roman numeral into a normal number. Useful for sorting or calculating with chapter, volume or year numbers written in Roman numerals.
ASIN: Returns the arcsine (inverse sine) of a number, as an angle in radians between -pi/2 and pi/2. Used to find an angle from a sine value.
ASINH: Returns the inverse hyperbolic sine of a number. Mainly used in maths, physics and engineering calculations.
ATAN: Returns the arctangent (inverse tangent) of a number, as an angle in radians between -pi/2 and pi/2. Used to find an angle from a slope or gradient.
ATAN2: Returns the angle in radians, between -pi and pi, from the x axis to a point with the given x and y coordinates. Useful for directions, bearings and angles between points.
ATANH: Returns the inverse hyperbolic tangent of a number. Used in statistics (for example the Fisher transformation) and engineering.
BASE: Converts a number into text in another number base, such as binary (base 2) or hexadecimal (base 16). Useful for codes, colour values and computing tasks.
CEILING.MATH: Rounds a number up to the nearest whole number or to the nearest multiple you choose. Handy for pricing, pack sizes and rounding up time.
CEILING.PRECISE: Rounds a number up to the nearest whole number or multiple, always towards positive infinity whatever the sign. Useful when you need consistent rounding up for negative values too.
COMBIN: Returns how many different groups you can choose from a set of items when order does not matter. Useful for lottery odds, team selections and probability.
COMBINA: Returns how many groups you can choose from a set of items when order does not matter and items can be picked more than once.
COS: Returns the cosine of an angle given in radians. Used in geometry, engineering and wave calculations.
COSH: Returns the hyperbolic cosine of a number. Used in maths and engineering, for example for hanging cable curves.
COT: Returns the cotangent of an angle given in radians. Used in trigonometry and engineering.
COTH: Returns the hyperbolic cotangent of a number. Used in specialist maths and engineering calculations.
CSC: Returns the cosecant (1 divided by the sine) of an angle given in radians. Used in trigonometry.
CSCH: Returns the hyperbolic cosecant of a number. Used in specialist maths and engineering calculations.
DECIMAL: Converts a number written in another base, such as binary or hexadecimal, into a normal decimal number. Useful for reading hex codes and binary values.
DEGREES: Converts an angle from radians into degrees. Useful because Excel's trigonometry functions return radians.
EVEN: Rounds a number up, away from zero, to the nearest even whole number. Useful when items come in pairs.
EXP: Returns e (about 2.718) raised to the power of a number. Used for growth, decay and continuous compound interest calculations.
FACT: Returns the factorial of a number, which is the number multiplied by every whole number below it down to 1. Used to count the ways items can be arranged.
FACTDOUBLE: Returns the double factorial of a number, multiplying it by every second whole number below it. Used in specialist maths and statistics.
FLOOR.MATH: Rounds a number down to the nearest whole number or to the nearest multiple you choose. Useful for grouping values into bands or rounding down times and prices.
FLOOR.PRECISE: Rounds a number down to the nearest whole number or multiple, always towards negative infinity whatever the sign. Useful when you need consistent rounding down for negative values too.
GCD: Returns the greatest common divisor, the largest whole number that divides exactly into all the numbers. Useful for simplifying ratios and fractions.
INT: Rounds a number down to the nearest whole number. Commonly used to strip the decimal part or the time from a date and time value.
ISO.CEILING: Rounds a number up to the nearest whole number or multiple, always towards positive infinity. It works the same as CEILING.PRECISE and follows the ISO standard.
LCM: Returns the least common multiple, the smallest whole number that all the numbers divide into exactly. Useful for adding fractions and lining up repeating schedules.
LN: Returns the natural logarithm of a number, using base e. Used for growth rates, finance and science calculations.
LOG: Returns the logarithm of a number to the base you choose. Useful for working out how many times something must multiply to reach a value.
LOG10: Returns the base 10 logarithm of a number. Used for scales such as decibels and pH and for comparing orders of magnitude.
MDETERM: Returns the determinant of a square matrix. Used in maths and engineering, for example to check whether a set of equations has a unique solution.
MINVERSE: Returns the inverse of a square matrix as a spilled array. Used with MMULT to solve sets of simultaneous equations.
MMULT: Returns the matrix product of two arrays as a spilled array. Useful for matrix maths and for weighted totals across rows.
MOD: Returns the remainder after dividing one number by another. Useful for finding odd or even numbers, every nth row and working with times.
MROUND: Rounds a number to the nearest multiple you choose, up or down. Useful for rounding prices to the nearest 5p or times to the nearest 15 minutes.
MULTINOMIAL: Returns the factorial of the sum of the numbers divided by the product of their factorials. Used in probability to count ways of splitting items into groups.
MUNIT: Returns an identity matrix of the size you choose, with 1s on the diagonal and 0s elsewhere. Used in matrix maths.
ODD: Rounds a number up, away from zero, to the nearest odd whole number.
PERCENTOF: Returns what share of a total a subset makes up, as a decimal. Designed for use with GROUPBY and PIVOTBY but also handy on its own for percentage of total.
PI: Returns the value of pi, about 3.14159265358979. Used for circle and angle calculations.
POWER: Raises a number to a power. Works the same as the ^ operator and is useful for growth, area and volume calculations.
PRODUCT: Multiplies all the numbers given together. Useful for multiplying a whole range in one go.
QUOTIENT: Returns the whole number part of a division and discards the remainder. Useful for working out how many full packs or boxes you can make.
RADIANS: Converts an angle from degrees into radians. Needed because Excel's trigonometry functions such as SIN and COS expect radians.
RAND: Returns a random decimal number between 0 and 1. It recalculates every time the sheet changes, so it is used for simulations, random sampling and shuffling lists.
RANDARRAY: Returns a spilled array of random numbers, with the size, range and type you choose. Useful for test data, random samples and shuffling.
RANDBETWEEN: Returns a random whole number between two numbers you choose. Useful for test data, prize draws and simulations.
ROMAN: Converts a normal number into Roman numerals as text. Useful for chapter numbers, years on certificates and list numbering.
ROUND: Rounds a number to a set number of decimal places. Used for prices, VAT and any figure that should be stored, not just shown, at fixed precision.
ROUNDDOWN: Rounds a number down, towards zero, to a set number of decimal places. Useful when you must never round up, such as with ages or allowances.
ROUNDUP: Rounds a number up, away from zero, to a set number of decimal places. Useful for shipping charges, materials and anything you must not underestimate.
SEC: Returns the secant (1 divided by the cosine) of an angle given in radians. Used in trigonometry.
SECH: Returns the hyperbolic secant of a number. Used in specialist maths and engineering calculations.
SEQUENCE: Returns a spilled list of sequential numbers. Useful for numbering rows, building date lists and creating test data.
SERIESSUM: Returns the sum of a power series. Used in maths and engineering to approximate functions.
SIGN: Returns 1 if a number is positive, 0 if it is zero and -1 if it is negative. Useful for flagging increases and decreases.
SIN: Returns the sine of an angle given in radians. Used in geometry, engineering and wave calculations.
SINH: Returns the hyperbolic sine of a number. Used in maths and engineering calculations.
SQRT: Returns the positive square root of a number. Useful for geometry, distances and statistics.
SQRTPI: Returns the square root of a number multiplied by pi. Used in statistics and engineering formulas.
SUBTOTAL: Returns a summary such as a sum, average or count for a list, ignoring other SUBTOTAL results and, if you choose, hidden rows. Ideal for totals that update when you filter a table.
SUM: Adds numbers together. The most used Excel function, for totalling ranges, individual cells or values.
SUMIF: Adds up values that meet one condition. Useful for totals per customer, product, category or date.
SUMIFS: Adds up values that meet several conditions at once. Useful for totals by region and month, product and salesperson, and so on.
SUMPRODUCT: Multiplies matching items in arrays and adds up the results. Useful for totals of quantity times price, weighted averages and flexible conditional sums.
SUMSQ: Returns the sum of the squares of the numbers given. Used in statistics and geometry.
SUMX2MY2: Returns the sum of the differences of squares of matching values in two arrays. Used in statistical calculations.
SUMX2PY2: Returns the sum of the sums of squares of matching values in two arrays. Used in statistical calculations.
SUMXMY2: Returns the sum of the squared differences between matching values in two arrays. Useful for measuring how far forecasts are from actual figures.
TAN: Returns the tangent of an angle given in radians. Used in geometry, for example to work out heights and slopes.
TANH: Returns the hyperbolic tangent of a number. Used in maths, statistics and engineering.
TRUNC: Cuts a number down to a set number of decimal places without rounding. Useful for removing decimals or the time part of a date.

Statistical functions

AVEDEV: Returns the average of how far each value sits from the mean. Useful as a simple measure of how spread out your data is.
AVERAGE: Returns the average (arithmetic mean) of a set of numbers. Used for things like average sales, scores or prices.
AVERAGEA: Returns the average of values, counting TRUE as 1 and FALSE and text in ranges as 0. Useful when blank answers or text should count as zero rather than be skipped.
AVERAGEIF: Returns the average of the cells that meet one condition. Used for things like the average sale for one region or the average of values above a target.
AVERAGEIFS: Returns the average of cells that meet several conditions at once. Used for things like average sales for one product in one month.
BETA.DIST: Returns the beta distribution, often used to model proportions or percentages such as the share of time spent on a task.
BETA.INV: Returns the inverse of the cumulative beta distribution, the value at which a given probability is reached.
BINOM.DIST: Returns the probability of getting a certain number of successes in a fixed number of tries. Used for things like the chance of a given number of faulty items in a batch.
BINOM.DIST.RANGE: Returns the probability that the number of successes falls within a range. Useful for questions like the chance of between 3 and 5 sales from 10 calls.
BINOM.INV: Returns the smallest number of successes for which the cumulative binomial probability reaches a given level. Often used in quality control to set acceptance limits.
CHISQ.DIST: Returns the left tailed chi squared distribution. Used in statistical testing of how well observed data fits expectations.
CHISQ.DIST.RT: Returns the right tailed probability of the chi squared distribution. Used to get a p value from a chi squared test statistic.
CHISQ.INV: Returns the inverse of the left tailed chi squared distribution, the value below which a given probability lies.
CHISQ.INV.RT: Returns the inverse of the right tailed chi squared distribution. Used to find the critical value for a chi squared test.
CHISQ.TEST: Returns the p value of a chi squared test comparing observed counts with expected counts. Used to check if differences, such as in survey answers, are likely due to chance.
CONFIDENCE.NORM: Returns the margin of error for a confidence interval around a mean, using the normal distribution.
CONFIDENCE.T: Returns the margin of error for a confidence interval around a mean, using the Student's t distribution. Better than CONFIDENCE.NORM for small samples.
CORREL: Returns the correlation coefficient between two sets of values, from minus 1 to 1. Used to see how strongly two things move together, such as advertising spend and sales.
COUNT: Counts how many cells contain numbers. Used to count entries such as orders, scores or dates.
COUNTA: Counts how many cells are not empty. Used to count names, entries or any filled in cells.
COUNTBLANK: Counts the number of empty cells in a range. Useful for finding missing data or unanswered questions.
COUNTIF: Counts the cells that meet one condition. Used for things like counting orders from one region or scores above a pass mark.
COUNTIFS: Counts the rows that meet several conditions at once. Used for things like counting orders for one product in one month.
COVARIANCE.P: Returns the population covariance, the average of the products of deviations for two sets of data. Shows whether two things tend to rise and fall together.
COVARIANCE.S: Returns the sample covariance for two sets of data. Use it when your data is a sample rather than the whole population.
DEVSQ: Returns the sum of squared deviations from the mean. Used as a building block in variance and regression calculations.
EXPON.DIST: Returns the exponential distribution. Used to model waiting times, such as the time between customer calls.
F.DIST: Returns the left tailed F probability distribution, used to compare how spread out two sets of data are.
F.DIST.RT: Returns the right tailed F probability distribution. Used to get the p value of an F test, for example in ANOVA.
F.INV: Returns the inverse of the left tailed F distribution, the value below which a given probability lies.
F.INV.RT: Returns the inverse of the right tailed F distribution. Used to find the critical value for an F test or ANOVA.
F.TEST: Returns the two tailed p value of an F test, which checks whether two sets of data have significantly different variances.
FISHER: Returns the Fisher transformation of a correlation value. Used when testing or averaging correlation coefficients.
FISHERINV: Returns the inverse of the Fisher transformation, turning a transformed value back into a correlation.
FORECAST: Predicts a future value along a straight line trend based on existing data. Used for simple sales or demand forecasts.
FORECAST.ETS: Predicts a future value using exponential smoothing, which can pick up seasonal patterns. Used for forecasting sales or demand that rises and falls through the year.
FORECAST.ETS.CONFINT: Returns the confidence interval for a value predicted with FORECAST.ETS. Shows how far above or below the forecast the true value may fall.
FORECAST.ETS.SEASONALITY: Returns the length of the repeating seasonal pattern Excel detects in a time series. Useful for checking what cycle a forecast is based on.
FORECAST.ETS.STAT: Returns a statistic about the exponential smoothing model behind FORECAST.ETS, such as its smoothing parameters or error measures.
FORECAST.LINEAR: Predicts a future value along a straight line trend based on existing data. Used for simple sales, cost or demand forecasts.
FREQUENCY: Counts how many values fall into each range (bin) and returns the counts as a vertical array. Used to build histograms, such as grouping ages or scores into bands.
GAMMA: Returns the gamma function value, which extends the factorial to non whole numbers. Mainly used in statistics and engineering formulas.
GAMMA.DIST: Returns the gamma distribution, used to model waiting times and skewed data such as insurance claims.
GAMMA.INV: Returns the inverse of the cumulative gamma distribution, the value at which a given probability is reached.
GAMMALN: Returns the natural logarithm of the gamma function. Used in statistical calculations with large values to avoid overflow.
GAMMALN.PRECISE: Returns the natural logarithm of the gamma function. Used in statistical calculations with large values to avoid overflow.
GAUSS: Returns the probability that a standard normal value falls between the mean and z standard deviations above it.
GEOMEAN: Returns the geometric mean of positive numbers. Used for average growth rates, such as average yearly growth in sales or investments.
GROWTH: Predicts values along an exponential growth curve fitted to your data. Used for forecasting things that grow by a percentage, such as users or sales.
HARMEAN: Returns the harmonic mean of positive numbers. Used for averaging rates, such as average speed over equal distances.
HYPGEOM.DIST: Returns the probability of a number of successes when sampling without replacement. Used for things like the chance of drawing a number of faulty items from a batch.
INTERCEPT: Returns the point where a line of best fit crosses the y axis. Used with SLOPE to describe a linear trend, such as fixed costs in a cost model.
KURT: Returns the kurtosis of a data set, which shows how heavy the tails are compared with a normal distribution.
LARGE: Returns the k th largest value in a data set. Used to find top values, such as the second highest sale or top three scores.
LINEST: Calculates the line of best fit for your data and returns its slope and intercept, plus optional statistics. Used for regression analysis and trend modelling.
LOGEST: Fits an exponential growth curve (y = b*m^x) to your data and returns the growth factor m and the starting value b. Useful for modelling compound growth such as sales or user numbers.
LOGNORM.DIST: Returns the lognormal distribution of x, where the natural log of x is normally distributed. Used for values that cannot go below zero and are skewed, such as prices or incomes.
LOGNORM.INV: Returns the value of x for a given probability in a lognormal distribution. It is the inverse of LOGNORM.DIST, useful for finding thresholds in skewed data.
MAX: Returns the largest number in a set of values. Use it to find the highest sale, score or price.
MAXA: Returns the largest value in a list, counting TRUE as 1 and text or FALSE as 0. Useful when your data mixes numbers with logical values.
MAXIFS: Returns the largest value among cells that meet one or more conditions. For example, find the biggest order from a particular region.
MEDIAN: Returns the middle value of a set of numbers. Useful for typical values such as house prices or salaries where a few extreme values would distort the average.
MIN: Returns the smallest number in a set of values. Use it to find the lowest price, score or stock level.
MINA: Returns the smallest value in a list, counting TRUE as 1 and text or FALSE as 0. Useful when your data mixes numbers with logical values.
MINIFS: Returns the smallest value among cells that meet one or more conditions. For example, find the cheapest price from a particular supplier.
MODE.MULT: Returns a vertical list of all the most frequently occurring values in a set of data. Use it when more than one value could be the most common.
MODE.SNGL: Returns the most frequently occurring value in a set of numbers. Useful for finding the most common size, score or quantity.
NEGBINOM.DIST: Returns the probability of a given number of failures before a set number of successes. Used, for example, to estimate how many calls are needed before closing a set number of sales.
NORM.DIST: Returns the normal distribution for a value, given a mean and standard deviation. Used to find the probability that a measurement falls below a certain value.
NORM.INV: Returns the value below which a given probability falls in a normal distribution. Useful for setting thresholds, such as the score that 97.5% of people fall below.
NORM.S.DIST: Returns the standard normal distribution (mean 0, standard deviation 1) for a z score. Used to turn a z score into a probability.
NORM.S.INV: Returns the z score for a given probability in the standard normal distribution. Useful for confidence intervals and safety stock calculations.
PEARSON: Returns the Pearson correlation coefficient, between -1 and 1, showing how strongly two sets of data move together. Useful for checking if advertising spend relates to sales.
PERCENTILE.EXC: Returns the k-th percentile of a set of values, where k is strictly between 0 and 1. Used to find cut off points such as the 90th percentile of response times.
PERCENTILE.INC: Returns the k-th percentile of a set of values, where k is from 0 to 1 inclusive. Used to find cut off points such as the top 10% of sales.
PERCENTRANK.EXC: Returns the rank of a value as a percentage of the data set, excluding 0 and 1. Useful to see where one result stands relative to the rest.
PERCENTRANK.INC: Returns the rank of a value as a percentage of the data set, from 0 to 1 inclusive. Useful to see where a score or sale stands relative to the rest.
PERMUT: Returns the number of ways to choose and order a set of items from a larger group, without repeats. Useful for counting possible codes or race finishing orders.
PERMUTATIONA: Returns the number of ordered arrangements of items chosen from a group when items can be repeated. Useful for counting PIN codes or passwords.
PHI: Returns the height of the standard normal bell curve at a value. Mainly used in statistics and probability modelling.
POISSON.DIST: Returns the Poisson distribution, used to predict how many times an event happens in a fixed period. For example, the chance of a given number of customer calls per hour.
PROB: Returns the probability that values fall between two limits, given a list of values and their probabilities. Useful for simple risk or outcome tables.
QUARTILE.EXC: Returns the quartile of a data set using the exclusive method. Useful for splitting data into four groups, such as low and high performers.
QUARTILE.INC: Returns the quartile of a data set, including the minimum and maximum. Useful for splitting data into four groups and building box plots.
RANK.AVG: Returns the rank of a number in a list, giving tied values the average of their ranks. Useful for fair league tables and scoring.
RANK.EQ: Returns the rank of a number in a list, giving tied values the same top rank. Use it to create league tables or rank staff by sales.
RSQ: Returns the R squared value of a straight line fit, showing how much of the change in y is explained by x. Useful for judging how reliable a trend line is.
SKEW: Returns the skewness of a sample, showing whether the data leans to the left or right of the average. Positive means a longer tail of high values.
SKEW.P: Returns the skewness of a whole population, showing whether the data leans to the left or right of the average.
SLOPE: Returns the slope of the best fit straight line through your data. Shows how much y changes for each unit increase in x, such as extra sales per pound spent.
SMALL: Returns the k-th smallest value in a set of data. Useful for finding the second lowest price or the three fastest times.
STANDARDIZE: Returns a z score showing how many standard deviations a value is from the mean. Useful for comparing results on different scales.
STDEV.P: Returns the standard deviation of a whole population, showing how spread out the values are. Use it when your data includes every item, not a sample.
STDEV.S: Returns the standard deviation of a sample, showing how spread out the values are. This is the usual choice when your data is a sample of a bigger group.
STDEVA: Returns the sample standard deviation, counting TRUE as 1 and text or FALSE as 0. Useful when data includes logical values you want included.
STDEVPA: Returns the population standard deviation, counting TRUE as 1 and text or FALSE as 0.
STEYX: Returns the standard error of the predicted y values in a straight line fit. Shows how far actual values typically sit from the trend line.
T.DIST: Returns the left tailed Student's t distribution. Used in hypothesis testing with small samples.
T.DIST.2T: Returns the two tailed Student's t distribution probability. Used to get a p value from a t statistic.
T.DIST.RT: Returns the right tailed Student's t distribution probability. Used for one tailed tests.
T.INV: Returns the left tailed inverse of the Student's t distribution. Gives the t value for a given probability.
T.INV.2T: Returns the two tailed inverse of the Student's t distribution. Commonly used to find the critical t value for confidence intervals.
T.TEST: Returns the p value from a Student's t test, showing whether two sets of data have significantly different averages. Useful for comparing before and after results.
TREND: Returns values along a straight line trend fitted to your data. Useful for forecasting future sales from past figures.
TRIMMEAN: Returns the average after removing a percentage of the highest and lowest values. Useful for averages that are not distorted by outliers.
VAR.P: Returns the variance of a whole population, showing how spread out the values are. Use it when your data includes every item.
VAR.S: Returns the variance of a sample, showing how spread out the values are. The usual choice when your data is a sample of a bigger group.
VARA: Returns the sample variance, counting TRUE as 1 and text or FALSE as 0.
VARPA: Returns the population variance, counting TRUE as 1 and text or FALSE as 0.
WEIBULL.DIST: Returns the Weibull distribution, often used in reliability analysis to model how long products last before failing.
Z.TEST: Returns the one tailed p value of a z test, showing whether a sample average is significantly higher than a given value.

Text functions

ARRAYTOTEXT: Turns a range or array of values into a single piece of text. Useful for showing the contents of a range in one cell or building a readable list.
ASC: Changes full width (double byte) characters into half width (single byte) characters. Mainly used with Japanese, Chinese and Korean text.
BAHTTEXT: Converts a number into Thai text with the word for baht added. Used for writing amounts in words on Thai invoices and cheques.
CHAR: Returns the character for a given code number. Often used to add line breaks, CHAR(10), or special symbols into text.
CLEAN: Removes non printing characters, such as line breaks, from text. Handy for tidying data copied from other systems.
CODE: Returns the numeric code of the first character in a piece of text. Useful for finding hidden or odd characters.
CONCAT: Joins several pieces of text, or whole ranges, into one piece of text. The modern replacement for CONCATENATE.
DBCS: Changes half width (single byte) characters into full width (double byte) characters. Used with Japanese, Chinese and Korean text.
DETECTLANGUAGE: Identifies the language of a piece of text and returns its language code. Useful for sorting customer messages or reviews by language.
DOLLAR: Turns a number into text formatted as currency, using your computer's currency symbol. Useful for showing prices inside sentences.
EXACT: Checks whether two pieces of text are exactly the same, including upper and lower case. Returns TRUE or FALSE.
FIND: Finds the position of one piece of text inside another, matching upper and lower case. Often used with LEFT, MID or RIGHT to pull out part of text.
FINDB: Finds the position of text inside other text, counting in bytes. Intended for double byte languages such as Japanese.
FIXED: Rounds a number to a set number of decimal places and returns it as text, with or without thousands separators. Useful for showing tidy numbers inside sentences.
LEFT: Returns a set number of characters from the start of a piece of text. Useful for pulling out codes, initials or prefixes.
LEFTB: Returns characters from the start of text based on a number of bytes. Intended for double byte languages such as Japanese.
LEN: Counts the number of characters in a piece of text, including spaces. Useful for checking lengths of codes, phone numbers or titles.
LENB: Counts the number of bytes in a piece of text. Intended for double byte languages such as Japanese.
LOWER: Converts all letters in a piece of text to lower case. Useful for tidying email addresses and names.
MID: Returns a set number of characters from the middle of a piece of text, starting at a position you choose. Useful for extracting part of a code or reference.
MIDB: Returns characters from the middle of text based on a number of bytes. Intended for double byte languages such as Japanese.
NUMBERVALUE: Converts text to a number, letting you say which characters are used as the decimal and thousands separators. Ideal for numbers in European formats.
PHONETIC: Extracts the phonetic (furigana) reading stored with Japanese text in a cell. Only useful when Japanese input has been used.
PROPER: Capitalises the first letter of each word and makes the rest lower case. Useful for tidying names and addresses.
REGEXEXTRACT: Pulls out the part of text that matches a regular expression pattern. Great for extracting order numbers, postcodes or email addresses from longer text.
REGEXREPLACE: Replaces parts of text that match a regular expression pattern with something else. Useful for cleaning phone numbers, codes and messy imported data.
REGEXTEST: Checks whether text matches a regular expression pattern and returns TRUE or FALSE. Useful for validating postcodes, emails and codes.
REPLACE: Replaces part of a piece of text, chosen by position, with different text. Useful for changing a fixed part of a code.
REPLACEB: Replaces part of text, chosen by byte position, with different text. Intended for double byte languages such as Japanese.
REPT: Repeats text a given number of times. Useful for padding codes or drawing simple in cell bar charts.
RIGHT: Returns a set number of characters from the end of a piece of text. Useful for getting suffixes, last digits or file extensions.
RIGHTB: Returns characters from the end of text based on a number of bytes. Intended for double byte languages such as Japanese.
SEARCH: Finds the position of one piece of text inside another, ignoring upper and lower case. Supports wildcards and is often used to check whether a cell contains a word.
SEARCHB: Finds the position of text inside other text, ignoring case and counting in bytes. Intended for double byte languages such as Japanese.
SUBSTITUTE: Replaces specific text with new text wherever it appears, or only a chosen occurrence. Useful for swapping characters, removing words or cleaning data.
T: Returns the value if it is text, or empty text if it is not. Mainly used for compatibility with other spreadsheet programs.
TEXT: Converts a number or date into text using a format you choose. Useful for showing dates, currency or percentages inside sentences.
TEXTAFTER: Returns the text that comes after a given character or word. Ideal for splitting emails, codes and names.
TEXTBEFORE: Returns the text that comes before a given character or word. Ideal for getting first names, usernames or code prefixes.
TEXTJOIN: Joins text from several cells or ranges with a separator of your choice, optionally skipping blanks. Great for building comma separated lists.
TEXTSPLIT: Splits text into separate cells across columns or down rows using a delimiter. A formula version of Text to Columns.
TRANSLATE: Translates text from one language to another using Microsoft's online translation service. Useful for product descriptions and messages in other languages.
TRIM: Removes extra spaces from text, leaving single spaces between words. Essential for cleaning data pasted from other systems.
UNICHAR: Returns the Unicode character for a given number. Useful for adding symbols, arrows or emoji in formulas.
UNICODE: Returns the Unicode number of the first character in a piece of text. Useful for identifying unusual or hidden characters.
UPPER: Converts all letters in a piece of text to capitals. Useful for postcodes, codes and references.
VALUE: Converts text that looks like a number into a real number. Fixes numbers stored as text so they can be added up.
VALUETOTEXT: Converts any value into text. Useful when you need a number, date or result shown as plain text.

Date and time functions

DATE: Builds a date from separate year, month and day numbers. Useful when the parts of a date are in different cells or need calculating.
DATEDIF: Calculates the difference between two dates in whole years, months or days. Commonly used to work out ages and lengths of service.
DATEVALUE: Converts a date stored as text into a real Excel date serial number. Useful for fixing imported dates that will not sort or calculate.
DAY: Returns the day of the month (1 to 31) from a date.
DAYS: Returns the number of days between two dates. Useful for counting days until a deadline or since an order.
DAYS360: Returns the number of days between two dates based on a 360 day year (twelve 30 day months). Used in some accounting and interest calculations.
EDATE: Returns the date that is a given number of months before or after a start date. Useful for renewal, expiry and due dates.
EOMONTH: Returns the last day of the month a given number of months before or after a date. Useful for month end deadlines and payment terms.
HOUR: Returns the hour (0 to 23) from a time or date and time.
ISOWEEKNUM: Returns the ISO 8601 week number of the year for a date, where weeks start on Monday. This is the week numbering used in the UK and Europe.
MINUTE: Returns the minutes (0 to 59) from a time.
MONTH: Returns the month number (1 to 12) from a date.
NETWORKDAYS: Counts the working days (Monday to Friday) between two dates, including both ends, and can skip holidays. Useful for project time and staff leave.
NETWORKDAYS.INTL: Counts the working days between two dates with your own choice of weekend days, and can skip holidays. Useful for shift patterns or non Saturday and Sunday weekends.
NOW: Returns the current date and time. It updates every time the sheet recalculates.
SECOND: Returns the seconds (0 to 59) from a time.
TIME: Builds a time from separate hour, minute and second numbers. Returns a decimal fraction of a day.
TIMEVALUE: Converts a time stored as text into a real Excel time (a fraction of a day). Useful for fixing imported times.
TODAY: Returns today's date. It updates each time the workbook recalculates.
WEEKDAY: Returns the day of the week for a date as a number. Useful for spotting weekends or grouping by day.
WEEKNUM: Returns the week number of the year for a date. Week 1 is the week containing 1 January.
WORKDAY: Returns the date a given number of working days before or after a start date, skipping weekends and holidays. Useful for delivery and due dates.
WORKDAY.INTL: Returns the date a given number of working days from a start date, using your own choice of weekend days.
YEAR: Returns the year from a date as a four digit number.
YEARFRAC: Returns the fraction of a year between two dates. Useful for pro rata calculations, accruals and precise ages.

Financial functions

ACCRINT: Works out the interest that has built up on a bond that pays regular interest. Used to calculate accrued interest a buyer owes the seller.
ACCRINTM: Works out the interest built up on a security that pays all its interest in one go at maturity.
AMORDEGRC: Calculates depreciation for each accounting period using the French accounting system, with a coefficient based on the asset's life. Mainly used by businesses following French accounting rules.
AMORLINC: Calculates straight line depreciation for each accounting period using the French accounting system, with a part period first year.
COUPDAYBS: Returns the number of days from the start of the current coupon period to the settlement date. Useful for working out accrued bond interest.
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, as a date serial number.
COUPNUM: Counts the coupon payments still due between the settlement date and the maturity date.
COUPPCD: Returns the coupon date on or before the settlement date, as a date serial number.
CUMIPMT: Adds up the interest paid on a loan between two payment periods. Handy for seeing how much interest you pay in a given year of a loan or mortgage.
CUMPRINC: Adds up the capital (principal) repaid on a loan between two payment periods.
DB: Calculates depreciation for a period using the fixed declining balance method, where the asset loses a fixed percentage of its value each period.
DDB: Calculates depreciation for a period using the double declining balance method, which writes off more value in the early years.
DISC: Returns the discount rate of a security such as a Treasury bill, based on its price and redemption value.
DOLLARDE: Converts a price written as a whole number and fraction (as in some bond and share quotes) into a normal decimal number.
DOLLARFR: Converts a decimal price into a fractional price, as used in some bond and share quotes.
DURATION: Returns the Macaulay duration of a bond, the weighted average time in years until its cash flows are received. Used to measure how sensitive a bond is to interest rate changes.
EFFECT: Converts a nominal annual interest rate into the effective annual rate (AER) once compounding is taken into account.
FV: Works out the future value of an investment with regular payments and a fixed interest rate. Good for savings goals.
FVSCHEDULE: Works out the future value of a starting amount after a series of different interest rates are applied in turn.
INTRATE: Returns the annual interest rate for a fully invested security that pays no coupons.
IPMT: Returns the interest part of one loan payment in a given period. Useful for splitting mortgage payments into interest and capital.
IRR: Returns the internal rate of return for a series of regular cash flows. Used to judge whether an investment or project is worthwhile.
ISPMT: Returns the interest paid in a period for a loan where the capital is repaid in equal amounts. Kept for compatibility with Lotus 1 2 3.
MDURATION: Returns the modified duration of a bond, which estimates the percentage change in its price for a 1% change in yield.
MIRR: Returns the modified internal rate of return, which allows for the cost of borrowing and the rate earned on reinvested cash.
NOMINAL: Converts an effective annual rate (AER) into the nominal annual rate for a given number of compounding periods.
NPER: Works out how many payment periods are needed to pay off a loan or reach a savings target.
NPV: Returns the net present value of future cash flows using a discount rate. Used to decide if an investment is worth more than it costs.
ODDFPRICE: Returns the price per 100 face value of a bond whose first coupon period is shorter or longer than normal.
ODDFYIELD: Returns the yield of a bond whose first coupon period is shorter or longer than normal.
ODDLPRICE: Returns the price per 100 face value of a bond whose last coupon period is shorter or longer than normal.
ODDLYIELD: Returns the yield of a bond whose last coupon period is shorter or longer than normal.
PDURATION: Works out how many periods an investment needs to grow to a target value at a fixed interest rate.
PMT: Calculates the regular payment for a loan or mortgage with a fixed interest rate. Also works for saving towards a target.
PPMT: Returns the capital (principal) part of one loan payment in a given period.
PRICE: Returns the price per 100 face value of a bond that pays regular interest, given its yield.
PRICEDISC: Returns the price per 100 face value of a discounted security such as a Treasury bill.
PRICEMAT: Returns the price per 100 face value of a security that pays all its interest at maturity.
PV: Returns the present value of a series of future payments, such as how much you could borrow for a given monthly payment.
RATE: Works out the interest rate per period of a loan or investment from its payments.
RECEIVED: Returns the amount received at maturity for a fully invested discounted security.
RRI: Returns the equivalent steady growth rate per period for an investment to grow from a start value to an end value. Often used for compound annual growth rate (CAGR).
SLN: Returns straight line depreciation for one period, spreading the cost evenly over the asset's life.
STOCKHISTORY: Returns historical share price data for a stock, fund or currency pair as a spilling table. Needs a Microsoft 365 subscription and an internet connection.
SYD: Returns depreciation for a period using the sum of years digits method, which front loads the write down.
TBILLEQ: Returns the bond equivalent yield of a Treasury bill, so it can be compared with coupon bonds.
TBILLPRICE: Returns the price per 100 face value of a Treasury bill.
TBILLYIELD: Returns the yield of a Treasury bill from its price.
VDB: Returns declining balance depreciation for any period, including part periods, and switches to straight line when that gives more.
XIRR: Returns the annual rate of return for cash flows that happen on irregular dates. Ideal for real investments with uneven payment dates.
XNPV: Returns the net present value of cash flows that happen on irregular dates.
YIELD: Returns the yield of a bond that pays regular interest, given its price.
YIELDDISC: Returns the annual yield of a discounted security such as a Treasury bill.
YIELDMAT: Returns the annual yield of a security that pays all its interest at maturity.

Information functions

CELL: Returns information about a cell, such as its address, format, contents or the file name. Useful for building dynamic labels.
ERROR.TYPE: Returns a number identifying which type of error a value is. Useful for showing friendly messages for different errors.
INFO: Returns information about the current operating environment, such as the Excel version or the operating system.
ISBLANK: Checks whether a cell is completely empty and returns TRUE or FALSE.
ISERR: Checks whether a value is any error except #N/A and returns TRUE or FALSE.
ISERROR: Checks whether a value is any error, including #N/A, and returns TRUE or FALSE.
ISEVEN: Checks whether a number is even and returns TRUE or FALSE.
ISFORMULA: Checks whether a cell contains a formula and returns TRUE or FALSE. Useful for spotting hard typed values in a calculated column.
ISLOGICAL: Checks whether a value is TRUE or FALSE (a logical value).
ISNA: Checks whether a value is the #N/A error and returns TRUE or FALSE. Often used to test whether a lookup found anything.
ISNONTEXT: Checks whether a value is anything other than text, including blank cells, and returns TRUE or FALSE.
ISNUMBER: Checks whether a value is a number and returns TRUE or FALSE. Commonly paired with SEARCH to test if text contains a word.
ISODD: Checks whether a number is odd and returns TRUE or FALSE.
ISOMITTED: Checks whether an argument was left out when calling a LAMBDA function, so you can supply a default value.
ISREF: Checks whether a value is a cell or range reference and returns TRUE or FALSE.
ISTEXT: Checks whether a value is text and returns TRUE or FALSE. Useful for finding numbers stored as text.
N: Converts a value to a number: numbers stay the same, dates become serial numbers, TRUE becomes 1, and text or FALSE becomes 0.
NA: Returns the #N/A error value, meaning no value is available. Useful for marking missing data so charts skip it.
SHEET: Returns the position number of a sheet in the workbook. Without an argument it gives the number of the current sheet.
SHEETS: Returns the number of sheets in a reference. Without an argument it counts all sheets in the workbook.
TYPE: Returns a number showing what kind of value something is: number, text, logical, error or array.

Database functions

DAVERAGE: Averages the values in one column of a table, only for rows that match conditions you set in a criteria range. Useful for quick conditional averages on a list.
DCOUNT: Counts the cells containing numbers in one column of a table, only for rows that match your criteria. Useful for counting records that meet several conditions.
DCOUNTA: Counts the non blank cells in one column of a table, only for rows that match your criteria. Works with text as well as numbers.
DGET: Returns a single value from one column of a table for the one row that matches your criteria. Useful for pulling a unique record.
DMAX: Returns the largest number in one column of a table, only for rows that match your criteria.
DMIN: Returns the smallest number in one column of a table, only for rows that match your criteria.
DPRODUCT: Multiplies together the numbers in one column of a table, only for rows that match your criteria.
DSTDEV: Estimates the standard deviation of one column of a table, treating the matching rows as a sample. Useful for measuring spread in a filtered list.
DSTDEVP: Calculates the standard deviation of one column of a table, treating the matching rows as the entire population.
DSUM: Adds up the numbers in one column of a table, only for rows that match your criteria. Handy for totals with complex AND or OR conditions.
DVAR: Estimates the variance of one column of a table, treating the matching rows as a sample.
DVARP: Calculates the variance of one column of a table, treating the matching rows as the entire population.

Engineering functions

BESSELI: Returns the modified Bessel function In(x). Used in engineering and physics, for example in heat flow and signal processing calculations.
BESSELJ: Returns the Bessel function Jn(x) of the first kind. Used for vibration, wave and antenna calculations.
BESSELK: Returns the modified Bessel function Kn(x). Used in engineering problems such as heat loss from fins and pipes.
BESSELY: Returns the Bessel function Yn(x) of the second kind, also called the Weber or Neumann function. Used in wave and vibration calculations.
BIN2DEC: Converts a binary number (base 2) to an ordinary decimal number.
BIN2HEX: Converts a binary number to hexadecimal (base 16).
BIN2OCT: Converts a binary number to octal (base 8).
BITAND: Compares two numbers bit by bit and returns a number with only the bits that are set in both. Useful for checking flags or permission codes.
BITLSHIFT: Shifts the bits of a number to the left, which multiplies it by 2 for each place shifted.
BITOR: Compares two numbers bit by bit and returns a number with the bits that are set in either. Useful for combining flags.
BITRSHIFT: Shifts the bits of a number to the right, which divides it by 2 for each place and drops any remainder.
BITXOR: Compares two numbers bit by bit and returns a number with the bits that are set in one but not both.
COMPLEX: Builds a complex number such as 3+4i from its real and imaginary parts, ready for use with the other IM functions.
CONVERT: Converts a measurement from one unit to another, such as miles to kilometres, pounds to kilograms or Fahrenheit to Celsius.
DEC2BIN: Converts a decimal number to binary (base 2).
DEC2HEX: Converts a decimal number to hexadecimal (base 16).
DEC2OCT: Converts a decimal number to octal (base 8).
DELTA: Tests whether two numbers are equal, returning 1 if they are and 0 if not. Handy for counting matches with SUM.
ERF: Returns the error function between two limits. Used in statistics and engineering, for example in probability and diffusion calculations.
ERF.PRECISE: Returns the error function integrated from 0 to x. Used in statistics and engineering calculations.
ERFC: Returns the complementary error function, which is 1 minus ERF(x). Used in statistics and engineering.
ERFC.PRECISE: Returns the complementary error function integrated from x to infinity, which is 1 minus ERF(x).
GESTEP: Returns 1 if a number is greater than or equal to a threshold, otherwise 0. Handy for counting values above a target with SUM.
HEX2BIN: Converts a hexadecimal number to binary (base 2).
HEX2DEC: Converts a hexadecimal number to an ordinary decimal number.
HEX2OCT: Converts a hexadecimal number to octal (base 8).
IMABS: Returns the absolute value (modulus) of a complex number, which is its distance from zero.
IMAGINARY: Returns the imaginary part of a complex number as an ordinary number.
IMARGUMENT: Returns the argument (angle) of a complex number in radians.
IMCONJUGATE: Returns the complex conjugate of a complex number, which flips the sign of the imaginary part.
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 one complex number by another.
IMEXP: Returns the exponential (e raised to the power) of a complex number.
IMLN: Returns the natural logarithm of a complex number.
IMLOG10: Returns the base 10 logarithm of a complex number.
IMLOG2: Returns the base 2 logarithm of a complex number.
IMPOWER: Raises a complex number to a power.
IMPRODUCT: Multiplies complex numbers together.
IMREAL: Returns the real part of a complex number as an ordinary 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 one complex number from another.
IMSUM: Adds complex numbers together.
IMTAN: Returns the tangent of a complex number.
OCT2BIN: Converts an octal number (base 8) to binary.
OCT2DEC: Converts an octal number (base 8) to an ordinary decimal number.
OCT2HEX: Converts an octal number (base 8) to hexadecimal.

Web functions

ENCODEURL: Converts text into a URL safe form, replacing spaces and special characters with codes such as %20. Useful for building web links.
FILTERXML: Returns specific data from XML text using an XPath query. Often used with WEBSERVICE or to split text.
WEBSERVICE: Returns data from a web address, such as an API, straight into a cell.

Cube functions

CUBEKPIMEMBER: Returns a key performance indicator (KPI) property from an OLAP cube or data model, such as its value, goal or status. Needs a workbook data model (Power Pivot) or an OLAP cube connection.
CUBEMEMBER: Returns a member, such as a product or year, from an OLAP cube or data model. Needs a workbook data model (Power Pivot) or an OLAP cube connection.
CUBEMEMBERPROPERTY: Returns a property of a member in an OLAP cube, such as a customer's city. Needs a workbook data model (Power Pivot) or an OLAP cube connection.
CUBERANKEDMEMBER: Returns the member at a given position in a set from an OLAP cube or data model, for example the top selling product. Needs a workbook data model (Power Pivot) or an OLAP cube connection.
CUBESET: Defines a set of members, such as a list of products, from an OLAP cube or data model so other cube functions can use it. Needs a workbook data model (Power Pivot) or an OLAP cube connection.
CUBESETCOUNT: Returns the number of items in a set created by CUBESET. Needs a workbook data model (Power Pivot) or an OLAP cube connection.
CUBEVALUE: Returns an aggregated value, such as total sales, from an OLAP cube or data model for the members you choose. Needs a workbook data model (Power Pivot) or an OLAP cube connection.

Compatibility functions

BETADIST: Returns the cumulative beta distribution, often used to model the spread of percentages or project task times.
BETAINV: Returns the inverse of the cumulative beta distribution. Use it to find the value that gives a chosen beta probability.
BINOMDIST: Returns the binomial probability of getting a set number of successes in a fixed number of tries, such as heads in coin tosses or faulty items in a batch.
CEILING: Rounds a number up to the nearest multiple you choose, for example up to the nearest 5 or 0.05.
CHIDIST: Returns the right tailed probability of the chi squared distribution. Used to test whether observed results differ from what you expected.
CHIINV: Returns the inverse of the right tailed chi squared distribution. Use it to find the critical value for a chi squared test.
CHITEST: Returns the p value of a chi squared test comparing observed counts with expected counts. A small result means the difference is unlikely to be chance.
CONCATENATE: Joins several pieces of text into one, for example a first name and surname.
CONFIDENCE: Returns the margin of error for a population mean using the normal distribution. Add and subtract it from the average to get a confidence interval.
COVAR: Returns the population covariance of two sets of data, showing whether they tend to move together.
CRITBINOM: Returns the smallest number of successes for which the cumulative binomial probability is at least a chosen level. Often used in quality control.
EXPONDIST: Returns the exponential distribution, used to model waiting times such as time between customer arrivals.
FDIST: Returns the right tailed F probability, used to compare how spread out two data sets are.
FINV: Returns the inverse of the right tailed F distribution. Use it to find the critical F value for a test.
FLOOR: Rounds a number down to the nearest multiple you choose, for example down to the nearest 5 or 0.05.
FTEST: Returns the two tailed p value of an F test, which checks whether two data sets have significantly different variances.
GAMMADIST: Returns the gamma distribution, used to model skewed data such as waiting times or rainfall amounts.
GAMMAINV: Returns the inverse of the cumulative gamma distribution. Use it to find the value that matches a given probability.
HYPGEOMDIST: Returns the probability of a given number of successes in a sample taken without replacement, such as picking cards or checking items from a batch.
LOGINV: Returns the inverse of the lognormal cumulative distribution, used for values that cannot be negative and are skewed, such as prices.
LOGNORMDIST: Returns the cumulative lognormal distribution of x, for data whose logarithm is normally distributed.
MODE: Returns the most frequently occurring number in a list.
NEGBINOMDIST: Returns the probability of a given number of failures before reaching a set number of successes.
NORMDIST: Returns the normal (bell curve) distribution for a value, given a mean and standard deviation.
NORMINV: Returns the value on a normal distribution that matches a given probability.
NORMSDIST: Returns the cumulative standard normal distribution, which has a mean of 0 and standard deviation of 1. Use it to turn a z score into a probability.
NORMSINV: Returns the z score on the standard normal distribution that matches a given probability.
PERCENTILE: Returns the value at a chosen percentile in a set of numbers, for example the 90th percentile of delivery times.
PERCENTRANK: Returns the rank of a value in a data set as a percentage of the data set.
POISSON: Returns the Poisson distribution, used to predict the number of events in a period, such as calls per hour.
QUARTILE: Returns a quartile of a data set, such as the lower quartile or median.
RANK: Returns the rank of a number in a list, for example a salesperson's position in a league table.
STDEV: Estimates the standard deviation of a sample, showing how spread out values are from the average.
STDEVP: Returns the standard deviation of a whole population, showing how spread out values are from the average.
TDIST: Returns the Student t distribution probability, used in hypothesis tests on small samples.
TINV: Returns the two tailed inverse of the Student t distribution. Use it to find the critical t value for a confidence interval.
TTEST: Returns the p value of a Student t test, which checks whether two groups have significantly different averages.
VAR: Estimates the variance of a sample, a measure of how spread out values are.
VARP: Returns the variance of a whole population, a measure of how spread out values are.
WEIBULL: Returns the Weibull distribution, often used in reliability analysis such as how long a part lasts before failing.
ZTEST: Returns the one tailed p value of a z test, which checks whether a sample average is significantly higher than a given value.

Add-in and automation functions

CALL: Calls a procedure in a Windows DLL or code resource. It is a legacy function and is blocked by default for security.
EUROCONVERT: Converts between the euro and the old national currencies of euro countries, such as German marks, using the fixed official rates. Needs the Euro Currency Tools add in.
REGISTER.ID: Returns the register ID of a DLL procedure that has been registered, for use with CALL. A legacy function blocked by default for security.

AI and Python functions

COPILOT: Sends a prompt to Copilot AI and returns its answer in the grid, for tasks like summarising feedback, classifying text or generating ideas. Needs a Microsoft 365 Copilot licence.
PY: Runs Python code in the Microsoft cloud and returns the result to the cell. You normally type =PY or press Ctrl+Alt+Shift+P to open a Python cell rather than writing the function by hand.

Google Sheets only functions

ARRAYFORMULA: Makes a formula work on whole ranges at once instead of one cell, so a single formula fills a whole column. Useful for calculating every row without copying the formula down.
ARRAY_CONSTRAIN: Limits the result of an array or range to a set number of rows and columns. Useful for showing only the top part of a large result.
COUNTUNIQUE: Counts how many different values there are in a list, ignoring duplicates. Useful for counting distinct customers, products or dates.
EPOCHTODATE: Converts a Unix epoch timestamp into a date and time in UTC. Useful for reading timestamps exported from websites, apps and databases.
FLATTEN: Turns one or more ranges into a single column. Useful for stacking data from several columns into one list.
GOOGLEFINANCE: Fetches current or historical share prices, currency rates and other market data from Google Finance. Useful for tracking investments or converting currencies.
GOOGLETRANSLATE: Translates text from one language to another using Google Translate. Useful for translating product names, messages or lists in bulk.
IMPORTDATA: Imports a CSV or TSV file from a web address into the sheet. Useful for live data files that are published online.
IMPORTFEED: Imports items from an RSS or Atom feed, such as blog posts or news headlines. Useful for monitoring news or content in a sheet.
IMPORTHTML: Imports a table or list from a web page into the sheet. Handy for pulling in published tables such as league tables or price lists.
IMPORTRANGE: Pulls a range of cells from a different Google Sheets spreadsheet into the current one. Useful for combining data kept in separate files.
IMPORTXML: Imports data from a web page or XML feed using an XPath query. Use it to pull specific items such as headings, prices or links from a page.
ISEMAIL: Checks whether a value looks like a valid email address. Useful for spotting typing mistakes in contact lists.
ISURL: Checks whether a value looks like a valid web address. Useful for cleaning lists of websites.
JOIN: Joins values or whole arrays into one piece of text, with a chosen separator between each item. Useful for building lists such as email addresses separated by commas.
QUERY: Runs a database style query on a range using the Google Visualization API Query Language. Use it to filter, sort, group and summarise data with one formula.
REGEXMATCH: Checks whether text matches a regular expression pattern and returns TRUE or FALSE. Useful for validating codes, finding rows that contain numbers or spotting patterns.
SORTN: Sorts a range and returns only the first n rows. Useful for top 10 lists or leaderboards.
SPARKLINE: Draws a tiny chart inside a single cell. Useful for showing trends next to figures, such as monthly sales.
SPLIT: Splits text into separate cells around a chosen character or string. Useful for separating full names, addresses or comma separated lists.
TO_DATE: Converts a number into a date, so it displays as a date. Useful when dates show as plain serial numbers.
TO_DOLLARS: Converts a number into a US dollar value, so it displays with a dollar sign and two decimal places. Useful for showing prices in dollars.
TO_PERCENT: Converts a number into a percentage, so 0.25 displays as 25%. Useful for showing ratios as percentages in formulas.
TO_TEXT: Converts a number or other value into text, keeping how it is displayed. Useful before text functions such as REGEXMATCH.

Every function

All 523 Excel functions plus popular Google Sheets ones, each with its syntax, every argument explained and two worked examples.

Formula maker

Type what you want in your own words, such as “sum column C where column A is London”, and get the formula, other ways to write it, and the columns it uses.

Fits your Excel

Pick your Excel version and the maker gives formulas that work in it, with semicolon separators for European Excel.

Questions

How do I use the formula maker?

Describe the job, ideally with your column letters and values: “count rows where B is more than 100”, “look up the price in column C for the code in E2” or “if A2 is blank show Missing”. If you leave the letters out, the maker picks some and you can change them in the boxes under the formula.

Which Excel versions are covered?

Microsoft 365, Excel 2024, 2021, 2019, 2016 and older. Each function shows the first version that has it, and newer functions like XLOOKUP, FILTER and TEXTSPLIT come with older alternatives.

Does it work for Google Sheets?

Most formulas work in Google Sheets too, and every function says whether it does. Functions that only exist in Google Sheets, such as QUERY and IMPORTRANGE, are included as well.

Is anything I type sent anywhere?

The search and the built in formula maker run in your browser, so nothing is sent. If this site offers the AI option and you press its button, only your question is sent to write the formula. You never upload a spreadsheet.