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!

Randomize List Using SORTBY Formula

Suppose 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(A2:A15,RANDARRAY(COUNTA(A2:A15)))
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 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.

Name dataset with additional random number column

Here are the steps to do this:

  1. In the adjacent column, enter the following formula: =RAND()
  2. Copy and paste the formula to all the adjacent cells in column 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
  1. In the Sort dialog box, select ‘Random Number’ from the Sort by drop-down
Sort by Random Number column
  1. Click OK

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster