Although Excel offers a wide range of functionalities to filter and sort data. However, it does not have a built-in function to directly randomize the order of rows. This limitation can lead users to search for alternative methods or solutions to achieve their aim.
In this article, we'll explain in detail what a randomized row is and share some ways in which you can randomize rows in Excel.
What are Randomized Rows in Excel?
A randomized row or list in Excel is a column of information that can change order each time you calculate a new cell or restored cell. This feature is very important in Excel as it can help with an unbiased selection of data or provide new ways to analyze data. For instance, you can make use of this feature to assign daily tasks, changing assignees daily at random if you have a database of employees.
Now, let's take you through some of the most common methods by which you can carry out this feature effectively and effortlessly. Continue reading;
How to Randomize Rows in Excel
Before you can opt for the randomization of rows in Excel, your spreadsheet must contain clean and well-organized existing data. Once you have listed columns of data, then you can opt for randomization. Here are well-detailed ways we've prepared for you to make the task easier. We divided this into two methods - method one is a formula for randomization and method two is advanced techniques for randomization.
Method 1: Formulas for randomization (using the RANDARRAY, SORT BY, AND ROWS Formulas)
Using Rand Formula
Rand formula is one of the most common and simple methods used in randomizing rows in Excel. To randomize with the RAND formula, follow the below steps carefully;
Insertion of a new column
The first thing to do is insert a column next to the column you wish to randomize. You can do this by right-clicking on the column letter you wish to randomize and click on Insert. You can skip this step If perhaps there's only one column of information, or you're randomizing the last column on your spreadsheet.
Inputting the RAND formula
Input the RAND formula "=RAND()" in the first cell of the column you inserted. Then copy the formula down the column in every cell next to a listed item. This can be done in three ways; either by copying and pasting the formula, retyping, or double clicking the fill handle, which is the dot located in the bottom right corner of the next cell, then dragging it down to the cell next to the last item on the list. The number will be displayed in each cell where you input the formula.
Sorting column in ascending order
In the column where the number is displayed, where you inserted the RAND formula, click on any cell. Go to the Home tab in the top toolbar and look for the editing group toward the right side. Select the sort and filter drop-down option and click Sort largest to smallest.
Meanwhile, you can do this alternatively. Start by selecting the Data tab in the top toolbar and click on the sort and filter drop-down. From the drop-down, select the ZA button, which contains these letters and an arrow pointing downward.
The two methods above have Excel automatically expand the selection and sort the names in column A and Column B.
Changing the randomization
With the RAND formula, each time a change is made on the worksheets, a new number will be created. To change the current order, navigate to the Data tab and select Sort. You can continue clicking the sort button as many times as you wish- till your requirements are fulfilled.
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 make use of a keyboard shortcut to save, which is ctrl+s or command+s on Mac. Also, you can make use of a special feature to change the numbers to values if you prefer to keep the numbers in the column in case you want to randomize the list in the future.
Using the RANDARRAY, SORTBY and ROWS formulas
This is another common formula often used to randomize rows in Excel. Let's take you through it;
Insert a new column
Like every other formula used for this feature, the first step is inserting 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. You can skip this step and use the next column if there is only one column of information, or if you're randomizing the last column on your spreadsheet.
Insert the formula
In the new column you selected, make use of 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 instance, if you have list items in B1 to B12 but only want to randomize B2 to B10, you can do so by inputting "=SORTBY(2:B10, RANDARRAY (ROWS(B2:10))) as the formula. Doing this will help randomize the selected items in the list. This formula does not create duplicates.
Using the RANDBETWEEN formula
Randomizing rows using the RANDBETWEEN formula requires following the below steps;
Inserting a new column
Yes, you should be familiar with this step as it is common to all formulas that have to do with this feature. Inserting a column next to the column you wish to randomize is required, and you can do this by right-clicking on the column letter you wish to randomize and selecting Insert. You can skip this part and use the next column if there is only one column of information, or if you are randomizing the last column on your spreadsheet.
Insert the formula
Type in the RANDBETWEEN formula in the first cell of the column you inserted - this is also a way to select a range of numbers to use in the randomizing process rather than any number. Presuming you want random numbers between 1 and 10, the formula will be RANDBETWEEN (1, 10).
Copy this formula in every cell down the column next to a listed item. This can be done via copying and pasting, double-clicking the fill handle in the bottom right corner of the next cell, dragging it down to the cell of the last item in the list, or simply retyping. Numbers will appear in each cell where you insert the formula. This formula does not create replications.
Sorting of column
Using this formula can help you sort the list in ascending and descending order. How? Navigate to the top toolbar, select sort, and filter. Then click on either Sort smallest to largest or Sort largest to smallest.
Using CHOOSE and RANDBETWEEN formulas.
Rows can also be randomized using the CHOOSE and RANDBETWEEN formulas, you can go about it this way;
Insert a new column
To insert a new column, right-click on the column letter next to the column you wish to randomize and select Insert from the display menu. You can skip this part and use the next column if only there's one column of information or you're randomizing the last column on your spreadsheet.
Input the formula
Using this formula, you can select an item from a list based on the randomly assigned number of each selected item. This can be done with a full list of selected items. For instance, let's say you have information in B1 to B20, and you want to select an item at random from B7 to B16, The formula you'll apply is =CHOOSE(RANDBETWEEN(1, 10), $C$7, $C$8, $C$9, $C$10, $C$11, $C$12, $C$13, $C$14, $C$15, $C$16.
This can be used for random selection of one item, or you can drag the formula down the column for a longer list. This formula is one of the two formulas that may create duplicates. Thus, you should take into consideration how this formula can affect your work.
Using the INDEX and RANDBETWEEN formulas
Last but not least formula on the list, and you go about it this way;
Insert a new column
Right-click on the column letter next to the column you wish to randomize and click from insert from the display menu. Skip this part and use the next column if only there's one column of information, or if you're randomizing the last column on your spreadsheet.
Input the formula
This is a 2-in-1 formula that enables you to select specific items, apply randomized item numbers from a range, and call specified randomized items to the new column. For instance, let say you have a list in B1 to B20 and Want to apply random numbers to the item so from B4 to B17, the formula you'll apply is =INDEX($C$4:$C$17, RANDBETWEEN (1, 14). and if you want to view the random item assigned number 11, the formula is =INDEX($C$4:C$17, RANDBETWEEN (1,14), 11).
You can view more than one item from the randomized choice by dragging the formula down the column. This formula may also create duplicates. You can put in extra formulas to help avoid duplicates but can make your formula more complicated.
The Best Excellent Alternative-WPS Office
WPS (an acronym for writer, presentation, and spreadsheets formally known as Kingsoft Office) is a comprehensive and cost-effective office suite that provides a wide range of features and tools to make productivity easier.
WPS is the best free office alternative to create, open, edit, and save Microsoft Office files, including Excel( where you can effectively randomize rows effortlessly by following the steps listed above) Word, and PowerPoint, and is fully compatible with Windows and Mac.
Pros of WPS Office suite
Captivating and easy-to-navigate user interface: Users have invariably lauded the visually appealing and intuitive design of WPS Office, making it easy for them to navigate through the software.
Seamless PDF viewing capabilities: Several reviewers have highlighted WPS Office as one of the best apps they have used for viewing PDF files. They found the experience to be seamless and smooth, providing a high-quality PDF viewing experience.
Wide range of preloaded templates for presentations: Many users appreciate that WPS Office presents a diverse selection of pre-loaded templates for presentation slides. This feature not only saves time but also enables users to develop professional-looking presentations with ease.
How to Download WPS Office Suite
Here are the steps involved in downloading and installing WPS software on your PC;
Visit the website https://www.wps.com/ to download the latest version of WPS Office.
Click the free download button.
When the download completes, open the WPS Office inste.exe file from the downloads list in the lower-left corner of the interface.
The WPS office wizard dialog appears. Read and review the WPS office license agreement and privacy policy. Clicking the Install Now button will instantly take you to finish the installation with just one click.
WPS is finally installed on your PC.
Frequently Asked Questions (FAQS)
Can you randomize a list in Excel without repeating information?
Yes, there are many ways you can create a randomized list in Excel without repeating information. Some of the ways are; using the RAND formula or the RANDBETWEEN formula. You can also use the RANDARRAY, SORTBY, or ROWS formulas.
How can you recalculate a randomized list?
You can implement a rand formula to make sure the random number changes each time you recalculate the sheet. Click the sort button until the result you desire is obtained.
How can you remove duplicate cells in Excel?
You can remove duplicate cells by selecting the cells you want to check and clicking home then go to conditional formatting >highlight cell rules>duplicate values. Select the formatting you want to apply to duplicate cells, such as a specific color. Then drag your cursor to data and remove duplicates to check the ones you want to remove and click OK.
Some other common questions are; How do I randomly shuffle rows in Excel? How do I randomly assign numbers to rows in Excel? And how do I select a lot of rows at once?
The answers to these questions are already provided in the article, you can refer back to the article if there is anything you do not get.
Conclusion
The above-listed formulas are simply the best when it comes to randomizing row in Excel and they also work effectively on wps office spreadsheet. Just like every good office suite, WPS is a highly suggested software for every individual seeking to randomize lists in Excel due to its user-friendly interface and robust functionality.
WPS does not only offer seamless Microsoft compatibility, but it also provides developed features such as a wide range of free templates and a built-in PDF converter. Its simplicity and power make it a versatile choice for data management.
