If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Find out how dynamic arrays in Excel will change your (working) life!

Part four of a six-part series of blogs

Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier.

Using dynamic arrays in Excel
Can I use dynamic arrays?
Introducing dynamic arrays
Filtering and sorting data (this blog)
Two more functions: RANDARRAY and SEQUENCE
Implications of dynamic arrays

Posted by Andy Brown on 14 February 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Filtering and sorting data

For this page, assume that I've created three range names called House, Character and Rating (shown coloured separately below):

You can do this by selecting A1:C11, pressing SHIFT + CTRL + F3 and choosing Top row.

Picking out unique values

The new UNIQUE function takes the following 3 arguments:

Argument	Type	Use
1	Compulsory	The block of cells from which you want to pick out unique values.
2	Optional	Whether you want to pick out unique rows (in the selected range above there aren't any, since the combination of each character's house, name and rating is unique) or unique values.
3	Optional	Whether you want to pick out values which appear exactly once (in the above example there aren't any, as each house has at least two values).

Here's an example to pick out the unique Hogwarts' houses:

If you miss out the second and third arguments, you'll get one version of each house referenced.

When you enter the above formula, you'll get this:

The function creates a spill range containing (as things stand) 4 entries.

Sorting values

You can use the SORT function to sort dynamic arrays. It takes these arguments:

Argument	Type	Use
1	Compulsory	The column or range you want to sort.
2	Optional	The column number in this range you want to sort by.
3	Optional	The sort order (1 is ascending, -1 is descending).
4	Optional	Whether you are sorting by column or by row.

For our example we'd like the houses to appear in alphabetical (and, coincidentally, moral) order:

Here we're sorting the house names alphabetically (the default). The formula is =SORT(UNIQUE(House)).

Sorting one set of values by another

Supposing you now decide that you want to sort the characters according to the rating given. To do this, you can use the SORTBY function, which takes these arguments:

Argument	Type	Use
1	Compulsory	The column or range you're sorting
2	Compulsory	The values you're sorting by
3	Optional	The sort order (1 is ascending, -1 is descending).
4	Optional	Additional array and sort order pairs (allowing you to create additional sort keys).

Here's an example of a SORTBY function in use:

This formula is sorting the Harry Potter characters into descending order by their rating. Luna Lovegood has the highest rating, so comes at the top of the list.

Typically you would use the SORTBY function for sorting tables of data and the SORT function for sorting single columns.

Filtering data

You can also use dynamic arrays to pick out the rows you want, using the FILTER function. This takes the following arguments:

Argument	Type	Use
1	Compulsory	The array to be filtered
2	Compulsory	An expression to evaluate for each row
3	Optional	What to return if the filter would return no data

So for our example, you could show all the people in Slytherin (hiss!):

This formula would filter the list of characters to pick out all the ones in Slytherin.

Here's what this would show:

The two people in Slytherin returned by the FILTER function.

If you look for the people in the Treetops house, you'll get an error:

If a FILTER function returns no data, you get a #CALC! error.

You can get round this by using the third argument:

You can use the third argument to return something in the cell containing your formula.

You can combine two or more filters like this:

This function returns the characters in Gryffindor house who have a Wise Owl rating of 8 or more.

The syntax for this seems a bit ugly to me - I wonder why you can't include extra filters as additional arguments?

Using dynamic arrays in Excel
Can I use dynamic arrays?
Introducing dynamic arrays
Filtering and sorting data (this blog)
Two more functions: RANDARRAY and SEQUENCE
Implications of dynamic arrays

This blog has 0 threads Add post