How to Shuffle Data in Excel (7 Simple Methods)

Microsoft Excel is a handy software. We use Excel functions and features for our educational, business, and other daily life purposes. Furthermore, Excel provides some cool functions to randomize data in our workbooks. This article will show you 7 simple methods to Shuffle data in Excel.

Shuffling data in Excel can be really cumbersome if we consider a large amount of data. In this article, I will show you 7 easy methods to shuffle data in Excel. For instance, we take a dataset that represents the record of salespersons of a company.

1. Shuffling Data in Excel Using RANDBETWEEN and VLOOKUP Functions

In this article, we will combine Excel RANDBETWEEN and Excel VLOOKUP functions to shuffle data given in column C. The RANDBETWEEN function randomly returns an integer between two giver numbers. Excel calls it top and bottom. Moreover, it is a volatile function i.e. the decimal number will change when we open or change the workbook. On the other hand, the Excel VLOOKUP function looks up data arranged in the dataset vertically. Let’s combine these 2 functions to mix up our data. To do so, follow these steps,

Steps:

• First, write the following RANDBETWEEN formula in cell D5.

• As already mentioned, the RANDBETWEEN function returns two arguments top and bottom. These arguments can be integers or fractions but they always return an integer as output. The bottom represents the lowest number whereas the top function indicates the highest number between the given 2 numbers.
• Now, press Enter to execute the formula.

• As a result, a random number between 1 to 5 appears in cell D5.
• Later, drag the cell down to autofill the whole column with random numbers.

• Afterward, in cell E5, we use the VLOOKUP To do so, type the formula,

• Here, D5 is under the lookup argument that we will look for in column C. The argument $B$5:$C$9 is the array from where we will extract the value. As well as, the FALSE argument is under Range_lookup syntax that represents an exact match.
• Next, hit Enter to obtain the corresponding data in cell E5.

• Finally, autofill column E by dragging the cell all the way down to obtain the desired result.

2. Combining RAND Function and Sort Feature to Shuffle Data

Again, we can randomize our data by combining the Excel RAND function and the Excel Sort feature. The RAND function returns evenly distributed random values from 0 to 1. Whereas, the Sort feature sorts a range of cells in ascending or descending order according to a specific column. Let’s combine these 2 functions to shuffle the data.

Steps:

• First, write the following code in D5,

• As a result, the RAND function returns a number from 0 to 1.
• Now, drag the cell to autofill column D with random values.

• Afterward, select the range D5:E9 to sort.

• Next, go to the Data tab and locate Sort & Filter group.
• Later, click A-Z or Z-A to sort the data in ascending or descending order respectively.

• Thus, the sorted data will appear in the dataset.

3. Mixing Data with Excel SORTBY Function

The Excel SORTBY function returns a sorted array or range based on the values of its corresponding range or array. Furthermore, we use this function to randomize data in our workbooks. To do so, follow these procedures.

Steps:

• In cell D5, write this formula,

• Here, SORTBY sorts the array returned by the RANDARRAY RANDARRAY(5) indicates the total number of data in the dataset.
• Next, tap Enter to see the result.

• Afterward, autofill the column by dragging the formula cell down.
• Thus, the desired result will pop up.

4. Inserting RANDBETWEEN Function for Shuffling Data

We have already talked about the RANDBETWEEN function in our previous methods. However, in this method, we will use this function to randomize our data. Therefore, let’s follow some easy procedures.

Steps:

• Firstly, in cell D5, type the formula,

• Here, 1 and 5 fall under the bottom and top arguments.
• Next, press Enter to obtain random numbers from 1 to 5.

• Afterward, we will implement the Sort feature to mix up the data.
• To illustrate, go to the Data tab and A-Z or Z-A in the Sort group.

• Finally, the result will appear in column E.

5. Randomizing Data with INDEX and RANDBETWEEN in Excel

To shuffle data, in this method, we will combine the INDEX function and RANDBETWEEN function. The INDEX function returns a reference from a particular row or column in Excel. To combine these 2 functions, follow the procedures.

Steps:

• First, type the following formula in cell C5,

• Here, the RANDARRAY(1,5) will provide integer values randomly from 1 to 5.
• Then, the INDEX function will return the cell references of the random numbers.

• Finally, tap Enter to obtain the result.

6. Joining CHOOSE & RANDBETWEEN Functions

We have already mentioned RANDBETWEEN functions in our previous methods. In this method, however, we will join the Excel CHOOSE function to randomize the data. The CHOOSE function returns a value using an index from a specific list. Let’s follow some steps to combine these 2 functions.

Steps:

• Firstly, write the formula in cell D5,

• Later, press Enter.

• Thus, the shuffled data pops up in the dataset.

7. Shuffling Data Through Excel VBA

In the last method of our article, we will use the Excel VBA to randomize data. We will write a simple VBA code that receives the currently selected cell information, for instance, font color, background color, etc. Afterward, it shuffles them around into the dataset. Let’s see some procedures.

Steps:

• First, select the range D5:D9.
• After that, go to Developer > Visual Basic > Insert > Module.
• As a result, a module box will pop up.

• Next, in the module box, type the following VBA code.

Option Explicit
Public Sub Randomize_Data_in_Excel()
  Dim SelectRange As Range
  Set SelectRange = Selection
  Dim SelectHorizontally As Long
  Dim SelectVertically As Long
  SelectHorizontally = SelectRange.Columns.Count
  SelectVertically = SelectRange.Rows.Count
  Dim i As Long
  Dim j As Long
  For i = SelectVertically To 1 Step -1
    For j = SelectHorizontally To 1 Step -1
      Dim RandomRow As Long
      Dim RandomColumn As Long
      RandomRow = Application.WorksheetFunction.RoundDown(SelectVertically * Rnd + 1, 0)
      RandomColumn = Application.WorksheetFunction.RoundDown(SelectHorizontally * Rnd + 1, 0)
      Dim temp As String
      Dim tempColor As Long
      Dim tempFontColor As Long
      temp = SelectRange.Cells(i, j).Formula
      tempColor = SelectRange.Cells(i, j).Interior.Color
      tempFontColor = SelectRange.Cells(i, j).Font.Color
      SelectRange.Cells(i, j).Formula = SelectRange.Cells(RandomRow, RandomColumn).Formula
      SelectRange.Cells(i, j).Interior.Color = SelectRange.Cells(RandomRow, RandomColumn).Interior.Color
      SelectRange.Cells(i, j).Font.Color = SelectRange.Cells(RandomRow, RandomColumn).Font.Color
      SelectRange.Cells(RandomRow, RandomColumn).Formula = temp
      SelectRange.Cells(RandomRow, RandomColumn).Interior.Color = tempColor
      SelectRange.Cells(RandomRow, RandomColumn).Font.Color = tempFontColor
    Next j
  Next i
End Sub

• Before running the code, save your workbook as Excel Macro-Enabled Workbook type.
• Now, press the green Run button.

• Hence, the shuffled data appears.
• Moreover, you can select any range and hit Run again to randomize any data too.

Download Practice Workbook

Conclusion

In conclusion, we have discussed some easy ways to shuffle data in Excel. Please leave any further queries or recommendations in the comment box below.

<< Go Back to Randomize in Excel | Learn Excel