Despite the many ways you can shuffle rows, columns, and cells, there’s no built-in option to random sort in Excel. That can be useful for randomly assigning tasks or responsibilities, selecting samples, and much more.
Instead, you can write a formula that allows you to randomly sort any data you select.
Being able to randomize data in Excel is an incredibly useful skill for many situations. Whether you need to assign tasks randomly pick lottery winners, shuffle a deck of cards or organize a game roster knowing how to do a random sort will save you time and give you more flexibility with your data analysis and presentations.
In this comprehensive guide, I’ll walk you through the easiest ways to randomize lists and shuffle data in Excel using either formulas or convenient built-in tools. By the end, you’ll have a few simple tricks up your sleeve to quickly and easily random sort columns, rows or entire tables with a couple clicks.
Why Do a Random Sort in Excel?
There are many reasons you may need to randomly shuffle or sort your Excel data
- Randomly assigning tasks or shifts at work to avoid bias
- Selecting a random winner for giveaways, raffles or contests
- Shuffling card decks or game pieces for simulations and modeling
- Mixing up learning exercises or test questions so students get different versions
- Organizing randomized trials and experiments in science and research
- Shuffling music playlists so songs play in random order
Without an easy way to random sort in Excel generating a sufficiently randomized and unbiased list can take a lot of manual work. Fortunately Excel provides some simple ways to get the job done quickly.
How to Randomize a List with the RAND Formula
While Excel doesn’t have a built-in function specifically for random sorting, you can use the RAND formula to generate random numbers for each row or column, and then sort your list based on those random values.
Here’s a step-by-step guide to randomize a list using the RAND formula:
- Insert a new column next to the list you want to randomize.
- In the first cell of the new column, enter the formula
=RAND(). - Copy the formula down the entire column by double clicking the fill handle. This fills the new column with random decimal values.
- Highlight the column of random values, go to Data > Sort and Filter > Sort Largest to Smallest. This will shuffle your list based on the random numbers.
The RAND formula generates a new random decimal value each time the spreadsheet calculates. So if you aren’t satisfied with the initial shuffle, you can just keep clicking Sort to reshuffle as many times as needed until you’re happy with the randomization.
If you want to lock in the random order without further changes, simply copy the column of RAND values and paste as values only. This removes the formulas and preserves the existing random sort order.
Shuffle Data Easily with the Ultimate Suite Add-in
If you want an even faster way to random sort without any formulas required, check out the Ultimate Suite for Excel add-in by Ablebits.
This handy tool set includes a Random Generator utility with several options for easily shuffling your Excel data:
- Shuffle cells within each row or column
- Shuffle entire rows or columns
- Shuffle all cells in a selected range
Just select your data, click the Randomize button, choose one of the shuffle options, and click again to instantly reorder as needed.
The Ultimate Suite add-in provides an easy way to randomly sort lists of any size in literally 2 clicks, without needing to build and copy randon number formulas.
It also includes many other useful tools to boost your Excel productivity like removing duplicates, merging sheets, converting text and more. You can install a free trial from Ablebits.com to test it out.
Tips for Effective Random Sorting in Excel
Follow these tips to improve your results when doing a random sort in Excel:
-
Add identifiers – Include an ID column before shuffling so you can match data across multiple columns.
-
Sort larger sets – The RAND formula works best for smaller lists. For larger data sets, use the Ultimate Suite tool.
-
Check distribution – Review the distribution of shuffled values to ensure adequate randomization.
-
Lock random order – Copy and paste RAND results as values to prevent reshuffling upon recalculation.
-
Re-sort as needed – Keep re-sorting until you achieve the randomness you need.
Common Questions About Randomizing Lists in Excel
Here are answers to some frequently asked questions about doing a random sort in Excel:
How do I randomly assign names?
To assign names randomly, add a column with the RAND formula and sort the list by that column. Or use the Ultimate Suite shuffling tool.
Can I shuffle multiple columns together?
Yes, simply ensure the columns are next to each other with no gaps before shuffling. The order of rows will be randomized while keeping data aligned across columns.
How can I lock in the randomized order permanently?
Copy the column of RAND numbers and paste as values only to remove the formulas. This locks in the existing shuffled order.
Is there a way to generate unique random numbers?
Use the UNIQUE and RANDARRAY functions together to create an array of random numbers without duplicates.
Conclusion
Being able to quickly randomize lists and shuffle data is an invaluable skill for any Excel user. Using the built in RAND formula or add-ins like Ultimate Suite, you can easily random sort tables, lists and other data sets with just a couple clicks.
So next time you need to assign tasks impartially, randomly select winners, or shuffle music playlists, keep these simple Excel randomization tricks in mind to save time and get perfect results. Knowing how to do a quick random sort will prove useful again and again!
Sort by Random Values
To sort by the random values, select your Random column, select Sort & Filter on the Home tab, and choose to sort your data in ascending order.
Note that the Sort Warning box may pop up asking if you want to expand the selection. Be sure to click Expand the selection so all the data is sorted.
You can also select the Sort option from the Data ribbon tab, but you may get an additional dialog box that looks like this:
If so, just be sure you check the box that says My list has headers and that Random is the selected column.
Do you know any other easy ways to random sort in Excel? Let us know in the comments!
Sample file
How to Random Sort in Excel Using the RAND Function
Excel’s RAND function is used to generate a random number between zero and one, but we can also use it to shuffle other values.
Let’s say you have a list of names like the one below. You’ll start by creating a Random column to the right of the names.
Now that you have a new column for your random numbers, it’s time to use the RAND function to generate those numbers!
To use the RAND function, simply enter =RAND() into the first cell of your new Random column. This will generate a random number between 0 and 1 for that cell.
Once you’ve generated your first random number, you can easily fill the rest of your Random column with additional random numbers.
Click on the cell containing your first random number (C3), and drag the Excel fill handle (the small square in the bottom right corner of the cell) down to fill the rest of the column with random numbers.
Now you have a column full of random numbers that can be used to sort your data.
