Single formula to randomly shuffle an Excel range
Syntax: =\SHUFFLE(range) => array
=LAMBDA(range,
LET(
oc, COLUMNS(range),
r, TOCOL(range),
WRAPROWS(
CHOOSECOLS(
SORT(
HSTACK(
MAP(r,LAMBDA(x,IF(x="",1,RAND()))),
r
),
1
),
2
),
oc
)
)
)
Documentation
Returns the source range or array in random order. Blank cells will always be pushed to the end (last row and column of the output).
This function is based on the Excel RAND() function, and therefore is volatile – the output will change order on each recalculation.
Commentary
A simple but useful function. For a 2-D version, the result is not easily achievable with just RAND() and sorting.