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.

**Table of Contents**hide

## Download the Practice Workbook

**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:

- Past should be selected as
**Values** - The operation should be selected as
**None** - Lastly, click on the
**Ok**button

**Step 5: **After that click on the filter option of the Sort Order column. Then

- Select
**Sort Smallest to LargestÂ** - 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. - If you want to explore more about this SORT function, you can visit thisÂ link

**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. - If you want to explore more about this function you can check thisÂ link

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