Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Perform Random Sort in Excel (6 Easy Methods)

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.


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.

Random Sort in Excel


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.

In the formula, we have used the 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.

random sort in excel result

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.

Perform Random Sort with RAND Function Keeping List Order

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

random sort in excel result

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.

Use Excel RAND & SORT Functions to Do Random Sort

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

In the formula, Here 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


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.

Insert SORTBY, RANDARRAY & ROWS Functions to Do Random Sort

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

random sort in excel result

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.

Utilize SORTBY, RANDARRAY & COUNTA Functions for Random Sorting

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

In the formula, This formula is like the previous formula. Here instead of using the 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.

random sort in excel result

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

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo