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 **6 **easy methods to do random sort in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 6 Easy Methods to Perform Random Sort in Excel

We can do random sorting in **Excel **in various ways. I will show **6 **easy methods to random sort in **Excel **here. For demonstration, I have used a dataset having **Name**, **Quantity**, **Delivery Date **and **Price**.

### 1. Use RAND Function to Perform Random Sort in Excel

**Excel **has **the RAND function **which can generate a random fractional number between **0 **to **1**. I will use the **RAND **function and **Sort & Filter **option of **Excel **to do the random sort. Follow the given steps below.

- First, take two additional columns
**Name**and**Rand Value**beside the dataset and copy the names from**Name**column. - Then, write the following formula in
**Cell H5**.

`=RAND()`

- Next, press
**Enter**.

**RAND**function. It returns a real random number that is evenly distributed. It can be greater than or equal to

**0**and less than

**1**.

- Further, use the
**Fill Handle**to copy the formula in the cells following.

- Now, select the random values in
**Rand Value**column.

- Afterward, select
**Sort & Filter > Sort Smallest to Largest**in the**Home**tab of**Excel**

- Consecutively,
**Sort Warning**window will appear. Select**Expand the section**and press**Sort**.

- Instantly, we will see the values from new
**Name**column sorted randomly.

**Read More:** **How to Sort Unique List in Excel (10 Useful Methods)**

### 2. Perform Random Sort with RAND Function Keeping List Order

Instead of doing the sorting in a different location, we can do it in the dataset. For that, add an extra column **Sort Order**. Follow the given steps for the procedures.

- First, in the
**Sort Order**column generate random values using the**RAND**You can follow the steps from**Method 1**.

- Then, select data from
**Sort Order**column and select**Sort & Filter > Filter**in the**Home**tab.

- Consecutively, the
**filter**icon will appear in the**Sort Order**column. - Click on the small icon and select
**Sort Smallest to Largest**from options.

- Finally, we will see our dataset sorted randomly.

**Related Content:** **How to Sort ListBox with VBA in Excel (A Complete Guide)**

### 3. Use Excel RAND & SORT Functions to Do Random Sort

We can use the **RAND **and **SORT **functions of **Excel **to do the sorting. Let’s walk through the procedures.

- First, create another table without data in it.
- Secondly, write the following formula in
**Cell B19**.

`=SORT(B5:F14,5)`

- Simultaneously, press
**Enter**. - And we will see the sorted data in the table.

**B5:F14**is the range where our data is present. And

**5**is used as we have six columns.

**Note:**we used an array formula. For previous versions of

**Excel**except

**Excel 365**press

**Ctrl + Shift + Enter**instead of pressing only

**Enter**.

**Read More:** **How to Use Sort Function in Excel VBA (8 Suitable Examples)**

**Similar Readings**

**Excel Sort Dates in Chronological Order (6 Effective Ways)****How to Sort IP Address in Excel (6 Methods)****Sort by Month in Excel (4 Methods)****How to Sort Numbers in Excel (8 Quick Ways)****How to Sort Array with Excel VBA (Both Ascending and Descending Order)**

### 4. Insert SORTBY, RANDARRAY & ROWS Functions to Do Random Sort

Using **SORTBY**, **RANDARRAY **and **ROWS **functions of **Excel**, we can do random sorting. I am showing the steps for that.

- First, create another table without data in it.
- Secondly, write the following formula in
**Cell B19**.

`=SORTBY(B5:F14,RANDARRAY(ROWS(B5:B14)))`

- Simultaneously, press
**Enter**. - Finally, we will see the sorted data in the table.

In the formula,

- Firstly, in the
**SORTBY**function**B5:F14**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**As we are generating random values for each row that’s why we need to assign them using the**ROWS**function in each row. **ROWS**function here is counting the total number of rows of the dataset from**B5**to**B14**.

**Read More:**** [Solved!] Excel Sort Not Working (2 Solutions)**

### 5. Utilize SORTBY, RANDARRAY & COUNTA Functions for Random Sorting

The random sorting can be done using the **SORTBY**, **RANDARRAY **and **COUNTA **functions of **Excel**. I am showing the steps for doing that in the below section.

- First, type the headers in the
**range B18:F18**. - Then, write the following formula in
**Cell B19**.

`=SORTBY(B5:F14,RANDARRAY(COUNTA(B5:B14)))`

- After that, press
**Enter**. - Finally, we will see the sorted data in the table.

**ROWS**function, we are using the

**COUNTA**function which is doing the same thing by counting the row numbers.

**Related Content:** **How to Create Custom Sort List in Excel**

### 6. Apply VBA for Random Sorting in Excel

We can do the random sorting using the **Visual Basics for Applications**(**VBA**) code in **Excel**. The procedure is quite simple. Here, we will use the dataset below. In the dataset, I have included **Name **and **Rand Value**.

Let’s walk through the procedures below.

- First, go to the
**Developer**tab and select**Insert > Button (Form Control)**.

- Then, a button will appear. Place the button in your worksheet.
- You can change the button name to
**Random Sort**. - Now, right-click on the button and select
**Assign Macro**.

- Then, in the
**Assign Macro**window select**This Workbook**from**Macros in**section and press**OK**.

- Instantly,
**VBA**window will appear. - Write the following code there.

```
Sub Random_Sort()
Dim xtmpStr As String, xtmpInt As Integer, m As Integer, n As Integer
For Z = 5 To 14
Cells(Z, 3).Value = WorksheetFunction.RandBetween(0, 100)
Next Z
For m = 5 To 14
For n = m + 1 To 14
If Cells(n, 3).Value < Cells(m, 3).Value Then
xtmpStr = Cells(m, 2).Value
Cells(m, 2).Value = Cells(n, 2).Value
Cells(n, 2).Value = xtmpStr
xtmpInt = Cells(m, 3).Value
Cells(m, 3).Value = Cells(n, 3).Value
Cells(n, 3).Value = xtmpInt
End If
Next n
Next m
End Sub
```

- Further, save the
**VBA**code by pressing**Ctrl + S**. - Now, go back to the worksheet and click on the
**Random Sort**button. - Finally, we will see the data in our worksheet sorted randomly.

**Read More:** **How to Use Advanced Sorting Options in Excel**

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

## Further Readings

**Sum Using OFFSET and MATCH in Excel (With Alternative Options)****How to Sort Duplicates in Excel (Columns and Rows)****Sort Rows by Column in Excel (4 Methods)****Auto Sort When Data is Entered in Excel (3 Methods)****How to Auto Sort Multiple Columns in Excel (3 Ways)****Sort Two Columns in Excel to Match (Both Exact and Partial Match)****How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)**