Randomize a List in Excel Using RANDBETWEEN Function

Randomize a List in Excel Using RANDBETWEEN Function

I am back with yet another excel tip. This one would be a short read, but hopefully would still be helpful.

Have you ever had a list and wished you could rearrange it in a way that it randomizes its different elements? Well, you can easily do this in excel with the help of its RANDBETWEEN function.

random

Click here to download the file with the help of which I’ll explain the process.

Once you have downloaded the file, just follow the steps below:

  • First, make sure the list is arranged properly in a single column in a worksheet (in our case it is Column A).
  • Then, find out how many elements does the list have. In the file, you can see we have a list of 700 words in alphabetical order.
  • Next, in the adjacent column (Column B in our case), type =RANDBETWEEN(1,Number of Elements). Pull the formula down to have it adjacent to all the elements of the list. This will generate random numbers in the different cells of Column B.
  • Select all the results in Column B, press Ctrl C to copy them, and Ctrl Alt V to open the Paste Special dialogue box. Then select ‘Value’ and press Enter. This will replace the formulas with values. If we do not value-paste the random numbers they will keep changing after every action.
  • Press Alt A T to apply filter.
  • Sort the values in any order (ascending or descending). Your list will now be randomized. You can delete the values in Column B now.

I hope you enjoyed the above tip. To get updated with all my posts and to get additional excel tips, I encourage you to subscribe to our Facebook Page and join our 3300 followers. I share some really great content there, not just from Mad About Excel, but from other excel websites and blogs as well. Click below to subscribe.

Facebook_Mad_Mad_Excel

See you soon. Happy Excelling!