Sorting a list of items in random order in excel – using formulas

In shuffling a list of items in excel I have described the technique of using random numbers generated by RAND() to sort a list of items. The technique had one disadvantage though, every time you need to reshuffle the list you have to press F9 to recalculate the rand() and then go to menu > data > sort and sort the data again based on the new random numbers.

Here is a better technique that needs one key stroke to reshuffle the list of items (sorting the list in random order every time you press the key F9):

• Insert 2 columns to the left of the list of items you want to shuffle
• In the first column fill a series of numbers starting with 1
• In the next column fill RAND() formula
• Now, next to the list of items you want to sort in random order, we will use both VLOOKUP() and SMALL() excel formulas to fetch items in random order. See the formula below:

  

  The SMALL() excel spreadsheet formula is used to sort a list of numbers and fetch nth smallest number in a given list.

• When you want to reshuffle the order, just hit F9

More sorting: Sort text / tables from left to right along columns