MrExcel.com - Your One Stop for Excel Tips & Solutions

Celect Random Numbers from a Column

November 12, 2001 - by

Someone (why the rash of people not giving their name?) wrote:

I have a column of 50 numbers and want to randomly select 5 of them. Can this be done?

There are several approaches to this. Here is one way to accomplish this. Let's say that your 50 numbers are in cells A2:A51.

  • Highlight cells B2:B51
  • Enter the formula =RAND() and hit Ctrl + Enter. This will enter the formula in all 50 cells at once
  • Copy B2:B51 with Ctrl + C
  • PasteSpecial the formulas into values by using Edit - PasteSpecial - Values - OK

You have now assigned a random number to each row. You could sort by column B and take the top 5, the bottom 5, whichever you prefer. If for some reason you can not sort the data, you could use column C and enter a Rank function.

  • In cell C2, enter this formula: =RANK(B2,$B$2:$B$51)<6
  • Copy the formula from C2 to C2:C51

Anything with a value of TRUE is one of the 5 randomly selected numbers.