If you work in Excel much, there is probably a time when you needed to include a random number in Excel and used the RAND function or the new fancy RANDARRAY function to do so. And for most folks, the built-in random generator for Excel works fine for their needs. However, Excel uses what is called a pseudorandom number generator, in this case, the Mersenne Twister algorithm. What that means is that, technically speaking, the numbers that Excel’s RAND functions generate aren’t truly random.
Some companies want to get as random numbers as possible and get creative as to where their random data comes from (like CloudFlare’s wall of Lava Lamps). For folks where randomness truly matters, you shouldn’t rely on Excel’s random generator. If true randomness in Excel matters to you (which for 99.9% of the population, it doesn’t, but just in case), you have to get creative. This is where a service like random.org comes in. You can read the history how random.org generates its data. But basically if you’re running a lottery, choosing somebody for random testing, or some other critical order of things, you’re best using random.org as your random number source.
Since there is no built-in way for Excel to pull from random.org, you have to be creative to get the data from there. But it is possible to get a sorting value out of there. Here’s how to do it:
First off, create your list of names or data you need to sort randomly. In our case, it is a list of about 1700 Biblical names that we found online:
Once you create the spreadsheet, you need to create the data for the sort column. Head to the Random Sequence Generator. Once you get there, hit the “Switch to Advanced Mode” button to get all the available fields.
You need to input your value range, set the output format to be just a barebones text document, and then click Get Sequence:
Once that generates, you’ll get a long list of numbers in your browser. Highlight them all (or hit “Ctrl-A” to highlight everything) and then copy and paste them into your Excel document in the “Sort” column:
Select both columns and then use Excel’s sort function to sort it by the “Sort” column:
And you’ll see, Jabal is our winner, with Titus runner up:
Using an external sort like random.org will also keep the data from re-calculating itself, which will happen when using the RAND function in Excel if you don’t copy the data out as plain text somewhere else.
Hat tip to the awesome Office Watch newsletter for this idea.