Better live sorting in Excel 365

In late 2018, Excel 365 got an important new feature,  Dynamic Arrays . Here’s a simple way to use the feature to make your workbooks sort better and automatically. It also fixes a long-standing problem in Excel.

We’re going to focus on a very simple and useful part of dynamic array that anyone can use; the automatic sorting of tables. The details of dynamic arrays can be overwhelming in their power and complexity but simple sorting is a easy entry point.

Automatic sorting – not

Maybe you think Excel already has automatic sorting but it doesn’t and never has.  To put it another way, Excel’s core features can’t cope with the modern features which import and update data from external sources.

For example, here’s a list of the market caps of the biggest US companies using live stock price data. The table has been sorted by value and there’s conditional formatting as well.

That’s great until the stock values or shares issued change.  The valuations are updated by Excel’s Stock Data service.  Here’s what could happen as the market cap changes, (we’ve used fake valuations to make our point):

The conditional formatting has changed automatically as the values changed – no problem there.

But the sort order has NOT changed despite the changing values.  That’s because the table sorting and filtering is a once-only operation that’s not refreshed.  You have to reselect from the sort/filter pull-down to update.

That’s not good enough, especially when Excel has many more live and automatic data import options.  It’s dangerous when you think about filtering.  A filtered table isn’t updated automatically meaning the right rows might not be showing or incorrect ones visible.

There are a few workarounds to update the sort and filter.  One is via Excel VBA and refreshing the table. Another is PowerQuery (Excel for Windows only).

We’ll use Excel 365’s SORT() and SORTBY() functions because they work across all Excel 365 platforms.

SortBy() takes a Table and creates a copy of the Table (array) sorted in a different way.

SORTBY() is available in Excel 365 for all platforms, Windows and Mac, Apple, Android and Online. It’s not in Excel 2019, 2016 or earlier but will be in Office 2021 for Windows & Mac and Office LTSC for Windows/Mac

Real Time Excel

If you have our ebook Real Time Excel – check out  Market Capitalizations.xlsx in the ebook attachments for a working example of Sortby().   It’s the worksheet we’re using for all the examples in this article.

The full explanation of the workbook starts on page 46 of the current edition. There are also examples of auto-refreshing tables via VBA which is needed for Excel 2019, 2016 and earlier releases.

Source table vs displayed table

Sort()  and SortBy() make a copy of the original table, not update the original list.  That means we have to rethink how Excel displays information by separating the original data list from what’s visible.

Alphabetical is the original data in a table using live info from the Stock Data Type.  This is the source data that’s updated by Excel’s Stock Data type (see the little building icons at left).

By Market Cap is a copy of the same but automatically sorted by Market Cap. When the original data is updated, this tab is also updated.

By Country – sorts the same info by the country of origin (far right column)

Because we’re using Sortby() this sorting will update automatically each time the stock prices are updated (strictly speaking, each time Excel recalculates).

Here’s another example but this time the tables are side-by-side on the same sheet. The source data is on the left (yellow) table. The two right tables are the same data sorted into different orders – those tables will change if the original names and scores are updated.

It’s not just the values that get updated. If the source list is expanded or reduced, the SortBy() lists will be larger or smaller automatically.  That’s fantastic for transaction lists that are added to over time.

SortBy() syntax

The formal syntax for Sortby() is

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

Array – The array or range to sort. Can be a named table or range.

by_array1  – The array or range to sort on. Can be a named column in a table.

[sort_order1] – The order to use for sorting. 1 for ascending, -1 for descending. Default is ascending.

[by_array2]

[sort_order2] optional second sorting option.  It lets you sort, for example by value then name if the values are the same.

Add even more sort option pairs if you like.

Using SortBy()

The formula to make the sorted table for Market Capitalization is:

=SORTBY(MarketCap,Original Data[Market Cap (Billions)],-1)

MarketCap – the name of the table to be sorted

Original Data[Market Cap (Billions)] – the table column to sort by

-1 – sort order.  1 for ascending, -1 for descending.

That formula is in the cell A2, the first cell (top left) that you want the sorted details to appear in.

The formula in that single cell will fill all the cells below and across. NOT the table heading, which has to be copied separately.

If you’re not used to array formulas, that might be a surprise.  Most formulas return a single number or result that goes into that cell.  Functions like Sortby() return an array or table of results that fill the cells across and below the formula cell.

Notice that the conditional formatting of the Market Cap column is also copied into the auto-sorted table.

Tip: you can hide the source data table/tab so that only the automatically sorted tables appear in regular use.

What about Sort()?

Sort() is the little brother of SortBy() we generally use SortBy() because it has better options.

Sort’s syntax is:

=SORT(array,[sort_index],[sort_order],[by_col])

Sort works from data grids with column references by number – not name.

Sort’s fourth parameter allows sorting by column (TRUE) instead of the usual row sorting (FALSE – the default).

Dynamic Arrays now in Excel for iPhone and iPad
All about Office 2021 for Windows & Mac
The good and bad in Office LTSC