How to random sort in Excel using dynamic array functions

by | Jul 2, 2019 | Excel Tips & Tricks

random sort in excel

Excel doesn't have a built-in method to sort data randomly. Instead, you need to rely on formulas to sort a list of values randomly. Before dynamic array formulas, you needed to build a structure of formulas and helper columns to achieve this. Thanks to the dynamic array functions, now we have the ability to simplify this process substantially. In this article, we are going to show you how to random sort in Excel using the RANDARRAY, and SORTBY dynamic array functions.

The Dynamic Array is a new concept of how Excel handles the arrays. It was announced on September 2018, and so far has been released for Office 365 subscribers only. A dynamic array function can populate an array in a range of cells based on a formula. This behavior is called spilling and can help overcome the limitations of array formulas.

Download Workbook

Generating Numbers for Random Sort in Excel

The closest thing to a random list in Excel is random number generators (RNG). We can use RNG to generate a random order for our list. Excel has a number of functions that can generate a random number:

Between these three, the RANDARRAY function is best for this particular challenge, because it can generate an array of randomly generated numbers rather than a single number, and we're going to use it with a single parameter to generate an array of random numbers. Enter the cell (value) number of your list for the [rows] parameter.

Tip: You can use COUNTA function to get the number of cells that contain any data. Perfect for the job.

Let's say we have a named range called Name and we want to generate as many random numbers as there are values in the Name range.

RANDARRAY(COUNTA(Name))

Sorting

Since we have a random order, the next step is to sort our list by that order. Once again, we get help from a new function called SORTBY. The SORTBY function can sort values in an array based on values in a corresponding array. We can use the SORTBY function to sort our list based on an array with random generated numbers.

We can get our shuffled list using the Name named range as the first argument (array) and the RNG formula outcome as the second argument (by_array1).

=SORTBY(Name,RANDARRAY(COUNTA(Name)))

Re-shuffling

So far, we've covered how to random sort in Excel. However, this approach relies on formulas, the list order will be updated with each calculation. If you want to keep your shuffled list as it is, you may want to copy the list and paste as value without including the formulas. On the other hand, this also means that you can easily shuffle your list again by pressing F9 key and trigger the calculations.