How to Randomize A List of Names In Excel

Question

I have a list of names I wish to randomise in Excel. I will need to do this many times. I did find a way using the =Rand() function but it seems a very long winded way of doing things*. Is there a simpler way? It would be nice if there was a random way to sort the rows in a column similar to A-Z sort. Your advice would be appreciated.

eg

Kind regards

Chris

* https://www.automateexcel.com/how-to/random-sort/

Answer 1

The SORTBY and RANDARRAY functions are not available in Excel 2019. So using a column with =RAND() is the way to go. You have to create that column only once; after that you only need to press F9 to recalculate, then sort.

Answer 2

If you have Microsoft 365 or Office 2021, you can use the following formula in the first target cell:

=SORTBY(name_range,RANDARRAY(COUNTA(name_range)))

For example with names in B2:B11:

=SORTBY(B2:B11,RANDARRAY(COUNTA(B2:B11)))

Answer 3

Thank you. I am using Office 2019. I tried the formula. In the example below it produces an error. Am I doing something wrong?

Kind regards

Chris

Answer 4

Thank you very much for your help. Once it is set up F9 recalculates and works great!

Kind regards

Chris

PS Hopefully in the furure Excel 2019 might be updated to include RANDARRAY functions.

Answer 5

I'm afraid Microsoft won't do that.

The subscription version Microsoft 365 gets regular feature updates, but the buy-once versions such as Office 2019 and Office 2021 don't, they only get bug fixes and security updates.