Bạn đang xem bài viết How To Randomly Sort A List In Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Have you ever needed to take a list and randomly sort it? How would you do it? It’s not as intuitive as you think. We’re so used to sorting lists in alphabetical order or from smallest to largest, but this idea of randomly sorting a list is not very common in Excel. Let’s jump right into it and show you how to randomly sort a list in Excel.
Sample Data
Say we have some data that we’d like to sort:
Normally, we could sort by Movie title, the date that it opened (as it’s shown in the picture), or by the total amount it grossed. But what if we wanted to “shuffle” this list? How could we go about doing that?
By the way, this is the same data that we used in the Sum the Top 5 Values post.
You can also copy and paste this table to follow along:
Movie Date Opened Total Gross
Deadpool 2/12/16 $363,070,709
Zootopia 3/4/16 $341,268,248
Batman v Superman: Dawn of Justice 3/25/16 $330,360,194
The Jungle Book (2016) 4/15/16 $364,001,123
Captain America: Civil War 5/6/16 $408,084,349
Finding Dory 6/17/16 $486,295,561
The Secret Life of Pets 7/8/16 $368,384,330
Suicide Squad 8/5/16 $325,100,054
Rogue One: A Star Wars Story 12/16/16 $532,177,324
Sing 12/21/16 $270,329,045
Does this article help you? If so, please consider supporting me with a coffee ☕️
Randomly Sort a List in Excel
To sort a list randomly in Excel, first you need to add a new column to your data. When using Excel Tables, you can simply type in a new column name at the next available table header and it will be automatically joined to your table.
Here, we type in “Sort Order” and press Enter.
Next, we need a way to randomly sort the list. We can use the RAND() function in Excel to help us with that. The RAND() function will return a number at random between 0 and 1.
After we add the formula, we can sort by that column. This will randomize the list for us.
Also, each time the RAND() function is calculated, you get a different number. This is why you see the table has all different numbers.
Keep Shuffling
Another cool tip is that if you’re not happy with the initial shuffling of the list, you can simply keep sorting between Ascending and Descending to keep shuffling the list. This works because when you sort the list, it recalculates the RAND() function, giving a new number each time.
Keeping the List Order
But what if you don’t want the RAND() function to keep recalculating? What if you want to keep the sort order?
If you want to keep the sort order, you can simply save the values from RAND() before sorting again.
How To Select Random Names From A List In Excel?
How to select random names from a list in Excel?
Supposing you have a list of names locates in column A in Excel, for quickly selecting random name from this list, what would you do? Actually, you can select random name from list with formula. Beside the formula method, we will show you a handy add-in to easily solve this problem.
Select random name from a list with formula Easily select random name from a list with Kutools for Excel
Select random name from a list with formula
Please do as follows to select random name from a list in Excel.
1. Select a blank cell besides the list, copy and paste formula =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1) into the Formula Bar, and then press the Enter key. You can see a random name is displayed in the selected cell.
Notes:
1. Select this cell and press F9 key, you will get different names randomly.
2. You can also select the cell and drag the fill handle down to list the random names you need.
3. The random name will be changed automatically every time you refresh the worksheet.
Select random name from a list with Kutools for Excel
With the Sort Range Randomly utility of Kutools for Excel, you can quickly select random names from list in Excel.
Before applying Kutools for Excel, please download and install it firstly.
2. In the Sort/Select Range Randomly dialog box and under the Select tab, please configure as follows.
Specify number of cells you want to select in the No. of cells to select box;
Choose Select random cells option in the Select Type section;
Now the specified number of cells in the name list are selected randomly.
Note: If you want to list the randomly selected names together, please copy and paste them to other cells.
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Easily select random name from a list with Kutools for Excel
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Excel Formula: Random List Of Names
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this:
=
INDEX
(
names,
5
)
However, the trick in this case is that we don’t want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this:
RANDARRAY
(
10
,
1
,
1
,
COUNTA
(
names)
The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result:
=
INDEX
(
names,
RANDARRAY
(
10
,
1
,
1
,
100
,
TRUE
))
In either case, RANDARRAY will return 10 numbers in an array that looks like this:
{
64
;74
;13
;74
;96
;65
;5
;73
;84
;85
}
Note: these numbers are random only and do not map directly to the example shown.
This array is returned directly to the INDEX function as the row argument:
=
INDEX
(
names,
{
64
;74
;13
;74
;96
;65
;5
;73
;84
;85
}
Because we are giving INDEX 10 row numbers, it will 10 results, each corresponding to a name at the given position. The 10 random names are returned in a spill range beginning in cell D5.
Prevent duplicates
One problem with the above formula (depending on your needs) is that RANDARRAY will sometimes generate duplicate numbers. In other words, there is no guarantee that RANDARRAY will return 10 unique numbers.
To ensure 10 different names from the list, you can adapt the formula to randomly sort the full list of names, then retrieve the first 10 names from the list. The formula in F5 uses this approach:
=
INDEX
(
SORTBY
(
names,
RANDARRAY
(
COUNTA
(
names))),
SEQUENCE
(
10
))
The approach here is the same as above – we are using INDEX to retrieve 10 values from the list of names. However, in this version of the formula, we are sorting the list of names randomly before handing giving the list to INDEX like this:
SORTBY
(
names,
RANDARRAY
(
COUNTA
(
names)))
Here, the SORTBY function is used to sort the list of names randomly with an array values created by the RANDARRAY function, as explained in more detail here.
Finally, we need to retrieve 10 values. Because we already have names in a random order, we can simply request the first 10 with an array created by the SEQUENCE function like this:
SEQUENCE
(
10
)
SEQUENCE builds an array of sequential numbers:
{
1
;2
;3
;4
;5
;6
;7
;8
;9
;10
}
which is returned to the INDEX function as the row argument. INDEX then returns the first 10 names in a spill range like the original formula.
How To Generate Random Numbers In Excel (A Step
There may be cases when you need to generate random numbers in Excel.
For example, to select random winners from a list or to get a random list of numbers for data analysis or to create random groups of students in class.
In this tutorial, you will learn how to generate random numbers in Excel (with and without repetitions).
Generate Random Numbers in Excel
There are two worksheet functions that are meant to generate random numbers in Excel: RAND and RANDBETWEEN.
RANDBETWEEN function would give you the random numbers, but there is a high possibility of repeats in the result.
RAND function is more likely to give you a result without repetitions. However, it only gives random numbers between 0 and 1. It can be used with RANK to generate unique random numbers in Excel (as shown later in this tutorial).
Generate Random Numbers using RANDBETWEEN function in Excel
Excel RANDBETWEEN function generates a set of integer random numbers between the two specified numbers.
RANDBETWEEN function takes two arguments – the Bottom value and the top value. It will give you an integer number between the two specified numbers only.
For example, suppose I want to generate 10 random numbers between 1 and 100.
Here are the steps to generate random numbers using RANDBETWEEN:
Select the cell in which you want to get the random numbers.
In the active cell, enter =RANDBETWEEN(1,100).
Hold the Control key and Press Enter.
This will instantly give me 10 random numbers in the selected cells.
While RANDBETWEEN makes it easy to get integers between the specified numbers, there is a high chance of repetition in the result.
For example, when I use the RANDBETWEEN function to get 10 random numbers and use the formula =RANDBETWEEN(1,10), it gives me a couple of duplicates.
If you’re OK with duplicates, RANDBETWEEN is the easiest way to generate random numbers in Excel.
Note that RANDBETWEEN is a volatile function and recalculates every time there is a change in the worksheet. To avoid getting the random numbers recalculate, again and again, convert the result of the formula to values.
Generate Unique Random Numbers using RAND and RANK function in Excel
I tested the RAND function multiple times and didn’t find duplicate values. But as a caution, I recommend you check for duplicate values when you use this function.
Suppose I want to generate 10 random numbers in Excel (without repeats).
Here are the steps to generate random numbers in Excel without repetition:
Select the cells in which you want to get the random numbers.
In the active cell, enter =RAND()
Hold the Control key and Press Enter.
Select all the cell (where you have the result of the RAND function) and convert it to values.
In the adjacent column, use the following formula: =RANK.EQ(A2,$A$2:$A$11)
Now you can use the values in column B as the random numbers.
Note: RAND is a volatile formula and would recalculate every time there is any change in the worksheet. Make sure you have converted all the RAND function results to values.
Caution: While I checked and didn’t find repetitions in the result of the RAND function, I still recommend you check once you have generated these numbers. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it.
You May Also Like the Following Excel Tutorials:
Cập nhật thông tin chi tiết về How To Randomly Sort A List In Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!