Random sort in Excel: shuffle cells, rows and columns

The tutorial will teach you two quick ways to randomize in Excel: perform random sort with formulas and shuffle data by using a special tool.

Microsoft Excel provides a handful of different sorting options including ascending or descending order, by color or icon, as well as custom sort. However, it lacks one important feature - random sort. This functionality would come in handy in situations when you need to randomize data, say, for an unbiased assigning of tasks, allocation of shifts, or picking a lottery winner. This tutorial will teach you a couple of easy ways to do random sort in Excel.

How to randomize a list in Excel with a formula

Although there is no native function to perform random sort in Excel, there is a function to generate random numbers (Excel RAND function) and we are going to use it.

Assuming you have a list of names in column A, please follow these steps to randomize your list:

  1. Insert a new column next to the list of names you want to randomize. If your dataset consists of a single column, skip this step.
  2. In the first cell of the inserted column, enter the RAND formula: =RAND()
  3. Copy the formula down the column. The fastest way to do this is by double-clicking the fill handle:
    Excel RAND formula
  4. Sort the column filled with random numbers in ascending order (descending sort would move the column headers at the bottom of the table, you definitely don't want this). So, select any number in column B, go to the Home tab > Editing group and click Sort & Filter > Sort Largest to Smallest.
    Sort the column filled with random numbers in ascending order.
    Or, you can go to the Data tab > Sort & Filter group, and click the ZA button sort ascending.

Either way, Excel automatically expands the selection and sorts the names in column A as well:
Names are sorted in random order.

Tips & notes:

  • Excel RAND is a volatile function, meaning that new random numbers are generated every time the worksheet is recalculated. So, if you are not happy with how your list has been randomized, keep hitting the sort button until you get the desired result.
  • To prevent the random numbers from recalculating with every change you make to the worksheet, copy the random numbers, and then paste them as values by using the Paste Special feature. Or, simply delete the column with the RAND formula if you don't need it any longer.
  • The same approach can be used to randomize multiple columns. To have it done, place two or more columns side by side so that the columns are contiguous, and then perform the above steps.

How to shuffle data in Excel with Ultimate Suite

If you don't have time to fiddle with formulas, use the Shuffle Cells tool included in our Ultimate Suite for Excel to do a random sort faster.

  1. Head over to the Ablebits Tools tab > Utilities group, click the Randomize button, and then click Shuffle Cells.
    Shuffle Cells tool in Excel
  2. The Shuffle pane will appear on the left side of your workbook. You select the range where you want to shuffle data, and then choose one of the following options:
    • Cells in each row - shuffle cells in each row individually.
    • Cells in each column - randomly sort cells in each column.
    • Entire rows - shuffle rows in the selected range.
    • Entire columns - randomize the order of columns in the range.
    • All cells in the range - randomize all cells in the selected range.
  3. Click the Shuffle button.

In this example, we need to shuffle cells in column A, so we go with the third option:
Shuffle cells in the selected columns.

And voilà, our list of names is randomized in no time:
A list of names is randomized.

If you are curious to try this and explore a lot more fascinating features included with Ultimate Suite for Excel, you are welcome to download a 14-day trial version. If our tools have proved useful in your work and you decide to get a license, be sure to use this special opportunity:

Get Promo Code for Ultimate Suite - exclusive offer for our blog readers!

7 responses to "Random sort in Excel: shuffle cells, rows and columns"

  1. Jenn says:

    Thank you so much! I knew there had to be a way to randomize a list in Excel. I never would have come up with this on my own!

  2. bob says:

    the running ticker at the bottom of the site is incredibly distracting and makes it almost unusable. I use it ONLY if I can't find the answer on another site.

  3. Mohammad says:

    Thank you. that was creatively. I enjoyed it.

  4. Kelly says:

    I do circuit training routines at home. I have multiple exercises that I perform, and I'm currently looking through a list on a Word document, and just picking as I go. I would like for Excel to choose the exercises randomly for me. I have multiple columns of exercises, once column for each part of the body it works. Is it possible to open the spreadsheet, and Excel will automatically highlight several random cells out of each column? And each time I open it, Excel will choose a different set of random cells to automatically highlight out of each column? I am not well-versed in formulas, and was unable to make the multiple column thing work for me.

    Thanks for any advice or help you can give.


  5. manik says:

    thanks a lot you saved my job. i had to plan a tambola, and was easily able to generate 45 tickets with the help of the randomise > shuffle cells tool from Utilitites.
    I have been trying so hard from =Rand() formula, but it never worked multiple combinations.
    Thanks a lot.

  6. Lorne says:

    Brilliant! Your instructions worked perfectly, giving me a way to instantly randomize a list of names as often as I want. No way can anyone stumble upon this solution without in depth knowledge of Excel. Sincere thanks!

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)