Working with MS Excel Array Functions (FILTER, RANDARRAY, SEQUENCE, UNIQUE, SORT, SORTBY)

It’s hard to keep up with seemingly non-stop additions to Excel’s impressive catalog of functions. If you happen to belong to Office Beta channel you might have started working with the newest release of text and array functions. As a regular Office 365 subscriber I’ve already covered newish XLOOKUP and LAMBDA functions on this blog. It was last year when the Excel team introduced dynamic array functions, also known as spilled array functions. These functions return arrays of of values of different sizes and spill them into adjacent cells. You can usually specify how long and how wide the output ranges should be. These functions include – UNIQUE (returns unique values from the range of values), SEQUENCE (retrieves a sequence of values), SORT, SORTBY, RANDARRAY(array of random numbers based on specified parameters), and FILTER. In this post we will explore examples on how to use these functions.

Continue reading