How to Randomize a List in Excel? 2 Different Methods
(Note: This guide on how to randomize a list in Excel is suitable for all Excel versions including Office 365)
Excel is used for various purposes that include storing, retrieving, and showcasing data in several different formats. In some lists or tables, there might be a need to shuffle the entries randomly.
In this article, I will tell you how to randomize a list in Excel in 2 easy ways.
You’ll Learn:
Watch our video on how to randomize a list in Excel
Why Randomize a List in Excel?
Before we learn how to randomize a list in Excel, let us see some cases where we randomize the entries in lists and why we do that.
We all might be quite familiar with the concept of randomizing a value. When we have a list of entries at hand and have no criteria to sort them, organizing or arranging the entries in an unbiased way can be a bit complicated. Here is where randomizing the entries of the list or picking a random entry from the list comes to your aid.
We randomize the entries of a list or table while picking any random criteria in an unbiased way.
Excel has a variety of ways to sort your data either in ascending or descending format based on different columns. Excel even has a way to custom sort a list. But, Excel lacks a common functionality to sort data, i.e. the random sort.
You can easily randomize a list in Excel with the help of certain functions and shuffle all the values.
Related Reads:
How to Wrap Text in Excel? With 6 Simple Methods
How to Convert XML to Excel? – 2 Easy Methods
How to Count Cells with Text in Excel? 3 Different Use Cases
How to Randomize a List in Excel?
Though Excel does not provide an option of a random sort, there is a function called =RAND() which helps you to sort the data in the list randomly. However, when this function is used in the usual way, this returns a random numerical value. But this function can be used with other functions and with a series of steps to randomize a list.
Let us see 2 ways to randomize a list in Excel with an example.
Consider an example where a group of 10 people arrives for a conference. The members consist of different majors and fields. The order of presentation should be unbiased and must be picked randomly and decided beforehand.
Using a Combination of Formulas
Functions and formulas help to solve a variety of problems in an efficient way. To randomly sort out data, you can use the SORTBY formula with a combination of RANDARRAY and COUNTA functions with a few criteria to randomize the list.
First, select any destination cell to create the random list. One advantage of using this method is that the new randomized data will be created onto a new cell/column and the original row will be left intact for any further reference.
Enter the formula =SORTBY(range,RANDARRAY(COUNTA(range))) in the destination cell and press Enter. Here, the range is the array of cells that houses the list to be randomized.
This creates a random list of entries from the selected range of cells onto the destination cell.
Looks so simple, right? It really is. Now, let me explain how the function works and how it returns a list of randomized values from the array.
The COUNTA function counts the total number of values in the provided range of cells. In this case, this function counts the number of entries between cells B4 and B13.
The RANDARRAY function generates random values and stores them in an array. Since the value returned by the COUNT function is 10, the RANDARRAY function creates 10 values and stores them in an array.
Now, these numbers are given to the SORTBY function and it sorts the contents of an array and returns the value in the same form. In this case, randomized values from the RANDARRAY function are sorted and populate the cell range B4 to B13.
You can see that only the first cell contains the function and the formula for the rest of the values is grayed out. That means that the SORTBY function returns the array of values starting from the first cell which cannot be independently modified.
This combination of functions is volatile and changes with any update or iteration made to the sheet. If you are not satisfied with the current random values, you can either enter the edit mode of the first cell and press Enter or press the function key F9 and the values change automatically.
After you create a random list in Excel, you can copy the cells and paste them as Values to prevent any involuntary fluctuation of the values.
Note: This function is only available in newer versions of Microsoft Office (O365 or Office 2021 and higher versions).
Suggested Reads:
How to Add a Secondary Axis in Excel? 2 Easy Ways
Short Date Format in Excel – 3 Different Methods
How to Graph a Function in Excel? 2 Easy Ways
Using the RAND Function and Sorting
This is one of the oldest ways to create a random list in Excel. This method just employs the RAND function to create random values and randomly sort the data. However, it employs a couple of steps to arrive at the randomly sorted data.
First, select a destination cell.
Enter the formula =RAND and press Enter.
When you press Enter, a random value populates in the cell.
Use the drag handle to apply the formula to the remaining cells.
As of now, it makes no sense and the values are not random yet.
Now, with the selected cells, navigate to Data. Under the Sort & Filter section, click on Sort. You can sort the random values either ascending or descending.
Excel throws a warning suggesting you select the data adjacent to the random values. Select the Expand the selection and click Sort.
In case you click on Sort instead of clicking on the ascending or descending button directly, Excel opens a new Sort dialog box. Select the values to sort, the order of the sort, and click OK.
Once you click on Sort, the random values will be sorted out along with the values.
You can delete the random values and just keep the list that has been randomized.
Also Read:
How to Calculate Factorial in Excel? Along with 2 Easy Examples
How to Enable Excel Dark Mode? 2 Easy Methods
How to Create an Excel Slicer? 2 Easy Ways
Frequently Asked Questions
How to Randomize a Large List in Excel?
You can randomize a large list in Excel, it is better to use the SORTBY, RANDARRAY, and COUNTA functions as it randomizes the list in a single step.
How to Assign Random Numbers to a List in Excel?
To assign random numbers, sort the numbers in ascending or descending order. Now, use the random function and sort the cells together with the numbers.
Are Duplicates Created When Randomizing a List?
The answer is No. When randomizing a list in Excel, only the existing values are randomized. No new values are created, duplicated, or deleted.
Closing Thoughts
In this article, we saw how to randomize a list in Excel in 2 easy ways. On one hand, using a combination of SORTBY, RANDARRAY, and COUNTA functions is easy and efficient but it does not work on lower versions. (Ambien) Using the RAND function and sorting the values works on every version but it takes a few extra steps. Choose the method that suits you the best.
Want more high-quality guides for Excel? Check out our free Excel resources centre.
Click here to access in-depth Excel training courses and master in-demand advanced Excel skills.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 130+ IT training courses by seasoned professionals.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!