The August 2022 Excel update gives you several new functions to manipulate your data. Here’s a quick overview.
In August 2022, Microsoft introduced exciting new features and 14 new functions. While some of these new features are generally available, others are only limited to those on the Insider channels. The Insider features will also gradually roll out to everyone.
Let’s take a look at each of these functions and features and how you can use them to better analyze and calculate your data in Excel.
Availability: Excel for the Web & Office 365
The new TEXTBEFORE and TEXTAFTER functions make it easier to extract text strings from a cell. The TEXTBEFORE function returns the text before the intended delimiter, while the TEXTAFTER function returns the text string after it.
Another exciting addition is the TEXTSPLIT function which lets you split text across multiple rows or columns.
Availability: Excel for the Web & Office 365
Let’s take a look at the 11 array functions Excel released in its August 2022 update and how they can improve your experience with Excel:
You can use the new VSTACK and HSTACK functions to combine multiple dynamic arrays vertically or horizontally.
Use the TOROW function to convert the 2D array into a single row and the TOCOL function to convert it into a column. Alternatively, you can use the WRAPROWS and WRAPCOLS functions to convert a single row or column into 2D arrays after it reaches the set dimension (height or width.)
The TAKE and DROP functions let you keep or drop a set number of rows or columns (either from the start or the end). But if you want to fetch specific rows or columns from a dynamic array, you can use the CHOOSEROWS or CHOOSECOLS functions.
Excel has also introduced the EXPAND function that helps you expand your dynamic arrays. Not only can you specify the dimensions of the new array, but you can also specify a value to fill inside the new cells.
Availability: Excel for the Web & Office 365 (Business or Enterprise plans)
There are many good reasons to use Power Query in Excel: It lets you import and integrate data from external sources. You can then perform various operations on it to gain valuable insights while keeping the original source/s untouched.
New additions to the Power Query group operations make it much more convenient and efficient to sort out and analyze the data present on the Queries pane.
Now you can group queries together, helping you perform operations on multiple queries (of the same group) with a single click. For example, by refreshing a group, you can refresh all queries inside the group.
Moreover, Excel now lets you reorganize the Power Query groups more efficiently. You can move a query or group into another group or remove certain queries from a group. Likewise, you can expand or collapse all Power Query groups, simultaneously. Lastly, changing the group name and description has become more straightforward using Properties.
Availability: Excel for the Web & Windows Insiders (Beta Channel Version 2208)
In Excel, you can create PivotTables and connect them with Power BI dataset for a visual analysis of your data. For more efficient analysis, Excel has introduced two new improvements:
Availability: Windows, Mac, iOS, & Android Insiders
Excel’s new image function lets you return an image inside an Excel cell. Previously, when you inserted an image into your spreadsheet, it hovered over the cells.
Since the new image function returns images directly into cells, adjusting spreadsheets and moving cells won’t affect the image.
You only need to enter a source location into the image function formula to get the image. The formula also lets you enter alternative text and predefine the image dimensions.
Availability: Excel for the web, Excel for Microsoft 365, & Excel for Microsoft 365 for Mac
The Show Changes feature opens new doors for collaboration. You can select the Show Changes option in the Review tab to see the changes made in your workbooks. The Changes pane includes changes made up to 60 days (to review older changes, you’ll have to go to the Version History) with more recent changes at the top.
The Changes pane provides information on who made the changes, when and where they were made, and what was the previous cell value (allowing you to revert.)
You can review changes made within a particular sheet, cell, or cell range by selecting the relevant dataset and clicking Show Changes option in the context menu.
Apart from the formulas and improvements we discussed above, some features are introduced solely in Excel for the Web. These are listed as follows:
Hopefully, these additions will improve your experience of using Excel. But if you're still learning the ropes, it's best to enroll in an Excel course—there are many great Excel training courses.
Join our newsletter for tech tips, reviews, free ebooks, and exclusive deals!