This article is a logical continuation of the previous material about the new dynamic arrays (DM) that appeared in Excel in Office 365. If you have not read it yet (too lazy to read – there is a video), then I strongly advise you to do it now to understand what, in fact, we are talking about how to get all these joys in your Excel.
The Office 365 update that gave Microsoft Excel a new calculation engine with support for dynamic arrays also added 7 new functions to our arsenal, sharpened specifically for working with arrays. In this article, I would like to talk about the three most important functions: SORT, FILTER and UNIC. The rest play a rather auxiliary role – about them a little later.
For simplicity, in all examples I will show the work of these functions on ordinary tables, but you can keep in mind that with “smart” tables (created through Home – Format as a table or keyboard shortcut Ctrl+T) these functions work fine too.
Here we go …
SORT function
Syntax:
=SORT(array; [sort_index]; [the sort order]; [by_column])
In the simplest version, it requires only an array (range) as an argument and returns it already sorted:
By default, sorting is in ascending order. If the reverse order is needed, then the third argument is responsible for this (1 – Ascending, -1 – descending):
If at the input you specify a range of several columns in the data, then sorting occurs by the first column. If you need to sort not by the first one, then you can specify the column number for sorting as the second argument:
If you want to sort by several columns at once, you will have to specify an array of column numbers in the second argument in curly braces. At the same time, in the same way, you can set the sort direction for each column as the third argument.
For example, if we want to sort our list by city in ascending order and then by amount in descending order, it would look like this:
The last argument is used if you need to sort not rows, but columns, i.e. arrange data horizontally, which is much less common.
That’s it – simple and elegant. Especially if you remember what kind of monstrous array formula you had to enter earlier to sort just one (!) column:
Brrr… 🙁
FILTER function
Syntax:
=FILTER(array; turn on; [if_empty])
The purpose of this function is to take an array of source cells as an argument and filter it based on the specified condition(s). Which rows to include in the results, and which ones to remove, is determined by the second argument. It must be an array of boolean values. LYING (FALSE) и TRUE (TRUE)that set the status for each row:
The logical TRUE and FALSE can, for compactness, be replaced by 1 (or any other number) and 0 (or an empty cell):
And the most interesting thing is that boolean values can be the result of some expression, for example:
If you think a little, then the scope for imagination here opens up wide. For starters, here are a few options for conditions that this function will work great with:
- =FILTER(B2:D25; D2: D25>=10000) — we select all orders where the cost is greater than or equal to 10
- =FILTER(B2:D25; LEFT(B2:B25) = “B”) — filtering all lines where the product name starts with “B” (blouse, trousers, breeches, etc.)
- =FILTER(B2:D25; (B2:B25 = “Pants”) * (C2:C25 = “Anna”)) — selection of all Anna’s deals where she sold trousers
- =FILTER(B2:D25; (C2:C25 = «Anna») + (C2:C25 = «Ivan»)) — all transactions of Anna and Ivan
- =FILTER(B2:D25; IFERROR(SEARCH(“Samara”,A2:A25),0)) — filtering all transactions where the city name contains the word Samara (Samara city, Samara city, Samara city, Samara town, etc.)
If the FILTER function does not find any value that satisfies the condition, then it throws an error #CALCULATE! To output something more meaningful instead, you can use the third argument:
UNIQUE function
Syntax:
=UNIK(array; [by_columns]; [once])
In its simplest version, this function extracts all the values available there from the input array, removes repetitions and gives out what is left:
If several columns are selected as the initial array, then the check for uniqueness goes over all, i.e. by a bunch of values from all cells of each row:
The third argument works very interesting – it forces the function to extract from the original array only those data that occur there only once, i.e. do not have clones:
Function combination
Of course, no one forbids us to “marry” all the mentioned functions among themselves – this is where the real magic, power and synergy begins.
Let’s analyze, for example, a classic task: suppose that we need to create a city guide for a drop-down list based on uploading from some database. In the initial unloading, the cities are repeated in random order, there are empty cells and duplicates. And the directory we need, containing the reference set of cities, should be:
- no repeats
- no empty cells
- sorted ascending from A to Z
All of the above is done with one (!) Formula:
In the English version, this function looks like:
=SORT(UNIQUE(FILTER(B2:B100000;NOT(ISEMPTY(B2:B100000)))))
Here:
- FILTER – selects only those cells where there is data (not empty)
- UNIQUE – removes repetitions in the list selected by the FILTER function
- SORT – sorts the resulting directory alphabetically
After that, it remains only to specify the resulting dynamic array as a source for the drop-down list on the tab Data – Data Validation (Data — Validation), not forgetting to add a pound sign after the address of the first array cell:
Moreover, despite the decent size of the initial range (100 thousand rows!) There is absolutely no braking when recalculating such a formula – the new Excel calculation engine copes with a bang. At the same time, the classic array formulas on such tasks began to noticeably dull on tables already from 3-5 thousand rows, and at 100 thousand they would simply drive your Excel into a coma.
Looking back and remembering all my completed automation projects over the past X years, I sadly realize how many days and probably even weeks could have been saved if dynamic arrays and such functions existed then. Yeaaah….
Well, better late than never, right? At least it will be easier for our children 🙂
- What are dynamic arrays in the new Excel and how do they work
- How to sort a list with a formula
- How to create a dropdown list in an Excel sheet cell