Microsoft Office 2021 is now available for purchase, with many new features above the last version Office 2019 for those who prefer a one-off payment option instead of a monthly subscription you now have access to some great enhancements:

If you are a Microsoft Office 365 subscriber, you may use this post as a digest of some (but not all) of features released over the last few years.

My top 5

• Dynamic Arrays in Excel (one formula returns multiple cells)
• XLOOKUP in Excel, more capable than VLOOKUP whilst being easier to write
• Stock Images built into PowerPoint (and other apps)
• Simultaneous editing of Excel, PowerPoint & Word desktop apps
• A translation tool for emails, phrases & virtually anything

All apps

You’ll notice rounded edges on the tabs on the ribbon, performance updates and other small user interface changes like the undo/redo buttons have moved and the search bar is updated and now can find text, commands, documents, help etc. Microsoft documents some updates to all apps here, but this list is more complete:

Hide Quick Access Toolbar: Your quick access toolbar may disappear by default, but you can get it back by right clicking the ribbon and choosing to show it, then when you right click the toolbar you can choose to show command labels.

Excel, PowerPoint & Word co-authoring

Co-author: A document saved on OneDrive or SharePoint can now have multiple people editing simultaneously using the desktop apps of Word, Excel, or PowerPoint, you will see icons of everyone’s photo who is currently in the file. Some enhancements alongside co-authoring have also been introduced…

Autosave: Store on OneDrive or SharePoint and you’ll be able to turn on/off Autosave

Modern Comments: Insert > Comment and start a thread with possibilities for replies or @mentioning co-workers, you can open the comments pane from the top right. Excel’s feature formerly known as “Comments” has been renamed to “Notes” still available from the Review tab.

Accessibility checker: Review > Check Accessibility now suggests how to improve documents.

Translate: Use Review > Translate on a phrase or your entire document/sheet/slide

Excel general

The Microsoft blog on Excel 2021 new features is available to read, but our list is more complete (e.g. with Power Query features). Images show what’s new in this version and features from the last 10 years you may have missed:

Unhide multiple sheets: Right click a sheet → Unhide then multi select sheets

LET: Simplify long formulas by assigning names to certain parts then referring back to the name, often called variables in programming. Rewrite =IF(C6*1.05<200,200+C6,C6*1.05), with =LET(FiveP,C6*1.05,IF(FiveP>200,FiveP,200+C6)) where FiveP is the name given & reused.

Workbook stats: This review tab feature shows an overview of your worksheet & workbook. It’s also useful to identify the last cell with data which leading to an unintentionally bloated file size.

Sheet view: One person’s filter could disrupt the flow of others during simultaneous editing, so you now can filter or sort for yourself only, you can also save a filter/sort view and reuse.

Excel’s XLOOKUP

XLOOKUP: VLOOKUP (Excel’s 3rd most used function) is now largely regarded as a legacy function that will continue to exist only because most Excel users sadly don’t explore what’s new. Merging two tables by looking up a value in one table in another table us useful in many scenarios. VLOOKUP has always been required but inconvenient and experts would historically resort to the terribly complicated INDEX MATCH combination, XLOOKUP not only fixes most of VLOOKUP’s flaws but does so whilst being more intuitive to write & understand for novices.

The required syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Inputs 1-3 are required, and I sometimes use 4 but rarely 5-6. Here are the VLOOKUP issues inherently fixed with it:

a) Exact match as default (the most common use case).
b) Lookup right to left or left to right since you specify columns, not the table array
c) Inserting columns inside the lookup table doesn’t cause issues.
d) XLOOKUP also replaces HLOOKUP as it works with arrays (a column or a row)
e) If not found return a chosen value with the fourth input.

More obscure benefits based on the last two inputs

f) Approximate match through next largest or next smallest item.
g) Reverse order search.

XLOOKUP isn’t perfect, it still cannot handled duplicates in the lookup table nor combine columns to look up but Power Query’s merge queries can fill these gaps.

• For two-way lookups with the INDEX MATCH MATCH combination, XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) is useful too.

Excel’s Dynamic Arrays

One formula returns an output on one cell, that’s the way Excel has always worked until now...

Dynamic arrays premise: One formula can now return multiple cells superseding the previous overcomplicated “Array formula” concept. Instead of e.g. =A3*B3 in C3 and drag down to C15, now you could do =A3:A15*B3:B15 in C3 and the results would show in cells C3:C15:

You may see a #SPILL! error if data is already in cells C4:C15 or if the dynamic array is set to return values inside an Excel Table (although a Table can be used as an input source).

# referencing: A5# refers to a dynamic array starting at A5 (which could be a dynamic number of multiple rows or columns).

TRANSPOSE: Although this isn’t a new function, it’s much easier now, =TRANSPOSE(source array) flips rows and columns linked to the source array.

ANYTHINGIFS: nest an IF inside TEXTJOIN to concatenate text based on criteria, replicate a MEDIANIFS, STDEVIFS, or whatever you like in similar ways.

Six new functions were released alongside dynamic arrays:

UNIQUE: =UNIQUE(Column) returns the unique values and more advanced multi column options exist, the resulting dynamic array grows or shrinks based on the source data.

SORT: I often use =SORT(UNIQUE(Array)) to create a sorted data validation list.

SORTBY: Use SORT for sorting by the first column or I recommended SORTBY otherwise. With SORTBY you reference the column to sort by (which could be outside the range) compared to a Column index number for SORT.

FILTER: Generate a filtered output array of certain columns.

SEQUENCE: Generate consecutive numbers in a specified array size, and set the start and the step increase. Use cases include TOP5 sales or all sales in the last 7 days.

RANDARRAY: Generate a matrix of random numbers, specify the desired columns, rows, min, max and whether it must be integer values.

Excel’s Power Query

Extract from PDF: Data > Get Data > From PDF, or even combine multiple PDFs in a folde

Fuzzy Matching: Match Fred or freddy or Elizabeth with Lizzie or 078-524 with +4478542. When merging queries, tick this option to match close but not exact text, specify to ignore casing or spaces, how close the match must be from 0-1 (0.8 is default), the maximum number of matches are or use a transformation table for matches that don’t resemble at all (like UK to Great Britain).

M IntelliSense: Get autocomplete when writing custom M code in a custom column, the advanced editor, or the formula bar, like in Excel, double click, or press tab to lock in a suggestion

Enter Data: Add custom data directly in Power Query from this home tab feature

Column profiling: A coloured bar now appears below the column name showing errors, nulls, and values, but the view tab has new options as shown:

By default the first 1000 rows is shown change to all rows from the bottom left of your screen.

PowerPoint

The Microsoft blog on PPT 2021 new features is available to read, but our list is more complete. Images show what’s new in this version and features from the last 10 years you may have missed:

Aside from the first three, these features are available in Excel, Word & Outlook too but would be used the most in PowerPoint

Link to slide: If a doc is saved on the cloud, you’ll get this option on right clicking a slide

Reading order: Choose how a screen reader will prioritise objects from Review > Check Accessibility

Enhanced recording: Slide Show > Record now supports your video, laser pointer & inking

 Image transparency: Configure via Picture Format > Transparency

Sketched lines: A new line format option means lines around shapes can now have curved lines imitating a hand drawing

Hex code colouring: Set a colour by clicking “More colours” wherever a colour can be changed to type in a Hex or RGB code

Autoplay Gifs: Insert any gif and it auto plays, or click || to pause

Ink updates: Draw > Ink can replay animates a previously made hand drawn ink, a ruler, eraser, and lasso selector are also available (not in Outlook). The Draw Tab may not be on by default, enable it via File> Options> Customise Ribbon

Built in stock images library

1000’s of stock content across 6 categories is mainly for for PowerPoint, but also available in Excel, Word & Outlook, more content gets added each month, but non premium subscribers will have access to a portion of the library only though.

Stock photos: Pixel perfect high-definition photos from Insert > Pictures > Stock Images, some have much blank space for compelling full screen slide backgrounds

More icons: Insert > Icons is now also part of the library and much larger

Cutout people: Microsoft hired models to pose in different positions with transparent backgrounds

Stickers: The Telegram/Snapchat generation will prefer these characters to cutout people

Videos: Subtle videos without sound autoplay then loop once ended, great for compelling title slides, only available in PowerPoint

Word

Word and Outlook have comparatively few features over and above what has been mentioned above

Dark mode: Click File > Account and choose black to get a true dark mode with white text on a black canvas

Line focus: View > Immersive reader has a new “line focus” view

Outlook

Translation: Translate a message, translate a phrase via right click or set up auto translation from File > Options

Inking: The Draw tab in an email lets you annotate emails or draw on a separate canvas, the Draw Tab may not be on by default, enable it via File> Options> Customise Ribbon

With dynamic arrays, XLOOKUPs and Stock content plus other updates, the Office 2021 upgrade could be a real game changer.