Excel 365 vs 2021 vs 2019: Which Functions You Get

Excel 365 vs 2019 vs 2021 matters most for one reason: which functions you can use. Excel 2019 added IFS, SWITCH, MAXIFS, MINIFS, CONCAT and TEXTJOIN. Excel 2021 added XLOOKUP, LET and dynamic arrays such as FILTER, SORT and UNIQUE. Microsoft 365 has all of those and keeps adding more, such as TEXTSPLIT, TEXTBEFORE and TEXTAFTER (also in Excel 2024). This guide is for anyone sharing files across versions or preparing for a job test on an unknown setup.

Excel 365 vs 2021 vs 2019 at a glance#

Each version includes everything from the versions before it, plus its own new functions. Microsoft 365 is the only one that keeps changing.

Excel for the web, the free browser version, follows Microsoft 365 closely for functions; our comparison of Excel desktop vs Excel for the web covers what else differs.

What Excel 2019 added#

Excel 2019 brought a set of functions that make everyday formulas shorter. None of them spill, so they work like classic functions:

• IFS checks several conditions in order, replacing long nested IFs.
• SWITCH turns codes into labels, like =SWITCH(A2,"N","North","S","South","Other").
• MAXIFS and MINIFS find the highest or lowest value that meets conditions.
• CONCAT and TEXTJOIN combine text; TEXTJOIN adds a separator and can skip blanks.

If your files must open in Excel 2016 or older, avoid these too. Our guides to the IFS function and MAXIFS and MINIFS cover the older alternatives.

What Excel 2021 added#

Excel 2021 is the big step: it brought dynamic arrays, where one formula returns many values that spill into the cells around it. The new functions built on that are:

• FILTER: every row that meets a condition.
• SORT and SORTBY: a sorted copy of a range.
• UNIQUE: a list without repeats.
• SEQUENCE: a list of numbers, such as 1 to 12.

Excel 2021 also added XLOOKUP and XMATCH, the modern lookup functions, and LET, which names a value inside a formula so you don't calculate it twice. LET's names live only inside one formula; for names the whole workbook can use, see our comparison of named ranges vs cell references. XLOOKUP alone changes how people work: it looks left, defaults to an exact match and has a built-in "not found" message. Our guide to XLOOKUP for beginners covers it in full, and the XLOOKUP function guide has a quick reference.

What Microsoft 365 adds on top#

Microsoft 365 includes everything above and keeps receiving new functions through updates, so its list grows over time. Among the newer ones:

• TEXTSPLIT splits text at a delimiter into columns or rows.
• TEXTBEFORE and TEXTAFTER return the text before or after a delimiter.

These three are also in Excel 2024, but not in Excel 2021 or 2019. Microsoft 365 also has other recent additions that this guide doesn't list one by one; if a function you read about isn't in your Excel, check the function's Microsoft Support page, which lists the versions it works in.

The same task in each version#

Here is one staff list. The task: list everyone in Sales.

In Excel 2021, 2024 or Microsoft 365#

One formula in E2 does it:

=FILTER(A2:A7,B2:B7="Sales")

E2:E4 fill with Tom, Mateo, Kofi, and the list updates if a department changes. The FILTER function guide explains the arguments.

In Excel 2019#

FILTER doesn't exist, so the usual answer is a tool instead of a formula: select the table, press Ctrl+Shift+L (⌘⇧F on a Mac) to turn on filter buttons, and choose Sales in the Department filter. The result is the same three names, but it doesn't update by itself and it hides rows instead of making a list.

Looking up a value to the left#

To find Aisha's department from her name, any version can use INDEX and MATCH, while 2021 and later can also use XLOOKUP:

=INDEX(B2:B7,MATCH("Aisha",A2:A7,0))
=XLOOKUP("Aisha",A2:A7,B2:B7)

Both return Operations. When the return column is on the left, XLOOKUP is still one function, as our guide to looking up to the left shows.

What happens when a newer function meets an older Excel#

A file keeps its formulas when it moves between versions, but older Excel can't calculate functions it doesn't know. When Excel 2019 opens a workbook that uses XLOOKUP or FILTER:

• The formula appears with an _xlfn. prefix, such as =_xlfn.XLOOKUP(…).
• The cell may show the last saved result at first, but returns #NAME? once it recalculates.
• Dynamic array results may appear as fixed, older-style array formulas.

The same happens in Excel 2021 with TEXTSPLIT. If a file will be shared widely, either stick to functions everyone has or paste the results as values before sending.

When to choose each version#

For learning and job preparation, the version decides which functions you can practise:

• Microsoft 365: the best choice if you can. You get every function in this guide and future ones, and it matches what many workplaces use. Analyst roles increasingly expect dynamic arrays; see Excel skills every data analyst needs.
• Excel 2021 or 2024: fine for nearly all job tasks, including XLOOKUP and FILTER. Excel 2024 adds the TEXTSPLIT family.
• Excel 2019: still works for classic skills (VLOOKUP, INDEX MATCH, SUMIFS, pivot tables), but you can't practise the newer functions. Microsoft's support for Office 2019 ended in October 2025, so it no longer receives security updates.

To check your version on Windows, go to File > Account and look under Product Information (or click About Excel). On a Mac, open the Excel menu and choose About Microsoft Excel.

Common mistakes with Excel versions#

Version mix-ups cause some of the most confusing errors:

1. Assuming everyone has XLOOKUP. Colleagues on 2019 or older will see #NAME?.
2. Learning only the new functions. Many existing workbooks and tests still use VLOOKUP and INDEX MATCH.
3. Typing over a spill range. In 2021 and later, any value in the way of a FILTER or SORT result gives #SPILL!.
4. Treating Excel 2021 like Microsoft 365. 2021 doesn't get new functions, so TEXTSPLIT never arrives there.
5. Treating "Microsoft 365" as one fixed version. It is a subscription with update channels, so two people on Microsoft 365 can see different function lists for a while. If a new function is missing, check for updates.

Our recommendation#

Practise on Microsoft 365 if you can, but write formulas for the oldest version your readers use. For files you share widely or tests on an unknown setup, VLOOKUP, INDEX MATCH, SUMIFS and IF are safe everywhere. Use XLOOKUP, FILTER, SORT and UNIQUE when everyone has Excel 2021 or later, and the TEXTSPLIT family only on Microsoft 365 or Excel 2024.

In short: Excel 2019 for the classics, 2021 for XLOOKUP and dynamic arrays, Microsoft 365 for everything.

Practise it#

Try the FILTER exercise on this page, then work through the XLOOKUP lesson to look up in both directions with a "Not found" message. The modern Excel functions lesson covers TEXTSPLIT, XMATCH, SORTBY and the # spill reference, so you'll know exactly which newer tools need which version.