I often need to use this when I’m creating tutorials where I need a random set of numbers or items. It could also be useful if you’re creating a random team in your office or classroom. In this tutorial, I will show you three easy ways to quickly shuffle a list of names or items in Excel. So let’s get started!
Randomize List Using SORTBY Formula
Suppose you have a list of names as shown below, and you want to randomize this list. Below is the formula that will do this: I have used the COUNTA function to get the total number of names in the list, which is then used within the RANDARRAY function to generate a list of 14 random numbers (as there are 14 names in the list). This random list of 14 numbers is then used within the SORTBY function to give us the shuffled list of names. In case you want to shuffle this list again, hit the F9 key and the list would shuffle again (this happens because RANDARRAY is a volatile function and refreshes whenever you hit F9 or make a change in the Excel file) Once you have the list of random names you want, you can convert the formula to values so that it doesn’t change again. Note that both ‘SORTBY’ and ‘RANDARRAY’ are new formulas and are only available in Excel for Microsoft 365, Excel 2021, and Excel for the Web. In case you’re using an older version of Excel, you won’t have access to these functions. If that’s the case with you, you can use the method covered next
Randomize List Using RAND Formula + SORT Feature (for Older Excel Versions)
In case you don’t have access to SORTBY and RANDARRAY function, you can randomize the list the old-fashioned way. Below I again have the list of names that I want to shuffle and get a new list. Here are the steps to do this: The above steps would sort the list of names based on the random numbers we generated. In case you want to shuffle the list again, just hit the F9 key. This will force the RAND formula to recalculate and it will give you a new set of random numbers. Now you can sort the list of names based on this new random number dataset and you will have the new shuffled list of names. Once done, you can delete the numbers in column B. These are two simple ways that you can use to shuffle a list of names or items in Excel. If you’re using Excel for Microsoft 365 or Excel 2021, you can use the first method where I use the SORTBY and RANDARRAY function. And in case you are using older versions of Excel, then you can use the second method where I use the RAND function. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Generate Random Numbers in Excel Random Group Generator Template [FREE Download] How to Generate Unique Random Numbers in Excel (No Duplicates) How to Sort by the Last Name in Excel (Easy Guide) How to Combine First and Last Name in Excel Extract Last Name in Excel (5 Easy Ways) How to Do Factorial (!) in Excel (FACT function) How to Switch First and Last Name in Excel with Comma?
