How To Randomize a List in Excel (5 Ways and What It Is)

By Indeed Editorial Team

Published April 8, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

If you work with data or are interested in a profession that works with data, it's important to learn how to use databases and formulas in Excel. Research professionals, marketing professionals or data entry professionals can use functions like randomizing lists in Excel. Researchers may use this feature to analyze various data correlations, while marketing professionals may use this feature with client data to send rewards or target promotions at a random selection of people from their client database. In this article, we explain what a randomized list is and share five ways to randomize a list in Excel.

Related: What Is a Data Set? (With Definition, Components and Types)

What is a randomized list in Excel?

A randomized list in Excel is a column of information that can change order each time you calculate a new cell or refresh cells. This feature can help with an unbiased selection of data or provide new ways to interpret the data. For example, if you have a database of employees, you can use this feature to assign daily tasks at random, changing assignees day-to-day. There are a variety of ways to create a randomized list in Excel.

Related: What Is Data Curation? (With Steps and Benefits)

How to randomize lists in Excel

Before you can randomize a list in Excel, you want a spreadsheet with existing data. Once you have listed columns of data, you can randomize your lists. Here are five ways to randomize lists in Excel:

Using the RAND formula

To randomize a list with the RAND formula, consider the following steps:

1. Insert a new column

Insert a column next to the column you wish to randomize. To do this, right-click on the column letter you wish to randomize and select "Insert". If there's only one column of information, or you're randomizing the last column on your spreadsheet, you can skip this step and use the next column.

2. Input the formula

In the first cell of the column you inserted, type in the RAND formula, which is "=RAND()". Copy this formula down the column in every cell next to a listed item. You can do this by copying and pasting the formula, retyping it or by double-clicking the fill handle, which is the dot that appears in the bottom right corner of the next cell, then drag it down to the cell next to the last item in the list. Numbers appear in each cell where you input the formula. This formula doesn't create duplicates.

Related: Basic Excel Formulas and How To Use Them

3. Sort column in ascending order

In the column with numbers, where you added the RAND formula, click on any cell. In the top toolbar, go to the "Home" tab and find the editing group toward the right side of the toolbar. Click the "Sort and Filter" drop-down option and select "Sort Largest to Smallest".

There's an alternative method for this step. Another option is to click the "Data" tab in the top toolbar and select the "Sort and Filter" drop-down. From the drop-down, select the "ZA" button, which has these letters and an arrow pointing downward. Either method has Excel automatically expand the selection and sort the names in Column A and Column B.

4. Changing the randomization

With the RAND formula, each time the worksheet's recalculated, it creates new numbers. To change the current order, go to the "Data" tab and click "Sort". You can continue pressing the "Sort" button as many times as necessary for your needs.

5. Stop the randomization

If you no longer want to randomize your list, you can delete the formula column and press "Save" in the top toolbar, or use the keyboard shortcut to save, which is "Ctrl+S" or "Command+S". If you prefer to keep the numbers in the column in case you want to randomize the list in the future, you can use the Paste Special feature to change the numbers to values. You can do this by copying the cells, then click "Paste" from the "Home" tab. Select "Paste Special", click the "Values" option and press "OK".

Using the RANDBETWEEN formula

To randomize a list with the RANDBETWEEN formula, consider the following steps:

1. Insert a new column

Insert a column next to the column you wish to randomize. To do this, right-click on the column letter you wish to randomize and select "Insert". If there's only one column of information, or you're randomizing the last column on your spreadsheet, you can skip this step and use the next column.

2. Input the formula

In the first cell of the column you inserted, type in the RANDBETWEEN formula, which is a way to select a range of numbers to use in the randomizing process rather than any number. If you want random numbers between one and 15, the formula is "=RANDBETWEEN(1,15).

Copy this formula down the column in every cell next to a listed item. You can do this by copying and pasting the formula, retyping it or by double-clicking the fill handle in the bottom right corner of the next cell, and dragging it down to the cell next to the last item in the list. Numbers appear in each cell where you input the formula. This formula doesn't create duplicates.

3. Sort the column

With this formula, you can sort the list in ascending or descending order. From the top toolbar, click the "Home" tab. In the editing group, located to the right section of the toolbar, select "Sort and Filter". Click either "Sort smallest to largest" or "Sort largest to smallest".

Using the RANDARRAY, SORTBY and ROWS formulas

To randomize a list using the RANDARRAY, SORTBY or ROWS formulas, follow these steps:

1. Insert a new column

Insert a column next to the column you wish to randomize. To do this, right-click on the column letter you wish to randomize and select "Insert". If there's only one column of information, or you're randomizing the last column on your spreadsheet, you can skip this step and use the next column.

2. Input the formula

In the new column, use all three formulas along with the column letters and row numbers you want randomized, which can be all or a selection of items from the list. For example, if you have list items in A1 to A17 but only want to randomize A2 to A15, you can do so by typing "=SORTBY(B2:B15, RANDARRAY (ROWS (B2:B15)))" as the formula. This randomizes the select items in the list using numbers one to 14 since you selected 14 rows in your list column. This formula doesn't create duplicates.

Using the INDEX and RANDBETWEEN formulas

To randomize a list using the INDEX and RANDBETWEEN formulas, you can follow these steps:

1. Insert a new column

Insert a column next to the column you wish to randomize. To do this, right-click on the column letter you wish to randomize and select "Insert". If there's only one column of information, or you're randomizing the last column on your spreadsheet, you can skip this step and use the next column.

2. Input the formula

Using INDEX and RANDBETWEEN formulas together lets you select specific items, apply randomized numbers from a range, and call specified randomized items to the new column. For example, if you have a list in A1 to A20 and want to apply random numbers to the items from A4 to A17, the formula is "=INDEX($B$4:$B$17, RANDBETWEEN (1,14)). If you want to view the random item assigned number 11, the formula is "=INDEX($B$4:B$17, RANDBETWEEN (1,14), 11)".

If you want to view more than one item from the randomized selection, you can drag the formula down the column. This formula may create duplicates. You can add additional formulas to help avoid duplicates, but it can make your formula more complex.

Related: How To Remove a Duplicate From Excel (With Helpful Instructions)

Randomize a list with CHOOSE and RANDBETWEEN formulas

To randomize a list using the CHOOSE and RANDBETWEEN formulas, you can follow these steps:

1. Insert a new column

Insert a column next to the column you wish to randomize. To do this, right-click on the column letter you wish to randomize and select "Insert". If there's only one column of information, or you're randomizing the last column on your spreadsheet, you can skip this step and use the next column.

2. Input the formula

Using the CHOOSE and RANDBETWEEN formulas together, you can select an item from a list based on the randomly assigned number to each selected item. You can do this with a full list or selected items. For example, if you have information in A1 to A20, and you want to select an item at random from A7 to A16, the formula is "=CHOOSE(RANDBETWEEN(1,10), $B$7, $B$8, $B$9, $B$10, $B$11, $B$12, $B$13, $B$14, $B$15, $B$16)".

You can use this for the random selection of one item, or you can drag the formula down the column for a longer list. This formula may create duplicates. Consider how this affects your work before using this formula.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles