Dynamic array functions

There are over 22 dynamic array functions in Excel 365 and more are being added almost quarterly. We will, however, focus on the ones you can readily use in your reports and dashboards:

• UNIQUE
• FILTER
• SEQUENCE
• SORT
• SORTBY

Let’s start with UNIQUE, which is a very popular dynamic array function.

UNIQUE

UNIQUE is the formula version of Excel’s Remove Duplicates tool, which goes through a selection of Excel cells or tables and removes duplicate entries. It takes in a range and outputs a distinct version of that range. You can use it on a one-dimensional range – a list of values on a single row or single column. You can also use it on a table – a two-dimensional range. Unlike the Remove Duplicates tool, it is dynamically linked to the source range.

The function syntax is UNIQUE(array,[by_col],[exactly_once]). The array, as mentioned earlier, can be one-dimensional or two-dimensional. by_col is optional...