Cập nhật nội dung chi tiết về How To Randomly Sort A List In Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác 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 Sort Lists In Excel 2003
Use these tips to prevent problems when sorting in Excel. How to sort two or more columns, sort by row, sort in a Custom Order. For Excel 2007 and later, see the Sorting Data Basics page
Using the Sort Buttons
In Excel, it’s easy to sort your data by using the Sort buttons on the toolbar. But, be careful, or one column may be sorted, while others are not.
Select one cell in the column you want to sort.
Sort Data by One Column
Select all the cells in the list. This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list — but it’s not 100% certain. Some of the data may be missed.
From the Sort by dropdown, select the column you want to sort.Note: If the dropdown is showing Column letters instead of headings, change the setting forMy list has, fromNo header row to Header row.
Select to sort in Ascending or Descending order
Sort Data by 2-3 Columns
Select all the cells in the list.
From the Sort by dropdown, select the first column you want to sort.
Select to sort in Ascending or Descending order
From the Then by dropdown, select the second column you want to sort.
Select to sort in Ascending or Descending order
From the Then by dropdown, select the third column you want to sort.
Select to sort in Ascending or Descending order
Sort Data by 4+ Columns
Occasionally, you may need to sort by more than three columns. For example, in a mailing list, you may want to sort by Country, Region, City, and Name. To do this, you can sort the list multiple times, starting with the least important sort.
In the mailing list, there are four columns to sort. Name and City are the least important fields in the sorting process, so they can be sorted first.
Excel will retain what it can of this sort while you sort by the remaining fields.
From the Sort by dropdown, select Country.
From the Then by dropdown, select Region.
After sorting, the list is sorted by Country, then by Region, then by City and finally by Name.
More Tutorials
Sort Data With Macros Sorting Data Basics Sort a Row in Excel Numbers Don’t Sort Correctly
How To Sort In Excel
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.
One Column
To sort on one column, execute the following steps.
Result:
Multiple Columns
To sort on multiple columns, execute the following steps.
The Sort dialog box appears.
2. Select Last Name from the ‘Sort by’ drop-down list.
4. Select Sales from the ‘Then by’ drop-down list.
Result. Records are sorted by Last Name first and Sales second.
How To Sort By Color In Excel * Productivity Portfolio
Why Use Color
Perhaps, at one-time, color sorting and filtering wasn’t available, but you can do it now. The tutorial below can be used for font colors or cell color. For these instructions, I’ll be using Microsoft Office 365. However, the steps can be used in older Excel versions.
It’s probably my laziness, but I find it useful to change a cell’s background color. I might use this when I’m prioritizing or grouping a list. I need to highlight a cell and choose a background color.
In my screen snap above, I’ve applied the cell background color to Column C. However, some people might prefer using color across a row. In the second screenshot below, I used conditional formatting. Regardless of whether you’re using columns or rows, the process is the same. It also works with Excel tables.
How to Sort by Color in Excel
Tick the My data has headers checkbox in the top-right if your worksheet uses them.
Select your first fill color and keep the Order value as On Top.
Add in your other color levels. If you prefer, you can also use the On Bottom option.
You should now see your sorted list by color. You’re not limited to just 3 colors, but you can keep adding if they’re on your sheet. You also don’t need to enter the last color as Excel will put it on the bottom. In my case, that was ” No Cell Color.”
Select the column you wish to filter that has color.
Select your cell color from the side menu.
The same process works if you wish to sort by font color.
How to Filter by Color in Excel
Excel allows you to sort and filter by color
The color can be either the cell background color or font color
You can place colors on top or bottom
The system knows which colors you’ve used
No cell color is for normal cells
You can add multiple color sorts
Sort rules are processed in order
Some people prefer to filter instead of sort. This is great if you want to focus in on certain items. Even better, the procedure is shorter.
In my example, I was using a background color. However, when I added colored text, Excel adjusted the options. On the side menu for Filter by Color, you would also see Filter by Font Color.
Quick Takeaways
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-17
Bạn đang đọc nội dung bài viết How To Randomly Sort A List In Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!