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 papers of others. You need the same question paper but in different patterns. You can use the following techniques to randomize your data.
In our example, we have some names. We are going to randomize the data step by step.
- First of all, we have to create a new column after column A. To do this click the header letter of column B, the whole B column will be selected. Right-click and choose the Insert command, a new column will be created. Sometimes you may get the Insert dialog box, select radio button of Entire Column clicking the Radio Button behind it.
Randomizing the rows in Excel. Insert Dialog box.
- Select cell B4 and enter this formula “=RAND()”.
- Press Enter and a random number will be displayed in cell B4. The number is less than 1. Select cell B4 again. You will see the Fill Handle square box.
- To copy this formula to other cells of column B, just click on the Fill Handle, hold your mouse, drag down until you have reached the cell B132 in our example. Or you can use this technique. Click on cell B4. While clicking cell B4, press Shift and use navigation arrow (⇓) to reach cell B132. Cells are selected, now click CTRL+D to auto-fill all cells (you have selected) with the formula in cell B4.
- Select the cell B4 again. Choose Home ⇒ Editing ⇒ Sort & Filter ⇒ Sort Smallest to Largest. You will find that all the columns (not only A, another column will be changed) have been randomly changed. This was not our target. We wanted to change column A only.
- So one thing we have learned from this processing is that: you have to have only one column in your worksheet to change it randomly. If you have more than one column, and you don’t want to change others, then you have to use the separate worksheet. Use the following sample file to work with a single column.
- The same jobs will be performed in cell B4. Entering RAND() formula in cell B4, selecting cells and auto-fill them with this formula and choosing the command: Home ⇒ Editing ⇒ Sort & Filter ⇒ Sort Smallest to Largest. You will find now that the names under “Name” will be changed randomly.
Happy Excelling !!!