 # How to Randomize Lists and Shuffle Data in Excel 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

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

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. Figure 5. RANDBETWEEN function will be more useful if you choose a wide range of numbers

## How to Shuffle Columns and Rows in Excel  (Randomizing Multiple Columns and Rows in Excel)

If you want to shuffle the data in multiple columns or rows, you can follow these steps.

1. To shuffle columns, add a column to the left or right side of the table.
2. To shuffle rows, add a row to the top of the table.
3. Write the RAND formula in the cells of the added column and row.
4. Now you have a set of random numbers on top and left sides of the table. Like the sample below. Figure 6. Randomizing the data in multiple columns and rows.

To make it clear what happens when we shuffle columns and rows at the same time, we have colorized the columns and dedicated data to each of them. Now when it shuffles, we can easily find out what has happened.

5. Whether you randomly sort the data of rows or columns first, just sort them as you prefer. In our example, we have sorted both columns and rows from largest to smallest.
6. To sort the rows, go to Data> Sort, a window will open, then click on Option and select Sort Left to Right. Then follow the image below. figure 7. Sorting data in rows

See the result in the following sample.

It may seem that shuffling the data in columns and rows will shuffle the whole table. The problem here is that the data in this table is shuffled into groups. That means it doesn’t spread the data inside the table; it just sorts the data in column groups. To shuffle more randomly, you need to write a VBA code to work on the whole table.

If you are familiar with writing VBA code for Excel, add the codes below to a module in your worksheet. This code can help you to shuffle columns and rows in Excel more reasonably:

``````Option Explicit

Public Sub ShuffleGrid()
Dim rngSelected As Range
Set rngSelected = selection

Dim horizontalLength As Long
Dim verticalLength As Long

horizontalLength = rngSelected.Columns.Count
verticalLength = rngSelected.Rows.Count

Dim i As Long
Dim j As Long

For i = verticalLength To 1 Step -1
For j = horizontalLength To 1 Step -1
Dim rndRow As Long
Dim rndColumn As Long
rndRow = Application.WorksheetFunction.RoundDown(verticalLength * Rnd + 1, 0)
rndColumn = Application.WorksheetFunction.RoundDown(horizontalLength * Rnd + 1, 0)

Dim temp As String
Dim tempColor As Long
Dim tempFontColor As Long

temp = rngSelected.Cells(i, j).Formula
tempColor = rngSelected.Cells(i, j).Interior.Color
tempFontColor = rngSelected.Cells(i, j).Font.Color

rngSelected.Cells(i, j).Formula = rngSelected.Cells(rndRow, rndColumn).Formula
rngSelected.Cells(i, j).Interior.Color = rngSelected.Cells(rndRow, rndColumn).Interior.Color
rngSelected.Cells(i, j).Font.Color = rngSelected.Cells(rndRow, rndColumn).Font.Color

rngSelected.Cells(rndRow, rndColumn).Formula = temp
rngSelected.Cells(rndRow, rndColumn).Interior.Color = tempColor
rngSelected.Cells(rndRow, rndColumn).Font.Color = tempFontColor
Next j
Next i

End Sub``````

## Bottom Line

Randomly sorting lists is so easy on Excel; you first need to add a column or row to add random numbers using the RAND function, and then you can sort those numbers and your list will be shuffled accordingly.