Organizing data in Excel can be a challenge, especially when you have to maintain the order of a long list of items. Randomizing a list in Excel can help to simplify the process and make sure that you’re able to quickly and accurately reorder the list of items. In this blog post, we’ll explore a few simple tips and tricks to help you randomize a list in Excel – quickly and easily. Whether you’re trying to randomize a list of names, numbers, or other types of data, this guide will provide the steps you need to get the job done quickly and effectively. So if you’re ready to learn how to randomize list in Excel, keep reading.
How to Randomize a List In Excel
What is a randomized list in Excel?
When you calculate a new cell in Excel or refresh the cells, a randomized list is a column of data that can have a different order every time. This feature can aid in the objective selection of data or offer fresh perspectives on how to interpret the data. For instance, you can use this feature to assign daily tasks at random to different assignees each day if you have a database of employees. In Excel, you can generate randomized lists in a number of different ways.
How to randomize lists in Excel
You need an Excel spreadsheet with current data before you can randomly generate a list in Excel. You can randomize your lists once the columns of data have been listed. Here are five ways to randomize lists in Excel:
Using the RAND formula
Consider the following steps to use the RAND formula to randomize a list:
Next to the column you want to randomize, add a new column. Choose “Insert” from the context menu when you want to randomize a column letter. You can skip this step and use the next column if your spreadsheet only has one column of data or if you’re randomizing the last column.
Put the RAND formula, “=RAND(),” in the first cell of the column you added. Add this formula to each cell in the column that is adjacent to a listed item. You can accomplish this by typing the formula again, copying and pasting the formula, or by double-clicking the fill handle, which is the dot that appears in the bottom right corner of the subsequent cell, and dragging it down to the cell next to the last item on the list. Numbers appear in each cell where you input the formula. This formula doesnt create duplicates.
Click on any cell in the column of numbers where you added the RAND formula. Locate the editing group on the right side of the toolbar in the top toolbar’s “Home” tab. Select “Sort Largest to Smallest” from the “Sort and Filter” drop-down menu.
Theres an alternative method for this step. Another choice is to select “Sort and Filter” from the drop-down menu after clicking the “Data” tab in the top toolbar. Choose the “ZA” button from the drop-down menu, which features these letters and a downward-pointing arrow. Either way, Excel expands the selection and sorts the names in Columns A and B automatically.
The RAND formula generates new numbers each time the worksheets are recalculated. Select “Sort” under the “Data” tab to alter the current order. The “Sort” button can be repeatedly pressed as often as is necessary for your needs.
You can remove the formula column from your list and then press “Save” in the top toolbar, or you can use the keyboard shortcut “Ctrl+S” or “Command+S” to save. You can use the Paste Special feature to convert the numbers to values if you prefer to keep the numbers in the column in case you ever want to randomize the list. You can do this by copying the cells, then from the “Home” tab, clicking “Paste.” Select “Paste Special”, click the “Values” option and press “OK”.
Using the RANDBETWEEN formula
Consider the following steps to use the RANDBETWEEN formula to randomize a list:
Next to the column you want to randomize, add a new column. Choose “Insert” from the context menu when you want to randomize a column letter. You can skip this step and use the next column if your spreadsheet only has one column of data or if you’re randomizing the last column.
Enter the RANDBETWEEN formula, which allows you to choose a range of numbers rather than just one to use in the randomizing process, in the first cell of the column you just added. The formula is “=RANDBETWEEN(1,15)” if you want random numbers between one and fifteen.
Add this formula to each cell in the column that is adjacent to a listed item. By copying and pasting the formula, typing it again, or by double-clicking the fill handle in the bottom right corner of the subsequent cell and dragging it down to the cell next to the last item on the list, you can accomplish this. Numbers appear in each cell where you input the formula. This formula doesnt create duplicates.
You can order the list using this formula in either ascending or descending order. From the top toolbar, click the “Home” tab. Choose “Sort and Filter” from the editing group’s menu in the toolbar’s right section. Choose “Sort smallest to largest” or “Sort largest to smallest” by clicking on it.
Using the RANDARRAY, SORTBY and ROWS formulas
Use these steps to randomize a list using the RANDARRAY, SORTBY, or ROWS formulas:
Next to the column you want to randomize, add a new column. Choose “Insert” from the context menu when you want to randomize a column letter. You can skip this step and use the next column if your spreadsheet only has one column of data or if you’re randomizing the last column.
Use all three formulas along with the column letters and row numbers you want to be randomly generated in the new column; these can be all or a subset of the items from the list. For instance, if you only want to randomize the items in A2 to A15 from a list of items in A1 to A17, you can do so by entering the formula “=SORTBY(B2:B15, RANDARRAY (ROWS (B2:B15))” Given that you chose to select 14 rows in your list column, this randomizes the chosen list items using the numbers one to 14. This formula doesnt create duplicates.
Using the INDEX and RANDBETWEEN formulas
These steps can be used to shuffle a list using the INDEX and RANDBETWEEN formulas:
Next to the column you want to randomize, add a new column. Choose “Insert” from the context menu when you want to randomize a column letter. You can skip this step and use the next column if your spreadsheet only has one column of data or if you’re randomizing the last column.
You can choose particular items, use randomized numbers from a range, and call specific randomized items to the new column by combining INDEX and RANDBETWEEN formulas. For instance, the formula is “=INDEX($B$4:$B$17, RANDBETWEEN (1,14)”) if you have a list in A1 to A20 and want to assign random numbers to the items from A4 to A17. The formula is “=INDEX($B$4:B$17, RANDBETWEEN (1,14), 11)” if you want to view the randomly assigned item with the number 11.
Drag the formula down the column to view more than one item from the randomized selection. This formula may create duplicates. To help prevent duplications, you can add more formulas, but doing so may make your formula more complex.
Randomize a list with CHOOSE and RANDBETWEEN formulas
The steps below can be used to RANDBETWEEN and CHOOSE a list for randomization:
Next to the column you want to randomize, add a new column. Choose “Insert” from the context menu when you want to randomize a column letter. You can skip this step and use the next column if your spreadsheet only has one column of data or if you’re randomizing the last column.
You can choose an item from a list using the CHOOSE and RANDBETWEEN formulas combined, using the randomly assigned number to each chosen item. This can be done with a complete list or just certain items. For instance, 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”) if you need to choose an item at random from A7 to A16.
You can use this to choose an item at random from a list, or you can drag the formula down a column to choose an item from a longer list. This formula may create duplicates. Consider how this affects your work before using this formula.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
How do you create a randomizer in Excel?
- Choose the cell where you want the random numbers to appear.
- In the active cell, enter =RANDBETWEEN(1,100).
- Hold the Control key and Press Enter.
How do I randomize a list into a group in Excel?
In the formula bar, enter =RAND() after choosing the range C5:C16 to assign all of the random values in one go. When entering the formula in all cells at once, use the keyboard shortcut control + enter.
