You are reading the article How To Shuffle A List Of Items/Names In Excel? 2 Easy Formulas! updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Shuffle A List Of Items/Names In Excel? 2 Easy Formulas!
If you have a list of items or names or numbers, you can use simple formulas in Excel to randomize this list.
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!
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:
=SORTBY(A2:A15,RANDARRAY(COUNTA(A2:A15)))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 2023, 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.
Also read: Separating First and Last Names in ExcelIn case you don’t have access to SORTBY and RANDARRAY functions, 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:
In the adjacent column, enter the following formula: =RAND()
Copy and paste the formula to all the adjacent cells in column B
Select the dataset (including names and the numbers in column B)
In the Sort dialog box, select ‘Random Number’ from the Sort by drop-down
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 2023, 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:
You're reading How To Shuffle A List Of Items/Names In Excel? 2 Easy Formulas!
Update the detailed information about How To Shuffle A List Of Items/Names In Excel? 2 Easy Formulas! on the Dacvumuahe.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!