When you’re working with data in a spreadsheet, it’s often useful to randomize a list. Fortunately, this is quite easy to do. You’ll be working with a built-in function in Excel that helps you randomize even large sets of data. Let’s learn how to randomize a list in Microsoft Excel.
Why Randomize Lists in Microsoft Excel?
Before we begin, it’s useful to consider potential uses for randomizing lists in Excel. Think of these as you approach your own work and projects.
- For research projects. Scientific and other experiments often require random data to help measure outcomes.
- For awards and contests. Simply sorting names or other details alphabetically or numerically won’t produce truly random winners.
- To boost process accuracy. Initial lists may be sorted or stratified by group. Randomizing data eliminates these variables to help maintain a scientific process.
- To remove unfairness and equalize outcomes. The selection of any given input or variable in a random process is equally likely for every variable. This removes the potential for sampling error or human-induced unfairness.
There are countless other advantages, but these encompass the fundamental reasons for randomizing lists. Now, let’s dive in and learn how to randomize any list of data in Excel.
How to Randomize a List in Microsoft Excel
There are a variety of tools online to quickly randomize data that you upload or input. But if your data is already inside an Excel spreadsheet, there’s no need to use any other tools or services to randomize the list.
Imagine that you have a list of ten names. Each one is a member of a team, one of whom will be chosen to win a $500 cash prize. You want to use a spreadsheet to determine the winner.
Before you begin, consider the best way to perform the selection. Sorting the names A to Z would benefit the team member with the first name beginning earliest in the alphabet. Similarly, sorting Z to A would benefit the team member at the end of the alphabet. While this might work out well for Aaron or Zara, it isn’t a fair way to choose a winner. The key here is to select randomly.
Begin by adding a new column immediately to the right of your list of names in Excel. It doesn’t require any particular title, but we’ll call it Sort here in this example.
Next, click into the first empty cell below the title. Here, that’s cell D2. Begin by typing in an = sign. This tells Excel that you’ll be inputting a formula into the selected cell.
To randomize a list, you’ll use Excel’s built-in RAND (Random) function. This is a very simple function that simply returns a value, greater than 0.0 and less than 1.0.
Continue with your formula by typing RAND(). This is your complete random formula:
=RAND()
Hit Enter on your keyboard, and you’ll see Excel return a random numerical value in the cell containing the formula. Click and drag on the lower right corner of cell D2 with your cursor. Copy your formula downward adjacent to the remaining names. Just like that, each one is assigned a random number.
An easy way to complete your selection is now to sort in ascending or descending order. Descending will place the highest random value first. Click on any of the values in column D. Then, on Excel’s Home tab, click on the Sort & Filter dropdown. Then, choose Sort Largest to Smallest.
Excel will sort your list. Notice that the values automatically recalculate each time you perform the sort, meaning that Excel will randomize it again when you click Sort. Here, your winner, through purely random chance, is Mark.
As you can see, it’s easy to quickly and fairly randomize a list in Microsoft Excel.
Lock a Row in Excel