How to perform a random sort

In this worksheet, we have the first 10 letters in the alphabet in the range B5:B14.

How can we sort this data in random order?

One way to do this is to add a helper column and useRAND functionto generate random values.

Then we can use theSORT functionto sort data by the helper column.

This works, but it clutters up the worksheet.

The trick is to use the new RANDARRAY function to generate the random values we need for sorting.

Ill first remove the SORT formula and helper column, and lets loop back and try.

This time, Ill use the SORTBY function.

For array, we want the source data.

For theby_array1argument, were going to use the RANDARRAY function.

RANDARRAY generates one or more random values.

It takes several optional arguments, but in this case we only need to provide therowsargument.

This is an example of nesting.

The ROWS function returns the count of rows in the data directly to the RANDARRAY function.

Since there are 10 rows of data, RANDARRAY generates 10 random values.

Finally, I want to mention that you could use the COUNTA function instead of the ROWS function here.

The size of the SORTBY array needs to correspond to the size of the source data.