# This Excel change is more than SORT of a big deal

In Part 4 of a seven-part series, Excel MVP Liam Bastick takes a look at two of the seven new functions Microsoft has created to go with its spreadsheet program’s new Dynamic Arrays capabilities.*Editor’s note:** Microsoft recently announced a partial rewrite of Excel’s calculation engine. The result is an evolutionary leap that promises to bring numerous improvements to Excel users in the coming years. Already unveiled are a new type of calculation (Dynamic Arrays), seven new functions, and two new errors. These changes have not yet been rolled out to all Excel users, but you don’t have to wait to see what they can do. Excel MVP Liam Bastick is covering all the changes in a seven-part series. In this instalment, Bastick walks you through two of the new functions — SORT and SORTBY. *

If you haven’t read the first three articles in this series, you really should. Just click on one of the links below:

- Part 1: Excel: Microsoft Unveils Dynamic Upgrades
- Part 2: Excel: Arrays, Tables, and ‘The Twilight Zone’
- Part 3: Excel: 2 New Errors and a SINGLE Function

On the off-chance you are new to this series and don’t have the time or inclination to read the first three articles, here’s a (relatively) brief summary. Microsoft’s new Dynamic Arrays functionality greatly simplifies the process of working with arrays in Excel. There’s no more copying formulas to the entire range and having to press **Ctrl+Shift+Enter** to see multiple results. Instead, you enter a formula in one cell, and, poof, the data you are looking for just *spill* out. Look at the screenshots below. As you can see, the formula entered into cell **F33** in the right image is **=F12:H27**, which references the original data shown in the left image.

As you can see, the formula spills (ie, it automatically extends into the designated area — in this case, three columns by 16 rows.). This type of formula is called a Spilled Array Formula, and Excel has introduced seven new functions that work with Spilled Array Formulas. One of the new functions, **SINGLE**, is needed to make workbooks with the old array formulas work with the updated Excel calculation engine (see “Excel: 2 New Errors and a SINGLE Function”). As for the other arrays, let’s go through them in an order that makes sense (well, to me anyway).

**Function focus: SORT**

Our first function is one people have been crying out for — for *years*. Enterprising spreadsheet gurus have developed array formulas and user-defined functions that have replicated the functionality of the new **SORT** function, but those won’t be needed once Microsoft makes **SORT** generally available.

So what’s the big deal? Let’s start with the syntax. The **SORT** function sorts the contents of a range or array, as follows:

**=SORT(array, [sort_index], [sort_order], [by_column])**.

It has four arguments:

**array:**this is required and represents the range that is required to be sorted.**sort_index:**this is optional and refers to the position of the row or the column in the selected**array**(eg,**by_column**. And be careful, it’s a little counterintuitive! The default value is 1.**sort_order:**this is also optional. The choices for**sort_order**are either 1 for ascending (default) or -1 for descending. It should be noted that you might not want to hold your breath waiting for sort by colour, formula, or custom list using this function.**by_column:**this final argument is also optional. Most people want to sort rows of data, so they will want the value to be FALSE (which is the default value if not specified).

To show you how devilishly simple it is, consider the following data:

Sorting the “Points” column in order is easy. All you have to do is type **=SORT(H13:H27) **into cell **F32**.

Simple, right? Note that the duplicates are repeated; there is no cull. If you want it in descending order, the formula is only slightly more sophisticated, in that the **sort_order** (third argument) needs to be specified as -1 to switch the sort to descending:

**=SORT(H13:H27,,-1)**.

Again, simple. But, you might be asking, why would anyone want to list the points all by themselves? What about the names?

Great questions, and the answer is that this is where all of these arguments in the **SORT** formula start to make more sense. Consider the screenshot below. **SORT(F13:H27,3,-1)** produces the whole array (**array** is **F13:H27**), sorts it on the third (**sort_index **3) column in descending (**sort_order **-1) order. Blake and Ivana tie on 508 points, but Blake appears first as he was first in the original (source) table.

So far, I have performed only one **SORT**. You can have more than one though. In the screenshot below, I have created a second (two-level) **SORT**. Here, you need to create what is known as an array constant for the second and third arguments (you just type the braces in — don’t use **Ctrl+Shift+Enter**):

**=SORT(F13:G27,{1;2},{1;-1})**.

This formula will sort on column 1 (“First Name”) first, then sort on column 2 (“Last Name”). This will be in ascending order (1) for the first column and descending order (-1) for the latter. It’s not as straightforward a formula entry as most Excel modellers are used to, but it’s relatively straightforward once you have committed it to memory.

My final example of **SORT** is not something that is limited to this new function, but it does show how things fit together. From the first three articles in this series, it might appear you can only get one value (using **SINGLE**) or all of them (using Dynamic Arrays). That’s not true as this illustration clearly demonstrates:

Only the top three have spilled in this example. How? Well, I cheated. I highlighted cells **F108:H110** first, then typed in the formula:

**=SORT(F13:H27,3,-1)**

and then pressed **Ctrl+Shift+Enter **(thus generating the **{** and **}** braces). This restricted the spill to the range stipulated. Cool. Other than making sure no one can delete or insert any rows by creating an array formula such as **{=1}** across the restricted area, these appear to be the only two uses of **Ctrl+Shift+Enter** now.

**SORT **is really useful then, but what if you want to sort on a field you don’t want displayed in the results?

**Function focus: SORTBY**

The **SORTBY** function sorts the contents of a range or array based on the values in a corresponding range or array, which does not need to be displayed. The syntax is as follows:

**=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)**.

It has several arguments:

**array**: this is required and represents the range that is required to be sorted.**by_array1**: this is the first range that**array**will be sorted on and is required.**sort_order1**,**sort_order2**, …: these are optional. The choices for each**sort_order**are 1 for ascending (default) or -1 for descending.**by_array2**, …: these arguments are also optional. These represent the second and subsequent ranges that**array**will be sorted on.

There are some important considerations to note:

- the
**by_array**arguments must either be one row high or one column wide. - all of the
**by_array**arguments must be the same size and contain the same number of rows as**array**if sorting on rows, or the same number of columns as**array**if sorting on columns. - if the sort order argument is not 1 or -1, the formula will result in a
*#VALUE!*error.

It’s pretty simple to use. Consider the following source data once more:

I can use **SORTBY** using the formula:** =SORTBY(F13:F27,H13:H27,-1,G13:G27,1). **This produces the results shown in the screenshot below.

Note that I have sorted the “First Name” field (**F13:F27**) on the “Points” column (**H13:H27**) in descending (-1) order and then used the second sort on “Last Name” (**G13:G27**) in ascending (1) order. No need for those pesky array references in multiple sorts with the **SORT** function (as detailed above).

An Excel Evolution: The Series

**Getting started**

Part 1: Excel: Microsoft Unveils Dynamic Upgrades

Part 2: Excel: Arrays, Tables, and ‘The Twilight Zone’

Part 3: Excel: 2 New Errors and a SINGLE Function

Part 4: This Excel Change is More Than SORT of a Big Deal

Part 5: Focus on 2 New Excel Functions: UNIQUE and FILTER

Part 6: Excel New Function Focus: SEQUENCE and RANDARRAY

**Final thoughts**

Part 7: Will Dynamic Arrays Kill Data Tables, PivotTables?

*— Liam Bastick**, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *An Introduction to Financial Modelling*. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an *FM* magazine senior editor, at Jeff.Drew@aicpa-cima.com.*