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 Random Generator for Excel tool included with our Ultimate Suite 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 tool in your Excel, you are welcome to download an evaluation version below. Thank you for reading!

Available downloads

Ultimate Suite 14-day fully-functional version
Random Generator for Google Sheets

Table of contents

23 comments

  1. Hello,

    I have excel sheet , and column sorted with condition formatting and value. I am trying to generate random date in column.when trying random generator following message appear "there are no empty cell in the destination, please click okay to override or cancel to select different location"

    please help
    Mark P

    1. Hello Mark!
      I am assuming that the column in which you want to write the random data already has some other data in it. The message you quoted mentions this.
      Similar cases have been described here: How to fix #SPILL! error.

  2. Hello,
    I have a issue where i want to shuffle rows. i have Column A as "problem title" and then column B as "problem description" that correlate to each other side by side. i want to know how to shuffle the rows without affecting the correlation of between the two columns.

    1. Hello Annette!
      To avoid breaking the correspondence between columns A and B, you can merge the columns. Then sort the rows randomly, and then split the columns. Here are articles that may be helpful to you: How to merge two columns in Excel without losing data and How to split cells in Excel: Text to Columns, Flash Fill and formulas.
      You can also use the new TEXTSPLIT function to split a column into two columns. Read more: TEXTSPLIT function in Excel: split cells / text strings by delimiter.
      I hope it’ll be helpful.

  3. Good Evening,

    I am needing some help from an expert formula/function guru.

    I am attempting to create a randomize list of job assignments for a high school shop class. I can create a list/schedule that continually rotates in a methodical order, but that continually results in the same students working with the same individuals. I have a set number of students that is different for each class and a set number of jobs/assignments (some duplicated but the total number of jobs/assignments matches the number of students for each class). I want to use a formula/function to randomly assign the jobs to each student for each day while still ensuring that each student has the same amount of opportunities to participate within each job.

    I hope this makes sense. Thank you in advance for your help.

  4. I havev two columns in mu excel...column A list of teachers(1-20), column B class room numbers(1-20). How to shuffle column A with out affecting column B.

  5. Google randomization is a lot easier and user friendly.

  6. I have a complicated spreadsheet in which I use iteration=1 to deal with an intentional circular reference. I use the RAND() function, and manual recalculation. I use COUNTIF to determine the sort sequence of the random numbers. I then use OFFSET and MATCH together to extract the data in the new randomized order.

    For reasons that are too complicated for this format, I want to retrieve first one column on one manual recalculation and then that column plus a second (without resorting the original list) on the next recalculation - hence the circular equations

    What's happening is that the COUNTIF function appears to be working on the random numbers before all of the random numbers have been calculated as the values returned are clearly wrong.

    I’m assuming there’s some interaction between iteration, RAND() and the Excel’s calculation sequencing that is causing the problems but I have been unable to work out a solution.

    Any ideas?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      I guess the reason is that the random data changes with each recalculation.

      1. That's been my thought too.

        I read Excel's description of how it determines the calculation order of cells so I tried linking the calculation of the COUNTIF function to the cell containing the random number so that a recalculation of the COUNTIF is tied to the last iteration's random number. This hasn't solved the problem, though.

        Do you know of a way to force the Excel's calculation tree to calculate specific cells in a particular order?

        I am willing to send you the spreadsheet if you are interested in digging into the situation more deeply.

        1. Hello!
          You can control the order of calculations in Excel only inside formulas. The rules of arithmetic are used.

  7. I need a computer/randomizer program for a volleyball tournament that shuffles 36 players to play or sit out within 12 games.

    1) We have 2 volleyball courts, 4 sides (Red & Green; Yellow & Blue).
    2) We have 36 players. 6 teams of 6 players per team. 24 players would play on 4 teams on the 2 courts for each game. 12 players would sit out each game. Each team will consist of 6 different players each game.
    3) Each player would have a number and would play on the team and court color each game.
    4) There would be a total of 12 games. Each player would play 8 and sit out 4 of the 12 games. Each player will play twice on each court color. No player will play more than 2 games in a row.
    5) With 36 players, 1 player would play 8 games with 5 other players each game.

    We would also like to have the program sort for: 36 players (6 teams of 6 players per team); 30 players (6 teams of 5 players per team); 24 players (6 teams of 4 players per team); 24 players (4 teams of 6 players per team); 20 players (4 teams of 5 players per team) and 16 players (4 teams of 4 players per team).

    I would like the program to be able to run on a laptop computer.
    Thanks for you assistance or comments.

    1. Hi,
      This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  8. 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!

  9. 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.

  10. 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.

    Kelly

  11. Hello
    Thank you. that was creatively. I enjoyed it.

  12. 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.

    1. You can hide it

  13. 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!

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 :)