Introduction
There are times when you will want to adjust how Microsoft Excel displays the information. From time to time, you might want to randomize the order of a list. Whether you want to take an email list and select a random winner for a contest or you have another reason for randomizing the list, there are several ways you can go about doing this. You need to know how to randomize a list in Excel.
How to Randomize a List in Excel Using a Formula
When it comes to using Microsoft Excel, there is a formula for just about everything in the program. And best of all, if there isn’t currently a formula for your particular needs you can always create one. Thankfully, you won’t need to create a formula to learn how to randomize a list in Excel. So whether you have entered in formulas into Excel in the past or this is your first time, all you need to do is follow these “how to randomize a list in Excel” instructions.
Creating the Randomized List
Open up the spreadsheet you want to randomize. Now, create a new column next to the names or any other column or row you want to randomize. If you have just a single column or row in the spreadsheet, then you don’t need to perform this step.
In the first cell of the new column, type in the RAND formula of “=RAND()” (without the quotation marks). You now will need to copy the code and paste it down along the area you want to randomize. You don’t need to type the same information throughout (which can be a major hassle if you have hundreds of cells you need to randomize). Instead, double-click the fill handle.
Now, you can sort the column to randomize the information displayed. You can do this by clicking on the “Home” tab and then choosing “Editing Group.” From there, you’ll want to choose “Sort & Filter.” A series of options will appear through a pop-up menu. This includes “Sort Smallest to Largest,” “Sort Largest to Smallest,” and “Custom Sort…” which allows you to adjust how you sort the information.
You can also sort by clicking the “Data” tab, then choose “Sort & Filter,” and select the ZA button (or click it back to go AZ).
One of the benefits of using the RAND function is that every single time you enter the information you will be given a different result. RAND will set different number functions for each of the current cells containing information. As each cell has randomly generated numbers (which Excel is then able to sort through based on largest or smallest), you can return to the RAND formula and continually click the “Sort” button and you will receive new results every time.
Shuffling Data with Ultimate Suite
You may not have the time or the patience to continually sift through formulas and other content within Excel. This is especially the case if you have hundreds of entries you need to randomize. When this is the case and you want to know how to randomize a list in Excel, you can shuffle the data within the application using what is known as the Ultimate Suite. This form of sorting doesn’t take much time to implement.
Initiating the Shuffle
To set up the shuffle click the “Ablebits Tool” tab, and then select “Utilities.” From there you’ll want to select the “Randomize” button. This will bring up two options. The first is “Shuffle Cells,” and the second is “Select Randomly.” For this purpose, you’ll want to choose the “Shuffle Cells.”
The shuffle information will load on the left side of your application screen. You will be given a handful of options for how you want to shuffle the information. The methods for shuffling are as follows:
- Cells in each row (this shuffles the cells in each row individually)
- Cells in each column (this randomly sorts out cells in each column)
- Entire rows (this will shuffle rows in a range you select)
- Entire column (this shuffles columns in a range you select)
- All cells in the range
After selecting the shuffle method you are interested in, click the “Shuffle” button and the new output will be displayed for you.
Save Ahead of Time
It is highly recommended to save your spreadsheet before you continue with this. This is because when you begin to randomize some information, it may only randomize certain rows or certain columns. When this happens, it breaks up sections of information you might want to keep together, such as a full row.
So, save your work, and then, when you randomize the information, save it as a different file. This way, you always have a fallback document should you find some information is no longer in the correct location.
Tips and Other Useful Information
Before you go about following these steps for how to randomize a list in Excel, you will want to make sure to read through these tips and suggestions. This will help ensure you always have the highest quality experience whenever using the shuffle features within Excel (or using the application).
Check after the First Shuffle
It is easy to go kind of crazy with the shuffle feature. You might select the shuffle feature once and then click it again and again and again. You will receive different results every time, but you might also be throwing the rest of your spreadsheet out of whack. The RAND shuffle option is the best at avoiding this problem; but even then you want to shuffle information and then check the details after the first shuffle.
By looking at the information after the first shuffle you can see whether all the information you need to remain together stayed together. After all, if you are shuffling student names as a teacher, you don’t want to accidently connect the grades of a different child, or combine last names. Attempting to undo this is a bit of a nightmare.
Instead, you want to save before you perform the shuffle, then check to see what happened with the information following the shuffle. As long as everything remained locked together as it should, you can continue with future shuffles; but again, save the file as a different file just in case you need to go back to the original document.
Selecting Entire Rows and Columns
There might be times when you are using the shuffle features and you just want to shuffle an individual row or column. Attempting to click and drag over the desired areas can get messy (and you may accidentally move one number over to another cell without even knowing what you did). To make sure you have a nice and neat selection, it is better to use a keyboard shortcut.
First, click on the first set of sequences you want to select. Now, hold down the CTRL and the SHIFT key. From there, use the arrow keypads in order to select the area you want have highlighted. This way you won’t be clicking around areas and risk moving data.
Another way to perform this is to select the cell you want to begin with, then hold down the CTRL and the SHIFT button, then push the * button. This will bring up the ability to type in the data set points for what you want to select.
Display Formulas
If you want to view the formulas you have entered into Excel (including the RAND formula), there are a few ways to do this. First, you can click the “Formulas” tab at the top of the screen. This will provide you with all of the information you need.
From there, you want to choose “Show Formulas” button. Of course, you are always able to remove the RAND formula if you want it out. This way, by removing the RAND formula you won’t be assigning randomized numbers to different cells.
Freeze Rows and Columns
There might be a time when you want to avoid moving certain rows and columns within your Excel worksheet. You can do this by clicking the “View” tab at the top of the screen. From there, choose “Freeze Panes.”
You have three options for what to freeze. You can choose “Freeze Pans” where you will select the panes you want to lock in position. Then there is the “Freeze Top Row” option and the “Freeze First Column” option.
Conclusion
There may be times when you want to view a list randomly in Microsoft Excel. Whatever the reason may be, entering a formula is simple, and it doesn’t take long to do. By following these “how to randomize a list in Excel” instructions, you can view the spreadsheet in random ways. So the next time you need to randomize your list, you’ll have all the information you need right here.