How to Randomize a Column in Microsoft Excel
Randomizing a column in Excel means changing the order of the cells in the column in a random manner. This is useful when you want to create a random order of data, such as when selecting a random sample from a larger data set or shuffling a list of items.
In this tutorial, we will learn how to Randomize a column in Microsoft Excel. There are various techniques available in Microsoft Excel to randomize a column. One of the popular ways is to use a combined formula of SORTBY, RANDARRAY, and COUNTA functions, which helps to shuffle the order of cells in a column randomly.
We have column A in the dataset containing a list of participants for competition and our goal is to randomize the order of this list.
Method 1: Using a Combination of SORTBY, RANDARRAY, and COUNTA Functions
The syntax of the combined formula for randomizing a column becomes:
Step 1 – Select a Blank Cell
- Select a cell in the column where you want to print the randomized column.
Step 2 – Place an Equals Sign
- Place an Equals Sign in the blank cell.
Step 3 – Enter the SORTBY Function
- Enter the SORTBY function right after the equals sign in the cell.
- The SORTBY function takes two arguments i.e. the range to be sorted and the array to use as the sort key.
- Open parentheses.
Step 4 – Enter the Range of Column
- Enter the range of the Column to be randomized.
Step 5 – Enter the RANDARRAY Function
- Enter the RANDARRAY function after placing a comma ( , ) after the range.
- RANDARRAY() creates an array of random numbers with the same length as the number of non-blank cells in the range.
- Open parenthesis.
Step 6 – Enter COUNTA Function
- Nest the COUNTA function in the RANDARRAY function and enter the column range to be customized as its argument i.e. COUNTA(range).
- The COUNTA function counts the number of non-blank cells in the range.
- Close the parenthesis of the COUNTA function.
Step 7 – Close the Parenthesis of the RANDARRAY Function and the SORTBY function
- Close the Parenthesis of the RANDARRAY function.
- Close the Parenthesis of the SORTBY function.
Step 8 – Press the Enter Key
- Press the Enter key.
- The randomized column will be printed in the selected column.
Method 2: Using the RAND() Function
Step 1 – Select a Helper Column
- Select a helper column.
Step 2 – Enter RAND() Function
- Enter the RAND() function in the first cell of the column.
- Press the Enter key.
Step 3 – Use Autofill to Apply RAND() Function
- Use Autofill to apply RAND() function across the column.
Step 4 – Now Select the Helper Column
- Select the Helper column.
Step 5 – Go to the Data Tab and Click on a Sort Option
- Go to the Data tab.
- Click on any of the sort options in the Sort & Filter section i.e. Sort A to Z.
Step 6 – Select the Expand Selection Option
- Select the Expand the selection option in the dialog box that appears.
- Click on OK.
- The column containing the names will be randomized.