Exceljet

Quick, clean, and to the point

Sort by random

Excel formula: Sort by random
Generic formula 
=SORTBY(rng,RANDARRAY(COUNTA(rng)))
Explanation 

To sort a list or table in random order, you can use the SORTBY function with the RANDARRAY function. In the example shown, the formula in D5 is:

=SORTBY(data,RANDARRAY(COUNTA(data)))

where "data" is the named range B5:B14. The result is a new random sort order whenever the worksheet is recalculated.

How this formula works

The SORTBY function allows sorting based on one or more "sort by" arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the first 10 letters of the alphabet. To generate 10 random numbers to sort with, we are using the RANDARRAY function together with the COUNTA function like this:

COUNTA counts the values in data, and returns the number 10, which goes into the RANDARRAY function as the rows argument. RANDARRAY returns an array of 10 decimal values like this:

{0.634511231774083;0.802832736299988;0.0143439778447176;0.456799995279187;0.535834928742951;0.993607835061287;0.217641630408339;0.638010821263368;0.917156124714596;0.489852948650118}

This array is provided to the SORTBY function as the by_array1 argument. SORTBY uses the random values to sort the data, and returns the 10 letters into a spill range starting in D5.

Note: RANDARRAY is a volatile function and will recalculate every time the worksheet is changed, causing values to be resorted. To stop values from sorting automatically, you can copy the formulas, then use Paste Special > Values to convert formulas to static values.

This example uses "Dynamic Arrays", a beta feature in Excel currently available through the Office Insiders program. Expected release in 2019.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables