COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
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.

  1. Using dynamic arrays in Excel
  2. Can I use dynamic arrays?
  3. Introducing dynamic arrays
  4. Filtering and sorting data (this blog)
  5. Two more functions: RANDARRAY and SEQUENCE
  6. 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):

Three range names

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:

UNIQUE formula example

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:

Results of UNIQUE function

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:

Sorted unique values

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: 

SORTBY function

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!):

Slytherin people

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

Here's what this would show:

Results of filter

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:

Filter returning no data

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

 

You can get round this by using the third argument:

What to show for empty dataset

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

You can combine two or more filters like this:

Combining two filters

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?

This blog has 0 threads Add post