Excel’s Newest Features That You’ll Want to Know About!

If you use Excel through an Office 365 subscription, you know that new features appear regularly throughout the year. Because of this ‘trickle’ you might easily miss something new and useful, especially if your updates take place automatically in the background. As we head into a new year, here are several new features and functions you definitely want to know about that have been added to Excel over the past one.

New Collaboration Features

Show Changes is a new feature that allows you to view changes made by anyone who has worked on a shared workbook. The Show Changes button on the Review tab opens the Changes pane where details of who changes what, where, and when are recorded and saved for up to 60 days. The feature only works on files that have been saved to a co-authoring drive such as OneDrive or SharePoint but continues Microsoft’s drive towards collaborative work.

New User Interface Features

The new Navigation Pane allows you to browse the structure of an entire workbook and find information you are looking for with search and filter features. The pane can help users quickly get oriented when opening an unfamiliar workbook and help you find content of specific types. Most importantly, the navigation pane is a significant improvement in accessibility for vision impaired users.

Command Search functionality has been added to right-click menus so that you can quickly find and execute those commands that aren’t already included in the shortcut menus. Simply right-click to open the flyout menu, type the command you want in the search box and then click on a result to apply the command. This can keep you focused on your worksheet and prevent having to hunt through ribbons for the one you want.

New Text Functions

Responding to feedback that common text manipulation tasks were cumbersome with existing functions, Microsoft has responded by adding three new text functions that make easier to extract text from a cell’s contents using delimiters:

TEXTBEFORE – returns text that’s before delimiting characters that you specify

TEXTAFTER – Returns text that’s after delimiting characters that you specify

TEXTSPLIT – Splits text into rows or columns using delimiters that you specify

In this simple example, we want to create new columns for first names and last names. We could use the Text to Columns feature, but then we would lose our starting column with the combined names.

You could also use the Flash Fill feature to quickly populate the new column, but this method requires that you repeat the steps each time new information and would be inconvenient if data is added over time.

Lastly, the following formula is what you would need to perform the task with previous functions. To extract the first name, not only do you have to nest text functions to specify a delimiter, but you also have to manually adjust your results by -1 to avoid invisible spaces:

=LEFT(A2, FIND(” “,A2)-1)

The Last name is even more difficult. The FIND function returns the location of the delimiter, a space, and then the RIGHT function returns the characters from the full name after that space.

=RIGHT(B5,LEN(B5)-FIND(” “,B5))

Here is the same result with Excel’s new functions:

=TEXTBEFORE(A2,” “) and =TEXTAFTER(A2,” “)

To separate text with multiple delimiters into multiple columns, use the TEXTSPLIT formula:

=TEXTSPLIT(A1,”,”)

TEXTPLIT behaves much like Text to Columns but allows you to preserve your original cell of data and place the new row anywhere you like on the sheet.

New Array Manipulation Functions

Dynamic arrays have become a popular and growing area of spreadsheet design since they were introduced in 2019. In 2022, Microsoft added 11 new functions for working with them.

• VSTACK and HSTACK allow you to more easily combine dynamic arrays by stacking them either vertically or horizontally.

• TOROW and TOCOL let you convert a 2D array into a single row or column

• WRAPROWS and WRAPCOLS creates a 2D array from a single row or column

• TAKE and DROP let you reduce an array by specifying the number of rows to keep or remove

• CHOOSEROWS or CHOOSECOLS lets you choose specific rows or columns out of an array using their index

• EXPAND lets you grow an array to the size of your choice and specify what the empty cells will display

While these are some of the most interesting and useful additions to Excel 365 in 2022, this list certainly does not include every new feature, tweak, improvement, and bug fix that Microsoft has released. It also does not include some very exciting features that have only been released to early testers that will roll out in the future to all accounts. So, if you have missed something from above that you wish you’d know about sooner, you can review what’s new each month at Microsoft’s Excel Blog and stay on top of the new features that will benefit you the most.

What new feature are you most excited about, either just released or coming soon?