Excel functions and calculation ¶

The release notes below describe changes compared with version 11.6; they are not the full function inventory. See the supported-functions reference.

1. Excel functions ¶

The largest expansion is in functions that return multiple results.

• Filtering, sorting and sequences. FILTER, SORT, SORTBY, UNIQUE, SEQUENCE and RANDARRAY.
• Reshaping and combining arrays. TAKE, DROP, CHOOSEROWS, CHOOSECOLS, VSTACK, HSTACK, TOROW, TOCOL, WRAPROWS, WRAPCOLS and EXPAND.
• Text and array mathematics. TEXTSPLIT, together with array-result support for TRANSPOSE, MMULT, MINVERSE, MUNIT, FREQUENCY, TREND, LINEST, LOGEST and GROWTH. The regression functions also gained support for multiple predictor columns.
• Expanded lookups. XLOOKUP and XMATCH already existed in v11.6; v12 extends them with array forms and multi-cell results.

Reusable formula logic is another major addition. LET introduces local names within formulas, and LAMBDA enables reusable worksheet-defined calculations. MAP, BYROW, BYCOL, REDUCE, SCAN, MAKEARRAY and ISOMITTED support applying that logic across arrays, accumulating results and handling optional parameters.

Regular expressions. REGEXTEST, REGEXEXTRACT and REGEXREPLACE, Microsoft 365’s functions for validating, extracting and cleaning text, use the same pattern syntax as Excel. That includes capture groups, case-insensitive matching, a chosen occurrence and multi-cell results. They work on any web page, with no special server or browser settings.

More functions, by category:

• Text. TEXTBEFORE, TEXTAFTER, ARRAYTOTEXT, VALUETOTEXT and NUMBERVALUE.
• Math and logic. XOR, IFNA, PERCENTOF, CEILING.MATH, FLOOR.MATH, and CONVERT for unit conversion.
• Lookup, reference and information. INDIRECT and OFFSET (now complete; see below), ADDRESS, AREAS, TYPE, and TRIMRANGE together with trim references such as A1:.A100.
• Date and financial. DAYS, VDB, and the coupon-date functions COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM and COUPPCD.
• Engineering. IMLOG10 and IMSQRT.
• Statistics. MEDIAN, MODE, MODE.SNGL, MODE.MULT, HARMEAN, MINA, MAXA, COUNTBLANK, RANK.EQ, RANK.AVG, PERCENTILE.INC, PERCENTILE.EXC, PERCENTRANK.INC, PERCENTRANK.EXC, QUARTILE.INC, QUARTILE.EXC, VAR.S, VAR.P, STDEV.S, STDEV.P, SKEW.P, COVARIANCE.S, COVARIANCE.P, PEARSON, STEYX, GAUSS, PHI, GAMMA, GAMMALN.PRECISE and PERMUTATIONA.
• Probability distributions and tests. NORM.DIST, NORM.INV, NORM.S.DIST, NORM.S.INV, LOGNORM.DIST, LOGNORM.INV, BINOM.DIST, BINOM.DIST.RANGE, BINOM.INV, NEGBINOM.DIST, HYPGEOM.DIST, POISSON.DIST, EXPON.DIST, GAMMA.DIST, GAMMA.INV, BETA.DIST, BETA.INV, WEIBULL.DIST, T.DIST, T.DIST.RT, T.DIST.2T, T.INV, T.INV.2T, T.TEST, F.DIST, F.DIST.RT, F.INV, F.INV.RT, F.TEST, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV, CHISQ.INV.RT, CHISQ.TEST, Z.TEST, CONFIDENCE.NORM and CONFIDENCE.T. The older names POISSON, EXPONDIST, GAMMADIST, GAMMAINV, BETAINV, CRITBINOM, TDIST, TINV, TTEST, FTEST, CHITEST and ZTEST now calculate too.
• Forecasting. FORECAST.LINEAR, FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY and FORECAST.ETS.STAT.

2. Formula types and calculation behaviour ¶

• Dynamic arrays and legacy array formulas. V12 adds an array calculation model, including spilled results, spill references such as B1#, and implicit intersection with @. Legacy Ctrl+Shift+Enter formulas use the same machinery. Expressions such as A1:A10*B1:B10, range comparisons and functions applied across ranges can calculate element by element. Spill output remains constrained by the converted page’s allocated space.
• Dynamic references. INDIRECT and OFFSET gain runtime address resolution, so references can respond to changing inputs. This includes structured table references inside INDIRECT and improved defined-name handling. Excel dependent dropdowns based on these functions can become cascading dropdowns on the web.
• More complete reference expressions. V12 expands INDEX reference-form support, including multiple areas and whole-row / whole-column results. A reference-returning function can also form either endpoint of a range expression.
• Improved calculation speed, fidelity and capacity.
• Iterative calculation. Iterative calculation settings are honoured in the runtime reference recalculation loop.
• Full Excel format strings. TEXT accepts Excel’s complete format-code language: several sections, conditions such as [>=1000], leading zeros, thousands separators, fractions, percentages, currency and language tags, elapsed time such as [h]:mm, and date and time codes down to fractions of a second. Richer date and time formats also display in cells as they do in Excel.
• Better international support. Calculations follow the workbook’s language and region. Numbers written as text are read with the correct decimal and thousands separators. Dates and times written as text are read in the local order. DOLLAR returns the local currency. COUNTIF/SUMIF-style criteria compare and sort text by local rules. TEXT format codes written in your Excel’s language are understood.

These are compatibility improvements as well as additions: they allow more existing workbooks to behave correctly without formula rewrites.

Version 12 help topics · Version 11 and earlier help