Excel is a best-in-class spreadsheet program featuring powerful formulas and functions that allow users to perform complex calculations and data analysis. It offers plenty of sorting options, including the ability to sort data in ascending or descending order by color, font style, and much more.
However, there are a few scenarios where you may wish to sort the dataset in a completely random order.
Here are a few examples,
- Sorting student names in random order for group studies or games
- Picking up participants randomly for an event
- Selecting a winner for a giveaway
Such tasks are done every day, so Excel has come up with various functions to randomize lists.
Welcome to this guide, where we will discuss several methods to randomize list in Excel. These methods include using built-in features and functions that may help you now and in the future, so make sure to read the article to the end.
Download the Example Excel Sheet
Before we dive deep into each method to randomize a list in Excel, consider downloading the following spreadsheet and make sure you follow me as we dive into this article.
It includes worksheets used to demonstrate various features and functions to randomize a list in the upcoming sections. If you have your own dataset ready to practice the things we are about to discuss, then skip downloading the above file.
Now, without any further ado, let’s begin.
How to Randomize a List in Excel
There are various methods to randomize a list in Excel. We will start with the quickest one and then move on to those that require knowledge of multiple formulas.
Note that you don’t need any expert knowledge to try any of the methods discussed below.
Consider the following dataset, which includes the names of Giveaway Participants. Our task is to randomize the given names and select the winner.
As you can see in the above image, the names are available across column A. For the sake of demonstration, we are randomizing only 20 entries, but note that you can randomize thousands of names at a time.
METHOD #1 – Using the RAND Function
Excel lacks built-in features to randomize lists compared to other popular spreadsheet applications like Google Sheets.
There is a function to generate random numbers named the RAND function in Excel.
Let us first discuss the function.
Explained: The RAND Function in Excel
The RAND function in Excel is used to generate random numbers. It returns a decimal number between 0 and 1.
The general syntax for the RAND function is as follows,
=RAND()As you can see, it is a simple formula that does not involve arguments. All you need to do is enter the formula and complete the brackets.
Before we proceed, create a separate column to populate the random numbers, as shown in the image below.
Here are the steps to randomize a list in Excel using the RAND function,
- Open your desired Excel spreadsheet
- Select the cell “B2”
- Double-click on it and type “=RAND”
- Select the first option from the popup or press “Tab” key
- Complete the formula with a closing parenthesis “)”
- Press the “Enter” button on your keyboard
That’s it! A random number will appear in cell B2.
Next, we need to apply the formula for the rest of the cells of column B.
- Hover to the lower-rightmost corner of the cell “B2”
- You will notice a “+” icon
- Click on it and drag it to the end of the table
Note that the random numbers are updated automatically every time you open the Excel sheet or perform any calculation within the same workbook.
Our final task is to randomize the list.
Let’s begin.
- Select all the cells from column B
- Go to the “Home” tab from the main menu
- Click on the “Sort & Filter” dropdown in the “Editing” group
- Select “Largest to Smallest” from the list
- In the “Sort Warning” dialog box, make sure the radio button is ticked before the “Expand the selection” option
- Click on the “Sort” button as shown in the following image
For the complete steps to randomize a list in Excel using the RAND function, refer to the following GIF.
This was one of the quickest ways to randomize a list. Now, let’s move on to other methods to do the same.
METHOD #2 – Using the RANDBETWEEN Function
This method is similar to the one discussed above. We just need to use the RANDBETWEEN function instead of the RAND function to generate the random numbers.
So, you may be asking, what is the difference between the RAND and RANDBETWEEN functions?
Let us first understand the same.
Explained: The RANDBETWEEN Function in Excel
The RANDBETWEEN function in Excel generates random numbers within the range specified by users. It returns only integer values.
The General Syntax for the function is as follows,
=RANDBETWEEN(bottom, top)Where the “bottom” argument needs to be replaced with the lowest number and the “top” argument with the highest number.
Let us refer to the same example data used in the previous section. Our task is to randomize the list and get the giveaway winner. Refer to the following image,
Now, let’s begin the actual steps,
- Open the desired Excel spreadsheet
- Click on the cell “B2”
- Type “=RANDBETWEEN”
- Select the first option from the popup or press “Tab” key
- Type “1” in place of the “bottom” argument
- Press “,” on your keyboard to move to the next argument of the RANDBETWEEN function
- Enter “1000” in place of the “top” argument
- End the formula with a closing parenthesis “)”
- Press the “Enter” on your keyboard
- Hover to the lower-rightmost corner of the cell “B5”
- Double-click on the fill handle to apply the formula to the remaining cells
- Next, go to the “Home” tab from the main menu
- Click on the “Sort & Filter” dropdown in the “Editing” group
- Select the “Sort Largest to Smallest” option from the list
- A “Sort Warning” dialog box will appear on the screen
- Make sure the radio button before the “Expand the selection” option is ticked
- Finally, click the “Sort” button as shown below
Excel will instantly sort all the names randomly in the list. Yes, you can use both the RAND and RANDBETWEEN functions in a similar way to randomize lists in Excel.
METHOD #3 – Using the RANDARRAY, SORTBY and ROWS Functions
Compared to the first two methods, this one requires a complex formula created by combining multiple functions in Excel.
- RANDARRAY
- SORTBY
- ROWS
Before diving into the actual steps, let’s learn the usage and general syntax of the above functions.
Explained: The RANDARRAY Function in Excel
The RANDARRAY Function is an array function that returns an array of random numbers between the two numbers specified by the users.
The syntax for the RANDARRAY function is as follows,
=RANDARRAY([rows],[columns],[min],[max],[whole_numbers])Each argument of the above function needs to be replaced with the proper information.
- “rows” – It is an optional argument that defines the number of rows to fill with random numbers
- “columns” – This is also an optional used to specify the number of columns to fill with random numbers
- “min” – It allows you to define the minimum number to generate (By default it is 0)
- “max” – Similar to the previous argument, it lets you define the maximum number to generate (By default it is 1)
- “whole_number” – It is used to define the type of numbers you want to generate. If you wish to create whole numbers, then type “TRUE,” and if you’re going to generate decimal numbers, type “FALSE”
All of the above arguments are optional. But make sure you define at least the first two for proper results.
Explained: The SORTBY Function in Excel
The SORTBY function in Excel is used to sort the contents of an array or range based on the values from another array or range.
This is a handy function for sorting data based on external criteria. The general syntax for the SORTBY function is as follows,
=SORTBY(array, by_array, [sort_order], [array/order],...)Where,
- “array” – It needs to be replaced with the range or array of data that is to be sorted
- “by_array” – Here, you can define the range or array containing the values by which you wish to sort the range specified in the first argument
- “sort_order” – It is an optional argument that defines the sort order. You need to type “1” for ascending and “0” for descending sort
- “array/order” – This is also an optional argument that defines additional array and sort order pairs
Explained: The ROWS Function in Excel
The ROWS function in Excel returns the number of rows in a given array or range. It is a very straightforward formula used for counting rows.
The function is handy when working with dynamic ranges. The general syntax for the ROW function is as follows,
=ROWS(array)Where the “array” argument needs to be replaced with the range or array across which you wish to count the number of rows.
Now that we have learned all the functions let’s combine them to create a formula capable of randomizing a list in Excel. We will consider the same dataset, including giveaway participant names.
Our task is to randomize the list and get the winner.
Let’s begin,
- Click on the cell “B2“
- Type “=SORTBY”
- Select the first option from the popup or press “Tab” key
- Provide the range as “A2:A21” for the “array” argument
- Press “,” to move to the next argument for the SORTBY function
- Type “RANDARRAY“
(As the RANDARRAY is a function in Excel, you will see a popup as shown below) - Select the first option from the popup
- In place of the “rows” argument of the RANDARRAY function, type “=ROWS”
(Here, we are creating an expression using the ROWS function. It will generate a random number for the cell range defined in the RANDBETWEEN function) - Select the first option from the popup
(As the ROWS is a function in Excel, you will see a popup as shown below) - In place of the “array” argument of the ROWS function, select the cell range “A2:A21”
- Close the bracket for the ROWS function using “)“
- Close the bracket for the RANDARRAY function using “)“
- End the formula with a closing parenthesis “)”
- Press the “Enter” button on your keyboard
The final formula is as follows,
=SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21)))As you can see in the above GIF, after typing the formula, Excel instantly sorts the participants’ names in random order.
Note that this is a volatile formula due to the use of the RANDBETWEEN function. This means that if you perform any calculation or even add entries, the sorting will be automatically updated.
For example, let us add “asd” in the “C1“. The list will be randomized automatically.
FAQs
Q. Can you randomize a list in Excel without repeating information?
Yes, of course.
There are many ways to create a randomized list in Excel. You can use built-in functions like the RAND and RANDBETWEEN to get the job done quickly.
We have discussed detailed steps in the above section of this article.
Q. How can you remove duplicate cells in Excel?
It’s really easy to remove duplicate entries in Excel. There are a couple of built-in functions and tools to get unique entries across a given column.
Here’s the one discussed,
- Open the desired Excel worksheet
- Hover to the main menu and click on the “Data” tab
- In the Data Tools group, click “Remove Duplicates”
- A new dialog box will appear on your screen
- Ensure the tick before the column from which you wish to remove the duplicates
- If you have headers, make sure the box before the “My data has headers” option is checked
- Click the “OK” button once done
To Summarize: How To Randomize List In Excel
You may want to randomly assign participants for events, questions for tests, samples from surveys, or winners for a giveaway; the RAND and RANDBETWEEN functions are your best bet.
I hope this article taught you all the tricks of randomizing a list in Excel.
Feel free to comment below if you are still having any issues or are stuck somewhere. Our team looks forward to answering all of your questions.
Also, make sure to explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.