How to Randomize Lists in excel + Shuffle Data

5.6 min read|Last Updated: December 24th, 2023|Categories: excel|
table of content

There are several options for sorting data in Excel, such as ascending or descending numbers or sorting them alphabetically. But what if you want to randomize a list or select random numbers from the list? Randomizing data is not so common in Excel. However, it doesn’t mean it is not useful. In this post, we will explain to you how to randomize a list in Excel.

How to randomize a list in Excel with a formula (random sort in excel)

You can make a list and allocate a random number to each cell using different formulas. The most commonly used formula is the RAND function, which generates a random number between 0 and 1. After dedicating a random number to each cell, you can sort the numbers and shuffle the list. Here is how it works:

Randomize Columns with RAND function

Assume that we have a list of our customers in column A. We want to sort this list randomly. Therefore, we need to have another column to show random numbers. In this example, we named it “Random Numbers.”

  1. Click on the first cell in column B and write =RAND() formula and then press Enter; a random number between 0 and 1 appears in the cell.
    Allocating a random number using RAND function.
    Figure 1. Allocating a random number to a cell.
  2. Double click on the lower right corner of the first cell so the function will be copied to the rest of the cells.
    Randomizing a list in Excel
    Figure 2. By double-clicking on the first cell you copy the formula to the other cells too.
  3. Now you can sort the randomized numbers by “sort and filter” from the home tab or from the sort and filter group in the data tab or even round them using the ROUND function.
    By sorting the numbers you can see that the list of customers will be sorted differently too.
    Figure 3 By sorting the numbers you can see that the list of customers will be sorted differently too.

Please note that every time you make a change in cells, the RAND function generates new random numbers. Therefore, by sorting the numbers, you may see new random numbers too. If you don’t want the numbers to change, first, copy the value of the cells in another column, then sort them.

Randomize Columns with RANDBETWEEN function

RANDBETWEEN is another function that can randomize numbers in Excel. In this function, the numbers are randomized between two given numbers. For instance, you want to generate random numbers between 1 to 10. Thus, you write RANDBETWEEN(1,10).

Allocating random number using RANDBETWEEN function.
Figure 4 RANDBETWEEN function only randomizes between the numbers that you choose.

However, as you can see, the problem with RANDBETWEEN is that it may randomize a number twice or even more. This part of randomizing is out of our control; if you want to have a more accurate result, the RAND function will give you more numbers.

If you widen the range between the numbers, for example, randomizing numbers between 1 to 1000, the chance of having a number twice will be less. Therefore, this method will be more useful to have a more unique random number.

Randomizing a list using RANDBETWEEN function
Figure 5. RANDBETWEEN function will be more useful if you choose a wide range of numbers

To receive expert