BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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