Sometimes you may need to randomize the rows in a column. Say you are a teacher of a school, and you have some naughty students who try to copy the papers of others. You need the same question paper but in different patterns. Then you must have looked for the techniques to randomize your data. In this article, I am going to show you the techniques to randomize rows in your Excel workbook.
In this section, you will find 2 simple and efficient ways to randomize rows in an Excel workbook. Here, I will demonstrate them with proper illustrations. Let’s check them one by one!
1. Randomizing Rows in Excel with Multiple Columns
Let’s say, we have got a dataset of some sales representatives of an organization, their net sales, profits, and the number of customers they got over a certain period of time.
We want to randomize the rows of this dataset. In order to do so, proceed with the following steps.
Steps:
- First of all, we have to create a new column after column B. For this, click the header letter of column C, and the whole C column will be selected.
- Then, right-click and choose the Insert command, and a new column will be created.
- Now, assign a name to the newly created column (i.e. Random Number). Select the first cell (i.e. C5) column and type the following formula into the cell.
The RAND function returns any random number “less than 1” to the cell it has been applied.
- After that, press ENTER, and a random number will be displayed in cell C5. The number is less than 1.
- Select cell C5 again and you will see the Fill Handle tool. To copy this formula to other cells of column C, just click on the Fill Handle, hold your mouse, and drag the tool down the next cells to Autofill the formula until you have reached the last cell of the column. You can also use this technique: Click on cell C5. While clicking cell C5, press SHIFT and use the Navigation Arrow (⇓) to reach the last cell. Cells are selected, now click CTRL+D to Autofill all cells (you have selected) with the formula in cell C5.
- Now, you will see that all the cells in which you have applied the formula have shown random numbers. These random numbers are not constant always. Every time you make any change to the worksheet, the random numbers will change their value.
- Then, select cell C5 again.
- After that, go to the Home tab ⇒ go to the Editing group⇒ click Sort & Filter ⇒ select Sort Smallest to Largest.
- As soon as you click the command, your rows will randomize themselves. It is noticeable that not only column C, but all the columns have also been randomly changed along the rows.
So these are the steps you can follow when you have a dataset with multiple columns and you want to randomize your rows in such a way that all the columns along a row randomize their values.
Read More: How to Randomize List of Names in Excel
2. Randomizing Rows with Single Column in Excel
If you have only one column in your worksheet and you want to randomize the rows of this column, then just follow the steps below to randomize your rows.
Steps:
- The same jobs will be performed in cell C5. Enter the RAND function in cell C5 just like Method 1.
- Now, drag and Autofill the formula ⇒ select the cell ⇒ go to the Home ⇒ then Editing group⇒ click Sort & Filter ⇒ select Sort Smallest to Largest.
- As a result, you will see that the names under “Name” will be changed randomly.
So easy! Isn’t it?
Read More: How to Shuffle Data in Excel
How to Shuffle Rows in Excel
Randomize and shuffle are quite similar in terms of Excel tasks. They are used pretty much interchangeably. You can randomize or shuffle the rows in your Excel sheet by applying the formula. Here, we will see the use of SORTBY, RANDARRAY, COUNTA Functions to shuffle the rows. In order to do so, follow the steps below.
Steps:
- Firstly, create a new column and type the following formula to the first cell of the column.
Here,
- B5:B20= The Array
💡 Formula Breakdown
COUNTA(B5:B20) returns the number of cells in the lookup array (B5:B20)
Output=> 16.
RANDARRAY(16) gives 16 random numbers for the 16 cells.
Finally, SORTBY(B5:B20,RANDARRAY(16))) will shuffle the cells along the rows with the random values.
- Then, press ENTER, and your rows will shuffle themselves.
See! As simple as that.
Read More: How to Shuffle Numbers in Excel
Download Practice Workbook
Conclusion
In this article, I have shown you two different cases to randomize rows in Excel. I hope you find it helpful. If you have any better methods and recommendations, don’t forget to share them in the comment box. For more queries, you can visit our website. Thanks for keeping in touch!
Happy Excelling !!!
No comments:
Post a Comment