How to Randomize a List in Excel:
Step-By-Step Guide (2024)

Got a list you need to sort in a random order?

While there’s no one-click solution to randomize data in Excel, you can do it in 3 simple steps.

Let me show you how💡

If you want to tag along, please download my sample data workbook here.

How to do a random sort in Excel

Let’s say you want to randomize a list of names in Excel.

Right now, it’s sorted in ascending alphabetical order.

You want the list randomized, so the names are in no particular order.

This is not possible by sorting the names column, since there’s no “shuffle” feature.

But if you add a column with random numbers, you can sort the randomized numbers.

And sorting that column will randomize the other columns in your data set.

Pretty clever, right?😉

Step 1: Add a random number column

Start with inserting a new column somewhere to the right of the names.

It could be in column B, moving the rest of the data 1 single column to the right.

Hard part’s over – the rest is going to be a cakewalk🍰

Step 2: Generate random numbers with the RAND-function

Now, insert random numbers in the new column.

You do that with the RAND-function.

To use the RAND-function, all you have to do is enter its syntax in a cell in the random number column:

=RAND()

That’s it! No arguments whatsoever. Just type the name and empty parenthesis and press Enter – and a random number between 0 and 1 is added to the cell.

Now, copy it down to create a whole column of random numbers that you need to randomize the list.

Combined with sorting, you are now able to shuffle cells in Excel.

Let’s do the last step…

Step 3: Sort the random number column

Sorting the randomized column in any order, will then automatically sort the entire list randomly.

Select the first random number in the column that contains the RAND-function.

Then go to the ‘Data’ tab and click one of the sort buttons to sort in ascending order or descending order.

It doesn’t matter which order you sort in.

And that’s it!

Now, you’ve randomized the list to shuffle data🎉

Don’t be confused that the numbers in column B don’t look sorted after sorting. The RAND-function instantly recalculates, so the random numbers change again after sorting.

Delete random number column

The RAND-function is what is called a ‘volatile function’ which means it keeps recalculating all the time.

So make sure to delete the column with random numbers to return your data set back to normal – but randomly sorted instead of whatever it was before.

That’s it – Now what?

From this tutorial, you just learned how to randomize a list in Excel.

Insert a new column and fill it with random numbers.

Then sort the new column so the rest of the data set is shuffled,

But random lists are only a small part of Microsoft Excel.

Excel can automate calculations, filter data, create professional and auto-updating visuals… And just make your life much easier.

If you want to get started with all that, you should enroll in my 30-minute free Excel training program that adapts to your Excel skill level.

Click here, sign up with your email address and get instant access to the course.

Other resources

There are other ways of creating the random numbers needed to randomly sort your list in Excel.

No method really stands out as being the best but the above is certainly the easiest.

You could replace the RAND-function with the RANDBETWEEN-function. As opposed to RAND, the RANDBETWEEN-function just needs 2 arguments to generate a random number between X and Y.

Or if you’re really into dynamic array functions you could generate random numbers with the new RANDARRAY-function.

Or you could use the SORTBY-function to skip the normal sort feature. The SORTBY-function sorts the list but in a corresponding range instead.