More than 750 million people use Microsoft Excel for its slick and futuristic design language. You can easily create your particular charts or pivot table using this program.
Suppose you are a teacher and want to create groups among your students. If so, you can use the randomize list feature from Excel. After completing the list, you can select a bunch of students from the random sheet and make a group.
But there are several methods in Excel with which you can create a random list. Therefore, I researched this topic profoundly and sorted out 3 simple yet functioning ways for your aid.
So don’t skip any part of this article if you want to know the easiest way to randomize an Excel list.
Let’s start!
Where Should I Use a Randomized List in MS Excel?
When creating an office work schedule or worker’s duty timetable, you can use this randomize list feature from Microsoft Excel. Also, if you are a teacher or lecturer, you can use this random list to create a student group for lab work or assignments.
Basically, when you want to build a randomized list in the Excel app, you first need to import your specific names into the cells. After shuffling those names or data, you will get a perfect randomized list.
But there is no default function in MS Excel with which you can create a random list.
Therefore you need to use the Formula to build a random list. As the name suggests, using a formula is not that hard.
Let’s find out how you can quickly make a random list in Excel in the following heading.
Also, check out our separate post on Microsoft Excel Freezing or Slow.
How to Randomize a List in Microsoft Excel
If you want to create a randomized list in MS Excel, you can use the RAND(Random) formula. Also, you can use the SORTBY and RANDBETWEEN formulas to build a random list.
Let’s find out how you can make a random list in MS Excel.
Here are the methods to create a random list in Microsoft Excel:
1. Use the RAND Formula
The most simple and quick way to form a random list in Excel is to use the RAND formula. Follow the instructions stated below to complete this action.
Here are the steps to use the RAND formula to make a random list in MS Excel:
- Launch the MS Excel program and input your specific names or data into the cells.
- Click on the first cell next to the name cell.
- Type =RAND() and hit the Enter key.
- Select the formula cell Drag Handel(+ icon) and drag it to the end of the row.
- Click the first cell again and Right-click on it.
- Choose the Sort > Sort Smallest to Largest or Sort Largest to Smallest options.
- Select the entire formula row and click on the Delete button.
- Save the newly formed random list.
After clicking on the Sort option, you will notice a new random list is formed.
2. Apply the SORTBY Formula
In the newly released MS Excel versions, such as 365 or 2021, the SORTBY formula is available. With the help of this formula, you can easily create a randomized list. For me, the Formula is :
=SORTBY(A1:A14,RANDARRAY(ROW(A1:A14)))
Here are the steps to apply the SORTBY formula to build the random list in MS Excel:
- Open the Excel application.
- Click on the starting cell beside the data cells.
- Write =SORTBY( and select the entire given cells in the array space and place a comma.
- Type RANDARRAY and select it from the down option menu while typing.
- Write ROW and select it while writing.
- Highlight and select the entire previous cells in the ROW section. Example: =SORTBY(A1:A14,RANDARRAY(ROW(A1:A14)))
- Hit the Enter key.
- Select the newly formed random list and Right click on it.
- Choose the Copy option.
- Right-click on the selected cells again and choose the Paste Special option.
- Select the Values option from the Paste section and hit the OK box.
With the help of these simple steps, you can create a random list on MS Excel. But this list will change automatically whenever you make any changes to the Excel sheet. That’s why you must Copy and Paste Values into the formula row to avoid any more changes to the random list.
Check our epic guide on how to Freeze Panes In Excel Easily.
3. Use the RANDBETWEEN Formula
Like the RAND formula that I used in the previous segment, you can also use the RANDBETWEEN formula to form a random list on Microsoft Excel. Let’s sort out how to complete that operation.
Here are the steps to use the RANDBETWEEN formula in Excel:
- Select the adjacent first cell from the MS Excel sheet.
- Type =RANDBETWEEN( and select the desired row number limit, I choose my row numbers 1 to 14.
- Complete the bracket and hit the Enter key; my formula is =RANDBETWEEN(1,14)
- Click on the formula cell’s Drag Handel(+ icon) and pull it to the end of the row.
- Select the entire chart and Right click on it.
- Select the Sort > Sort Smallest to Largest or Sort Largest to Smallest menu.
- Save the changed list.
When you select the Sort menu, immediately, a random list appears. Read along the next heading if you want to know how to create a random list without duplicates in Excel.
Here’s a complete guide on how to Lock a Cell in Excel.
How to Randomize a List in MS Excel without Duplicates
To create a random list in Microsoft Excel without any duplicates, you can use the RANDBETWEEN formula. Let’s see how to write this formula correctly to make a random list.
Here are the steps to use the RANDBETWEEN formula to build a random list without duplicates:
- Click on the adjacent first cell from the Microsoft Excel spreadsheet.
- Write =RANDBETWEEN( and Highlight the specific row number limit, for me, it is 1 to 14.
- Place a bracket and press the Enter button; I write =RANDBETWEEN(1,14) in the formula cell.
- Select the formula cell’s Handel(+ icon) and drag it to the end.
- Highlight the whole chart and Right-click on it.
- Choose the Sort > Sort Smallest to Largest or Sort > Sort Largest to Smallest options.
Instantly you can see that a random list is created. You must Copy that list and Save it for further use. Also, you can form various groups with that list if you want.
Related guide: how to Freeze A Row On Excel Easily
Frequently Asked Questions(FAQs)
How do I shuffle a list in Microsoft Excel?
After creating a random list in MS Excel, press the F9 key to shuffle the list until you find the perfect match.
Can you randomize data in MS Excel?
Yes, you can randomize data in Microsoft Excel. You just need to use the RAND formula to create a random list, then press the F9 button to shuffle that list.
Can you scramble data in Microsoft Excel?
Yes, you can scramble data in MS Excel with the help of the SCRAMBLE formula. Select the cell, type =SCRAMBLE(cell number) and press the Enter button to scramble its data.
Bottom Line
Some essential features and formulas from Microsoft Excel can make our life much easier.
Creating a random list on MS Excel is an important feature for several users. You can quickly make a random list with the help of the RANDBETWEEN and SORTBY formulas. But the most straightforward way to complete this action is the RAND formula.
This article contains all those stated formulas and their easy operational methods, and if you read it properly, you already know that.
You can ask your related questions in the comment section.
