How to Randomize Rows in Excel (2 Easy Ways)

Sometimes you may need to randomize the rows in a column. Say you are a teacher of a school, 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.


Download Practice Workbook

You can download the practice workbook from the link below.


2 Easy Ways to Randomize Rows in Excel

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 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.

Dataset to Randomize Rows in Excel

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 A. For this, click the header letter of column B, and the whole B 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.
=RAND()

The RAND function returns any random number “less than 1” to the cell it has been applied.

RAND Function to Randomize Rows in Excel

  • 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 the 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.

Randomize Rows with Multiple Columns in Excel

So these are the steps you can follow when you got 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.


2. Randomizing Rows with Single Column

If you have got only one column in your worksheet and you want to randomize the rows of this column, then just follow the steps below for randomizing 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 that the names under “Name” will be changed randomly.

Randomize Rows with Single Column in Excel

So easy! Isn’t it?


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 formula also. 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.
=SORTBY(B5:B20,RANDARRAY(COUNTA(B5:B20)))

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.

Use Functions to Randomize Rows in Excel

See! As simple as that.


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 !!!

Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo