Latest functions in Microsoft Excel

As any other professional software, Excel evolves and changes over the years. Sometimes major changes are launched (e.g. Power Pivot) and other times smaller improvements are made available as part of the normal updates.

Below is a list of five recent functions that you might not have heard of. They are simple to use and often makes previous methods of data manipulation redundant.

In the bottom of this article, you will find an Excel file with a table introducing each function. Please feel free to download it and test the functions for yourself.

SORT (array, [sort_index], [sort_order], [by_col])

The SORT function sorts the contents of a range or array. This is an alternative to the usual filtering and sorting functionalities of Excel. Be aware that the sort order argument is 1 = Ascending (default) and -1 = Descending. Also, the by_col argument is TRUE = sort by column and FALSE = sort by row (default).

UNIQUE (array, [by_col], [exactly_once])

The UNIQUE function returns a list of unique values in a list or range. This is an alternative to the remove duplicates functionality. As for the SORT function, the by_col argument is TRUE = sort by column and FALSE = sort by row. Default is FALSE. Also the exactly once argument is TRUE = values that occur once, FALSE= all unique values (default).

FILTER (array, include, [if_empty])

The FILTER function allows you to filter a range of data based on criteria you define. This is an alternative to the usual filtering functionalities of Excel. You can even use multiple filtering criteria by using boolean logic. The if_empty argument is the value to return when no results are returned.

CONCAT(text1, [text2],…)

The CONCAT Function joins a range of cells or individual strings of data. It is a replacement of the previous CONCATNATE Function. The new version allows you to use ranges.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. Set the delimiter to and empty space (“ “) and the function effectively concatenates pieces of text into a sentence with proper spacing.

Figure 1. Workbook illustrating how to use the new functions in Excel 2020

Download a copy of the workbook illustrated above by clicking the link below. If the link has expired, please send me an e-mail at [email protected].

Reach out

If you have any questions, comments or need assistance with finance and performance management related issues, please reach out to me at [email protected]