Random Sort in Excel (Formulas + VBA)

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.

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.

Randomize a List in Excel with a Formula

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

Enter the following formula in cell I4 and copy it down to I15 =RAND()

Step 2: Now select the Rand Value column and then select Sort Smallest to Largest in the Sort & Filter option under the Home tab

select Sort Smallest to Largest

Step 3: Now all the names will be sorted randomly

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.

Randomize a List Keeping the List Order

Step 1: First select the all-sort order column values

First select the all-sort order column values

Step 2: Then copy all the values

Then copy all the values

Step 3: Right-click on the copied data select the Paste Special option

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

Paste special

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

Sort option

Step 6: Now all the data will be sorted randomly

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:

rand function

Now the scenario will be like this:

Randomize a List Using RAND and SORT Functions

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

formula using SORT function

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.

Randomize Using SORTBY, RANDARRAY, and ROWS Functions

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

Randomize Using SORTBY, RANDARRAY, and ROWS Functions

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

Randomize Using SORTBY, RANDARRAY, and COUNTA Functions

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

: 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: Place the button at any place on the worksheet

Step 2: Now right-click on the button and select the Assign Macro option

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

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

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

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.

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