How to shuffle a list of items in a random order

by | Apr 16, 2018 | Excel Tips & Tricks

Do you have a list of items or names that you want to sort randomly? Using the RANDBETWEEN, RANK, and INDEX functions in conjunction, you can easily shuffle an item group for a random draw.

Syntax

=RANDBETWEEN( 1, number of list items ) + ROW() / very large number that has more digits than the number of list items)

=RANK(  random number generated via formula, list of generated random numbers)

=INDEX( original list, rank of generated random number)

Steps

  1. Select the cell from the column that's right next to the list (i.e. C3)
  2. Type in =RANDBETWEEN(1,8)+ROW()/10000 to generate random unique numbers. Make sure 2nd argument of RANDBETWEEN is greater than or equal to the number of list items, and last number that divides ROW function has more digits than the number of list items.
  3. Copy down the formula to the rest of the column (i.e. C3:C10)
  4. Select the cell in the column that's right next to it (i.e. D3)
  5. Type in =RANK(C3,$C$3:$C$10) to return a list of the ranks. Make sure that the first argument is relative and the second is absolute (i.e. $A$1).
  6. Copy down the formula as long as your list goes (i.e. D3:D10)
  7. Select a new cell that is to be the first cell of the shuffled list (i.e. F3)
  8. Type in =INDEX($B$3:$B$10,D3) to get the values from the original list in random order. Make sure the first argument is absolute and covers the original list.
  9. Copy down the formula for the remaining rows (i.e. F3:F10)

How

First, we're going to shuffle a list randomly from a set of randomly generated numbers (RNG). The RANDBETWEEN function is perfect for the job, as it can return an RNG between limits. When determining the limits, use 1 for minimum, and the number (count) of list items for maximum.

However, there is a possibility that RANDBETWEEN return the same numbers for different cells. This situation will cause value duplication. That's something we don't want, since same values will affect ranking of generated values and mess up our list. To avoid this, use the ROW function and divide the return value with a very large number to end up with a very small number, that can help separate same numbers.

=RANDBETWEEN(1,8)+ROW()/10000

Second step is to get ranks from the RNG in list and we're going to use the RANK function to do this. Finding each RNG rank will give us a list of shuffled indexes.

 =RANK(C3,$C$3:$C$10)

The last step is to generate a new list by using indexes calculated by the RANK function. The INDEX function returns values in a range of specified indexes. Although it can take both row and column arguments as index values, in this example we used only row index, because our range is only a single column.

=INDEX($B$3:$B$10,D3)