Data clean-up techniques in Excel: Randomizing the rows

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.

Download sample file to work with Randomizing the rows in Excel

  • 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 Insert command, a new column will be created. Sometimes you may get Insert dialog box, select radio button of Entire Column clicking the Radio Button behind it.
Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. Creating a new column after column A.

Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. Insert Dialog box.

  • Select cell B4 and enter this formula “=RAND()”.
Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. Entering the formula in cell B4.

  • 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.
Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. A random number(<1) is displayed in cell B4. We have selected cell B4 again.

  • 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.
Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. Cells are filled with random numbers.

  • 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 the column A only.
Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. All the columns have been changed randomly.

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

Download this file to work with single column

  • 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.
Data clean-up techniques in Excel: Randomizing the rows

Randomizing the rows in Excel. We are using here a single column to change it randomly.

Happy Excelling !!!

Read More…

Data clean-up techniques in Excel: Replacing or removing text in cells

Data clean-up techniques in Excel: Matching text in a list


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 here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply