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 use RAND function to generate random values. Then we can use the SORT function to sort data by the helper column. This works, but it clutters up the worksheet. This is a situation where it makes sense to use the SORTBY function, which can use arrays that don’t exist in the source data to sort. The trick is to use the new RANDARRAY function to generate the random values we need for sorting. I’ll first remove the SORT formula and helper column, and let’s try again. This time, I’ll use the SORTBY function. For array, we want the source data. For the by_array1 argument, we’re 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 the rows argument. We need the same number of rows that we have in the source data, and we can easily get this with the ROWS function. 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. These values are delivered in an array directly to the SORTBY function, which uses the random values to sort the data. One thing you should be aware of is this result is dynamic, and will continue to update with each worksheet change. For example, if I enter some data in the worksheet, RANDARRAY generates new values each time I press enter, which causes SORTBY to re-sort the data. Finally, I want to mention that you could use the COUNTA function instead of the ROWS function here. However, be aware that if there are empty cells in the data, you’ll get an error, because the SORTBY array will no longer be the correct size. The size of the SORTBY array needs to correspond to the size of the source data.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
