New Excel Functions You Need to Know About (August 2022 Update)

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.

1. Text Functions

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.

2. Array Functions

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:

Combine Multiple Dynamic Arrays

You can use the new VSTACK and HSTACK functions to combine multiple dynamic arrays vertically or horizontally.

Convert 2D Arrays Into Lists and Vice Versa

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.)

Keep or Remove Rows/Columns

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.

Expand Dynamic Arrays

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.

3. Power Query Group Operations

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.

4. Improvements to the Excel and Power BI Connection

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:

• Dates are now date-and-time objects rather than strings. That means you can reorganize data from oldest to newest or vice versa.
• PivotTables connected to Power BI now support drag-and-drop aggregations. Previously, you had to create measures for analyzing data.

5. Insert Images in Cells

Availability: Windows, Mac, iOS, & Android Insiders

Excel’s new picture function lets you return an picture inside an Excel cell. Previously, when you inserted an picture into your spreadsheet, it hovered over the cells.

Since the new picture function returns images directly into cells, adjusting spreadsheets and moving cells won’t affect the picture.

You only need to enter a source location into the picture function formula to get the picture. The formula also lets you enter alternative text and predefine the picture dimensions.

6. Show Changes Option

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.

7. New Functions in Excel for the Web

Apart from the formulas and improvements we discussed above, some features are introduced solely in Excel for the Web. These are listed as follows:

• Format part of a cell using Rich text formatting.
• Sort Excel cells based on their color or icons using the sort-by-color or -icon options.
• You can delete chart elements. Simply select a data series in the chart and press the Delete or Backspace key on your keyboard.
• You can expand (and collapse) the formula bar for better visibility.
• Edit files having legacy data connections (e.g.Web Queries) and access data from these connections without altering or refreshing them.
• Edit files with the legacy Shared Workbook feature.

Try Out the New Excel Functions

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.