MS Excel VBA

MS Excel VBA
How to Shuffle a List Items/Names in Excel

How to Shuffle a List Items/Names in Excel? 2 Easy Formulas.

52 Comments
In this tutorial, Excel VBA show you three easy ways to quickly shuffle a list of names or items in Excel.
Randomize List Using SORT BY Formula

Presume you have a list of names, as shown below, and you want to randomize this list.

Name list to be shuffled

Below is the formula that will do this:

SORTBY formula to shuffle the list

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 checklist).

This random list of 14 digits is then used within the SORT BY function to give us the shuffled list of names.

If you want to reshuffle this list, hit the F9 key, and the list will 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 to don’t change again.

 

Randomize List Using RAND Formula + SORT Feature 

If you don’t have access to the SORT BY and RANDARRAY function, you can randomize the list the old-fashioned way.

Below I again have the list of names that I like to shuffle and get a new list.

Name dataset with additional random number column

Here are the steps to do this:

 

  1. In the adjacent columns, enter the following formula: =RAND()
  2. Copy and paste the formula to all the adjacent cells in columns B

RAND function to get random numbers

  1. Select the dataset (including names and the numbers in column B)
  2. Click the Data tab in the ribbon
Click the Data tab
  1. In the Sort and Filter group, click on the ‘Sort’ icon
Click the Sort icon in the ribbon

6. Select ‘Random Number’ from the Sort by drop-down in the Sort dialog box.

Sort by Random Number columnClick OK

Related Posts

52 thoughts on “How to Shuffle a List Items/Names in Excel? 2 Easy Formulas.”

  10. bahis

    If some one wishes expert view regarding blogging and site-building then i advise him/her to go to see this blog, Keep up the good work. Laverne Shupe

    Reply
  14. bitcoin

    Very revealing bless you, I do think your current audience might want a whole lot more content such as this continue the excellent work. Gregory Leonor

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top