# Random Sort in Excel (Formulas + VBA) MS Excel provides a few different sorting choices on top of the usual ascending and descending orders. We also have sorted by color or icon, as well as the custom sort. However, it requires one important feature which is random sort. In this article, I will show different ways to do random sort in Excel.

## 6 Ways to Perform Random Sort in Excel

### 1. Randomize a List in Excel with a Formula

Let’s consider we have a food list with their name, quantity, delivery date, status, and price. Now we will randomize the food name list using a formula. We will generate a random value again for each name and will sort them according to their random values. Step 1: Enter the following formula in cell I4 and copy it down to I15

`=RAND()`` `

Formula Explanation

• This function returns an evenly distributed random real number greater than or equal to 0 and less than 1.
• For more details, you can visit this link Step 2: Now select the Rand Value column and then select Sort Smallest to Largest in the Sort & Filter option under the Home tab Step 3: Now all the names will be sorted randomly ### 2. Randomize a List Keeping the List Order

Now here we will see how we can sort the whole dataset randomly by keeping the sort order. Our scenario will be like the below picture. Here we have calculated the sort order randomly using the RAND function. Step 1: First select the all-sort order column values Step 2: Then copy all the values Step 3: Right-click on the copied data select the Paste Special option Step 4: Now follow the process:

1. Past should be selected as Values
2. The operation should be selected as None
3. Lastly, click on the Ok button Step 5: After that click on the filter option of the Sort Order column. Then

1. Select Sort Smallest to Largest
2. Lastly, press the OK button Step 6: Now all the data will be sorted randomly ### 3. Randomize a List Using RAND and SORT Functions

In this section, we will sort the whole data randomly using RAND and SORT functions. Random numbers for Sort Order are generated by the following formula: Now the scenario will be like this: Step 1: Enter the following formula in cell B19 and press Enter

`=SORT(B4:G15,6)`

Formula Explanation

• Here B4:G15 is the range where our data is present. And 6 is used as we have six columns. ### 4. Randomize Using SORTBY, RANDARRAY, and ROWS Functions

Let’s see how we can sort randomly without using and Sort Order column. Again, our dataset will be the same but here we won’t use the Sort Order column. Step 1: Enter the following formula in cell B19 and press Enter

`=SORTBY(B4:F15,RANDARRAY(ROWS(B4:B15)))`

Formula Explanation

• Firstly, in the SORTBY function B4:F15 is the range where the sorting will be performed.
• The second argument of the SORTBY function is by_array1. We have passed a random array by using the RANDARRAY function. As we are generating random values for each row that’s why we need to assign them using the ROWS function in each row.
• If you want to learn more about SORTBY and RANDARRAY functions you can visit these two
RANDARRAY Function
SORTBY Function
• ROWS function here is counting the total number of rows of the dataset from B4 to B15.To explore more the ROWS function you check my another article on this function in this link ### 5. Randomize Using SORTBY, RANDARRAY, and COUNTA Functions

Another way to randomize datasets is using the COUNTA function instead of the ROWS function. Here we will do random sort using SORTBY, RANDARRAY, and COUNTA functions for the previous dataset.

Step 1: Enter the following formula in cell B19 and press Enter

`=SORTBY(B4:F15,RANDARRAY(COUNTA(B4:B15)))`

Formula Explanation:

• This formula is like the previous formula. Here instead of using the ROWS function, we are using COUNTA which is doing the same thing by counting the row numbers. ### 6. Random Sort Using VBA

Lastly, we will see how we can perform random sorting using VBA in Excel. Let’s see the process:

Step 1: Add a Button from the Insert option’s Form Controls which is available under the Developer tab Step 2: Place the button at any place on the worksheet Step 2: Now right-click on the button and select the Assign Macro option Step 3: Give the name of the Macro and then click on the New button. Make sure Macros in is selected as This Workbook Step 4: Now write the code in the VBA window Code:

``````Sub Button1_Click()
Dim tempString As String, tempInteger As Integer, i As Integer, j As Integer
For ii = 2 To 13
Cells(ii, 2).Value = WorksheetFunction.RandBetween(0, 100)
Next ii
For i = 2 To 13
For j = i + 1 To 13

If Cells(j, 2).Value < Cells(i, 2).Value Then

tempString = Cells(i, 1).Value
Cells(i, 1).Value = Cells(j, 1).Value
Cells(j, 1).Value = tempString

tempInteger = Cells(i, 2).Value
Cells(i, 2).Value = Cells(j, 2).Value
Cells(j, 2).Value = tempInteger
End If
Next j
Next i

End Sub
``````

Step 5: Now click on the button and sort randomly the food names ## Things to Remember

Common Errors When they show
#VALUE If there is any blank cell in the dataset and if you use the SORTBY function on that dataset this error will occur.
#SPILL As with any other dynamic array function, a #SPILL! The error most often means that there isn’t enough space in the intended spill range to display all the results.
#NAME If the function’s name is misspelled, then this error will appear.

## Conclusion

These are some ways to make random sort in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.  