Dynamic Sorting Excel 365

Sorting as you know it changed in 2018, with the introduction of two new Excel 365 Sort Functions, SORT and SORTBY.  Both are Dynamic Array Functions (DAFs).  Dynamic sorting in Excel 365 will change how you program Microsoft Excel.

In this post we will show you several ways to sort in Excel 365, using four of the new dynamic array functions.  We will also show you how to sort in Power Query.  Lastly, you can manually sort an Excel Table.

 

What made is think about doing this post is simple, two of the newer dynamic array functions, PIVOTBY and GROUPBY, have one column to determine both the Sort Column, but also the Sort Order, in one cell.

The trick was the inclusion of negative numbers.  2 and -2 sort the same column, the second one, but one is ascending and one is descending.

 

Dynamic Sorting Excel 365 – Functions with SORT Ability.

  • SORT Function.
  • SORTBY Function.
  • GroupBy Function.
    • Contains a Sort Parameter
  • PivotBy Function.
    • Contains a Sort Parameter
      • Sort Column Option and Sort Order Option are combined, in a single attribute, GroupBy & PivotBy.  That was smart.

 

In the image below, you see the new SORT Function being used.  Pretty simple, sort the table.

Dynamic Sorting Excel 365 Blog Post: Image of new Microsoft Excel SORT Function being used against a Power Query Table.


The Blue cell is the cell with the Dynamic Array Function. The rest of the cells are empty.

 

~ Think Differently About Sorting Excel Data – Dynamic Sorting Excel 365 ~

It is time to rethink how you sort in Excel.  You can sort a Table, or you can sort in a Function.

  1. How you sort data in an Excel Table is how people have been doing it for years.  They either use a macro, or they do so manually, via the Data Ribbon or Header Row.
    1. You can also sort the data in a Table, in Power Query, if it is a Power Query Table.
  2. How people sort data in a Data Range is via one of four dynamic array functions;  Sort, SortBy, GroupBy and PivotBy.

 

Are you sorting data in an Excel Table

If you are sorting the data in an Excel Table, you can use the Sort Buttons on the Data Ribbon, you can use VBA, or you can sort via the Filter Option in the Header Row of an Excel Table.

If your Table is a Power Query Table, you can set the sort choices in the actual Query, and then also on sheet, just as above.  Power Query always increases your options.

 

Are you Sorting Data in via a Dynamic Array Excel Function?

If you are writing Excel formulas, you have four to choose from that have Sort Options.  Two are the SORT and SORTBY, Sort functions.  Then there are PIVOTBY and GROUPBY, both functions have built-in Sort options (Combined in one attribute).

  • You can use SORT and SORTBY with most functions.  Combining functions is where the real power comes into play.

 

Dynamic Sorting Excel 365 Blog Post: Image of SORT Function being used against an Excel Table.

Users Note:  With these functions, and with a drop-down list, the user can select which column(s) to sort by, and in what order, without touching the formula, or the ribbon.  It is in the formula.

 

The SORT Function is often used to populate a Validation Control Drop-Down List, as in the image below.

 

Dynamic Sorting Excel 365 Blog Post: Image of SORT being used with UNIQUE.


This dynamic array function will be used to populate a Data Validation Drop-Down List.

 

 

It is Time to Think Differently About how you Program Microsoft Excel

Tables, Power Query, Pivots, and Dynamic Array Functions give the programmer so much power.  It is time to build fully integrated and automated solutions in Microsoft Excel.  It is time to think in terms of Ranges, and not Cells.  Focus on dynamic solutions that are not only easy to use, but that are also intuitive to use.

 

If you need help programming Microsoft Excel, please give us a call

877-392-3539

 

 

 

Manually Sorting Excel Tables – Dynamic Sorting Excel 365

In the image below, the user has selected a cell in the green Power Query Table.  If you want to sort the Table, click one of the options on the Data Ribbon.  You can also set Sort via a Filter in the Header Row.

Pretty easy.  And you can sort via a macro if you wish.

But please note, this Table is a Power Query Table, Power Query Output.  We can simply sort the Table in Power Query, and it will always be sorted that way, unless we manually change it.  But if we do, simply hit RefreshAll, and it as it once was.  Power Query Tables are read-only, refreshable.

 

Dynamic Sorting Excel 365 Blog Post: Image of SORT options on the Data Ribbon.


Power Query has many sort options, but you can also sort the Power Query Table via the Data Ribbon in Excel.

 

 

Excel Dynamic Array SORT Function

In the image below, this is the most basic use of the new Excel SORT Function.  Sort the entire Table, by the first column.  Done.

 

Dynamic Sorting Excel 365 Blog Post: Image of most basic SORT Function.


The SORT Function can be this easy.

 

 

In the image below we are using the SORT Function, but this time we have allowed the user to designate which column to sort by, and in what sort order, via the two drop-down lists.   User friendly.

Dynamic Sorting Excel 365 Blog Post: Image of more advanced use of the SORT Function.


Data Validation Drop-Down Lists are a great way to allow the user to interact with the file.

 

 

Excel Dynamic Array SORTBY Function

If you want to sort one array by another array, this is the function to use.

You can also do multiple sorts in this function.  This is almost as powerful as sorting in Power Query.

 

Dynamic Sorting Excel 365 Blog Post: Image of the new Excel 365 SORTBY Function being used.


The SORTBY Function gives the user choices in terms of which column you sort by. And you can do so in a function.

 

 

 

Excel GROUPBY Function

The new GroupBy Dynamic Array Function has a Sort built-in.

Interestingly, and the reason I am writing this post, is that two of the new Dynamic Array Functions have sort built-in, in one attribute, not two.  They merged two attributes into one, that was smart.

 

Dynamic Sorting Excel 365 Blog Post: Image of the new Excel GROUPBY Function. It has SORT built in.


Is this the future of “Sort” in Excel?

 

 

 

 

 

Microsoft Excel Dynamic Array PIVOTBY Function

This is one of the latest and most power functions.  It too has a built in Sort Option, just like the GroupBy Function.  Seems that merging the sort column and the sort order in the same attribute might be the way of the future.  That is much different than either Sort or SortBy.

 

Dynamic Sorting Excel 365 Blog Post: Image of the new Excel 365 PIVOTBY Function. This too has the SORT built in.

 

 

 

Dynamic Sorting Excel 365 – Power Query Sort Options

Power Query allows you to sort on one, or multiple columns.  The Waterfall Report for example, has numerous sorts, one column after another.  If you place the Query to a Table, then you can also sort this on the sheet, just like an Excel Table.  Power Query always gives you more options.

 

Dynamic Sorting Excel 365 Blog Post: Image of Power Query Editor, using SORT.


If you are not using Power Query, you were not shown how to use it, or you are not a programmer. Because once you see how easy it is to use, you will use it all the time.

 

 

 

VBA Sorting Options

In the past, prior to 2018, many Excel programmers used vba to sort their data.  It was, and still is, very common.  But just like the use of Index/Match, there are newer, better alternatives.

In your solutions, if you use the newer functions, Tables, and Power Query, you will find yourself not relying on VBA as much.  Less VBA is better, just as fewer Functions is better.  Less is more.

 

 

 

 

Conclusion – Dynamic Sorting Excel 365

In 2018 Microsoft Excel “changed”, and with it came new ways to sort your data.  You can sort a Table/Range, or you can Sort in a Function.  You can also sort in Power Query, on the Data Ribbon, or n a Filter.  So many sorting options.  Learn them all, then use what works best for the situation.

 

 

 

Contact Us – we offer Excel Programming Services

877-392-3539

See us on LinkedIn