where data is the named range B5:B14. The result is a new random sort order whenever the worksheet is recalculated. 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: 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.
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.
