Catalog

How to Randomize a List in Excel (Step-by-step guide)

August 9, 2023 1.2K views

Randomizing a list may not be as straightforward as it seems. While managing a small number of items can be done manually, handling a large list poses a challenge.

In this article, we examine the key techniques to learn how to Randomize a List in Excel and the various approaches that can be used to complete this task.

Step-by-Step Guide to Randomize a List in Excel

In Excel, you have two effective methods to randomize a list in Excel: through VBA or by utilizing the RAND() function and sorting the data based on random values. To understand how to randomize a list in Excel, we will employ both the RAND() function and the Sort Function in Excel. By combining these techniques, you can easily achieve a randomized list in Excel, introducing an element of unpredictability for various applications.

Using the Sort function

The RAND function generates a random decimal value between 0 and 1. By combining the power of the RAND function and the Sort function, you can easily achieve shuffling your list randomly in Excel.

Consider the following list of 10 Roll Numbers. Let’s shuffle this list using the sort function:

Excel sample data



Step 1: Make a copy of the original list by creating a new column next to it. You can label this new column as "Shuffle Column"

Excel shuffle column



Step 2: In addition to the shuffled list column, apply the RAND() formula in the adjacent cells to generate random values.

Excel RAND formula



Step 3: By doing so, random numbers will be generated in that cell. Then, simply drag down the cell to extend these random values to every cell beside the Shuffled List column.

Excel RAND formula result



Step 4: Then select both the Shuffled list and Random values columns, go to the “Home” tab, find the “Sort and Filter” button in the Editing group to shuffle the list according to the Random value column arrangement.

Excel Sort Function



Step 5: Select "Custom Sort" from the menu. In the following window.

Excel Custom Sort



Step 6: Next, select "Random" as the sorting criteria, and feel free to choose any desired order for the sorting process. Make sure to check the option "My data has headers"

Excel headers


 

You now have a shuffled list that will be randomized in Excel.

Excel randomize a list function



Using a Helper Column with the RAND Function

Let's explore a scenario where we have a list of 10 names arranged in alphabetical order.

Excel helper column



To understand how to randomize this column in Excel, follow the steps below

Step 1: Locate the column containing the list you want to randomize in Excel.

Excel randomizing column



Step 2: In an empty column next to your list, use the formula =RAND() to generate random values for each row. This will generate a random value between 0 to 1 in that cell.

Excel RAND formula



Step 3: Drag down the first cell to populate the rest of the cells.

Excel click and drag method


 

Step 4: With the random values generated, select the entire range (random values).

Excel selecting values



Step 5: On the "Home" tab, select the "Sort & Filter" button under the "Editing" group.

Excel Sort and Filter



Step 6: Select "Sort Largest to Smallest" or "Sort Smallest to Largest" according to your preferences.

Excel “sort & filter”



Step 7: In the Sort Warning window, select the “Expand the Selection” option. Afterward, proceed by clicking the Sort button to arrange both the main list and the accompanying numbers accordingly.

Excel “expand the selection



Step 8: Now that the list has been successfully sorted, you can go ahead and delete the column containing the random values in Excel.

Excel deleting method



That's how simple it is to randomize any list in Excel using a helper column with the RAND() function. Once your list is shuffled, now you can randomize your list in groups in applications like grouping students in teams without any context.

Excel randomization



A Free Alternative to Microsoft Office - WPS Office

Randomizing a list brings excitement as it shuffles contents, yielding completely unpredictable outcomes. Excel, being user-friendly, opens doors to numerous possibilities. Yet, there's a hidden gem you may  have to fully consider—the Randomize function in WPS Spreadsheet.

WPS Office



WPS Spreadsheet is lightweight, ensuring a swift installation and its integrated AI optimizes your workflow. While all of this is great for a Spreadsheet tool, do you know what would take this a notch higher on the productivity scale?  It is completely free. That too, while offering all the features of Excel and no strings attached. So, please download WPS Office

Merge and Split Cells in WPS Office

WPS Spreadsheets, the Excel equivalent in WPS Office, offers a host of powerful features to manipulate data effectively. Among these features are the abilities to merge and split cells.

Merging Cells:

Step 1: Select the cells you want to merge by clicking and dragging your cursor over them.

Excel “merge and split” method



Step 2: Click "Merge and Centre" in the Home Tab as shown below.

Excel “merge and split



Step 3: The selected cells will be combined into a single larger cell, with the content centered within.

Excel combination of cells



Splitting Cells:

Step 1: Select the cell that you want to split or unmerge.

Excel splitting cells



Step 2: In the Home Tab, click on the drop-down arrow of “Merge and Center” and click Unmerge Cells.

merge and center” dropdown menu



Step 3: The selected cells will be split into smaller cells.

Excel splitting of cell sample



Using these two methods, you can easily merge and split cells, making your work easy and fast, and not causing your document to become cluttered. Please choose the method that best suits your needs!

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

Q1. How do I generate 1000 random numbers in Excel?

To generate 1000 random numbers in Excel, you can use the "RAND()" function. In an empty cell, enter the formula =RAND() and press Enter. This will generate a random number between 0 and 1 for that cell. Now, click on the cell containing the random number, and you will notice a small square in the bottom right corner of the cell, known as the fill handle. Click and drag the fill handle down for 999 more rows (to a total of 1000 rows). As you drag, Excel will automatically generate 1000 random numbers in the selected cells.

Q2. How do I generate random numbers between a specific range, such as 0 to 100, in Excel?

To generate random numbers between 0 and 100 in Excel, you can use the formula "=RAND()*100" (for decimal numbers) or "=RANDBETWEEN(0, 100)" (for whole numbers).

Q3. Can I generate random numbers in Excel with a specific decimal precision, such as two decimal places?

Yes, you can generate random numbers with a specific decimal precision in Excel. To achieve this, use the formula "=ROUND(RAND(), 2)" (for two decimal places). The "ROUND()" function will round the random number generated by "RAND()" to the desired number of decimal places, allowing you to control the precision of the generated random values.

Q4. Is there a way to shuffle the order of a list in Excel using VBA (Visual Basic for Applications)?

Yes, you can use VBA to shuffle the order of a list in Excel. By writing a custom macro, you can loop through the list and rearrange the items randomly. VBA provides functions like "Rand()" to generate random numbers and "RandBetween()" to control the range of randomization.

Embrace WPS Office for Enhanced Productivity

In this article, while we explored the art of how to randomize a list in Excel, we also took a look into the many ways we can utilize this feature. We also talked about how WPS Spreadsheet might as well be a better productivity spreadsheet tool than Excel and all for the right reasons. So download WPS Office today and discover the potential it holds!


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.