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.
Download Practice Workbook
You can download this workbook to practice yourself.
7 Simple Methods to Shuffle Data in Excel
Shuffling data in Excel can be really cumbersome if we consider a large amount of dataset. 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. Shuffle 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.
=RANDBETWEEN(1,5)
- 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,
=VLOOKUP(D5,$B$5:$C$9,2, FALSE)
- 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.
Read More: How to Shuffle Numbers in Excel (7 Easy Methods)
2. Combine 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,
=RAND()
- 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.
Read More: How to Randomize List of Names in Excel (8 Easy Methods)
3. Mix 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,
=SORTBY(C5:C9,RANDARRAY(5))
- 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. Insert 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,
=RANDBETWEEN(1,5)
- Here, 1 and 5 falls 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.
Read More: How to Randomize a List in Excel Into Groups (5 Suitable Ways)
5. Randomize 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,
=INDEX($C$5:$C$9,RANDBETWEEN(1,5))
- 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.
Read More: How to Shuffle an Array Using Excel VBA (4 Examples)
6. Join 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,
=CHOOSE(RANDBETWEEN(1,5),$C$5,$C$6,$C$7,$C$8,$C$9)
- Later, press Enter.
- Thus, the shuffled data pops up in the dataset.
How Does the Formula Work?
- RANDBETWEEN(1,5)
First of all, this part returns random numbers between two arrays called bottom and top. Here, 1 and 5 indicate the bottom and top arrays that we want to calculate and later return 5 values. These values can be number or cell references.
- CHOOSE(RANDBETWEEN(1,5),$C$5,$C$6,$C$7,$C$8,$C$9)
Subsequently, $C$5 returns as Value1, $C$6 returns as Value2 and so on. They return the cell references from where the CHOOSE function will specify the range. Finally, CHOOSE specifies the range using the cell references and returns values using the index.
Read More: How to Randomize a List in Excel Without Duplicates (4 Ways)
7. Shuffle 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.
Conclusion
In conclusion, we have discussed some easy ways to shuffle data in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.