Top Five Features of Office 2021 Update
Dynamic array in Excel (one formula returns multiple cells)
XLOOKUP in Excel is more powerful and easier to write than VLOOKUP
Inventory images built into PowerPoint (and other applications)
Simultaneously editing Excel, PowerPoint, and Word desktop applications
Translation tools for emails, phrases, and almost everything
All Microsoft Office apps
You will notice the rounded edges, performance updates, and other small user interface changes on the ribbon tab (such as the undo/redo button being moved, the search bar being updated, and text, commands, documents, help, etc. can now be found). Microsoft documentation has made some updates to all applications here, but this list is more complete:
Hide Quick Access Toolbar: Your Quick Access toolbar may disappear by default, but you can restore it by right-clicking the ribbon and selecting Show it. Then, when you right-click the toolbar, you can choose to display the command label.
Collaborate with Excel, PowerPoint, and Word
Co author: Documents saved on OneDrive or SharePoint can now be edited by multiple people using desktop applications in Word, Excel, or PowerPoint, and you will see icons of each person's photo currently in the file. In addition to co creation, some enhanced features have also been introduced
Auto save: stored on OneDrive or SharePoint, you will be able to turn on/off auto save
Modern comments: Insert>comments and start a thread that may reply or @ mention colleagues. You can open the comment pane from the top right corner. The function previously known as' comments' in Excel has been renamed to 'comments' and can still be used from the' review 'tab.
Accessibility Checker: Review>Inspection Accessibility Now suggest how to improve the document.
Translation: Use 'Review'>'Translation' on phrases or entire documents/worksheets/slides
Excel Overview
A Microsoft blog about the new features of Excel 2021 is available for reading, but our list is more complete (for example, with Power Query functionality). The image shows the new features in this version and the features you may have missed from the past 10 years:
Unhide multiple worksheets: right-click on the worksheet → Unhide and then select multiple worksheets
LET: Simplify long formulas by assigning names to certain parts and then referencing them, usually referred to as variables in programming. Rewrite=IF (C6 * 1.05<200200+C6, C6 * 1.05) with=LET (FiveP, C6 * 1.05, IF (FiveP>200, FiveP, 200+C6)), where FiveP is the given and reused name.
Workbook Statistics: This review tab feature displays an overview of your worksheet and workbook. Identifying the last cell with data is also useful, as it can lead to particularly large file sizes.
Worksheet view: One person's filters may interfere with others' processes during simultaneous editing, so you can now filter or sort only for yourself, and you can also save the filter/sort view and reuse it.
XLOOKUP for Excel
XLOOKUP: VLOOKUP (the third most commonly used function in Excel) is now largely considered a legacy function and will continue to exist simply because most Excel users regretfully do not explore new features. Merging two tables by looking up values in another table is useful in many cases. VLOOKUP has always been necessary, but it is not convenient. Experts have always turned to the extremely complex INDEX MATCH combination. XLOOKUP not only fixes most of VLOOKUP's defects, but also more intuitively writes and understands for beginners while doing so.
The required syntax is XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Input 1-3 is required, I sometimes use 4 but rarely use 5-6. The following are its inherent VLOOKUP issues:
a) Default exact match (the most common use case).
b) Find from right to left or left to right because you specified columns instead of table arrays
c) Inserting columns in the lookup table will not cause problems.
d) XLOOKUP also replaces HLOOKUP as it applies to arrays (one column or one row)
e) If not found, returns the selected value with the fourth input.
Benefits of being more ambiguous based on the last two inputs
f) By approximate matching of the next maximum or next minimum item.
g) Search in reverse order.
XLOOKUP is not perfect as it still cannot handle duplicates in lookup tables or merge columns for lookup, but Power Query's merge queries can fill these gaps.
For bidirectional lookups using INDEX MATCH MATCH combinations, XMATCH (lookup_value, lookup_array, [match_mode], [search_mode]) is also useful.
Excel's dynamic array
A formula returns an output on a cell, which is how Excel has been working until now
Premise of dynamic array: a formula can now return multiple cells, replacing the overly complex concept of "array formula". Now you can execute=A3: A15 * B3: B15 in C3 instead of using=A3 * B3 and drag down to C15, and the results will be displayed in cells C3: C15:
You may see # SPILL! If the data is already in cell C4: C15, or if the dynamic array is set to return a value in an Excel table (although the table can be used as an input source), an error will occur.
#Reference: A5 # refers to a dynamic array starting from A5 (which can be a dynamic number of multiple rows or columns).
TRANSPOSE: Although this is not a new function, it is now easier to flip rows and columns linked to the source array.
ANYTHINGIFS: Nest an IF in TEXTJOIN to concatenate text according to standards, copy MEDIANIFS, STDEVIFS, or any similar method you prefer.
Six new functions have been released with dynamic array:
UNIQUE:=UNIQUE (Column) returns a unique value, and there are more advanced multi column options. The generated dynamic array grows or shrinks according to the source data.
SORT: I often use=SORT (UNIQUE (Array)) to create a sorted data validation list.
SORTBY: Use SORT to sort by the first column, otherwise I recommend SORTBY. Using SORTBY, you can refer to the columns to sort compared to SORT's column index numbers (which may be out of range).
FILTER: Generate a filtered output array for certain columns.
SEQUENCE: Generate a continuous number of specified array sizes, and set the start and step sizes. Use cases include TOP5 sales or all sales in the past 7 days.
RANDARRAY: Generate a random number matrix, specifying the required columns, rows, minimum and maximum values, as well as whether they must be integer values.
Power Query in Excel
Extract from PDF: Data>Get Data>From PDF, and even merge multiple PDFs into one folder
Fuzzy matching: Match Fred, Freddy, or Elizabeth with Lizzie or 078-524 with+4478542. When merging queries, check this option to match tight but imprecise text, specify ignoring case or spaces, match must be tight from 0 to 1 (default is 0.8), and the maximum number of matches is or matches with completely different conversion tables (such as UK to UK).
M IntelliSense: Obtain autocomplete functionality when writing custom M code in custom columns, advanced editors, or formula bars, such as in Excel, double-click or press Tab to lock suggestions
Input data: Add custom data directly in Power Query from this homepage tab function
Column analysis: A colored bar appears below the column name, displaying errors, null values, and values, but the view tab has new options.
By default, the first 1000 rows are displayed as all rows in the bottom left corner of the screen.
PowerPoint
The new features of PPT 2021 can be found on the Microsoft blog, but our list is more complete.
In addition to the first three, these features are also available in Excel, Word, and Outlook, but are most commonly used in PowerPoint
Slide link: If the document is saved in the cloud, you will get this option when you right-click on the slide
Reading order: Select how screen readers prioritize objects from "Review">"Check accessibility"
Enhanced recording: Slide Show>Recording now supports your videos, laser pens, and ink
Image transparency: configured through "Image Format">"Transparency"
Sketch lines: The new line format option means that the lines around the shape can now have curves that mimic hand drawn shapes
Hexadecimal code coloring: Set the color by clicking on 'More Colors', where you can change the color to type in hexadecimal or RGB codes
Auto Play Gif: Insert any Gif and automatically play, or click | | to pause
Ink update: Drawing>Ink can replay animations of previously created hand drawn ink, and also provides rulers, erasers, and lasso selectors (not in Outlook). By default, the drawing tab may not be open and can be enabled through File>Options>Customize Ribbon
Built in inventory image library
The 1000 types of inventory content in 6 categories are mainly used for PowerPoint, but can also be used for Excel, Word, and Outlook. More content is added every month, but non advanced subscribers can only access a portion of the library.
Inventory photos: Pixel perfect high-definition photos from Insert>Pictures>Inventory images, some with many blank spaces for eye-catching full screen slide backgrounds
More icons: Insert>Icons are now part of the library and larger
Cute character: Microsoft hires models to pose in different positions with transparent backgrounds
Sticker: Telegram/Snapchat generation will prefer these characters over cutting them out
Video: A subtle video that automatically plays without sound, looping through once completed, perfect for eye-catching title slides, only available in PowerPoint
Word
Word and Outlook have relatively few features besides the ones mentioned above
Dark mode: Click File>Account and select Black to obtain the true dark mode, with white text on the black canvas
Line Focus: View>Immersive Reader has a new 'Line Focus' view
Outlook
Translation function: Translate messages by right-clicking on the translation phrase or setting automatic translation from File>Options
Inking: The drawing tab in emails allows you to annotate emails or draw on a separate canvas. The drawing tab may not be open by default and can be enabled through File>Options>Customize Ribbon
With the help of dynamic array, XLOOKUP, Stock content and other updates, the Office 2021 upgrade may really change the rules of the game.