Đề Xuất 3/2023 # Revert To Original Sort Order In Excel # Top 12 Like

Cập nhật nội dung chi tiết về Revert To Original Sort Order 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.

Bottom line: Learn how to retain and revert to the original sort order of a range or table in Excel.

Skill level: Beginner

Video Tutorial

Often times we receive a data set that does not have a column of sequential numbers or dates. If we sort one or more columns in the range, it can be difficult or impossible to revert back to the original sort order.

One way to solve this problem is to add a column of sequential numbers to the data set. I typically refer to this as an index column.

After sorting the data you can then go back and sort the index column to restore the original sort order.

Setting up the Index Column

It’s important to setup the index column BEFORE you apply any sorting to the data.

Here are the steps to create the index column:

Type a 1 in a blank column to the right of the data range/table.

Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…

IMPORTANT: If you are NOT using an Excel Table then you will need to reapply the filters to include this new column. This must be done BEFORE you sort the other columns so that the index column is included in the sort.

If you are using an Excel Table then you do not have to worry about step 4. The new column will automatically be included in the table as long as you create it directly to the right of the last column. See the video above for more details.

Checkout my video on a Beginner’s Guide to Excel Tables to learn more about the benefits of using this awesome feature of Excel.

Here is another method for using the fill handle.

Checkout my article on Fill Handle Hacks to learn more about this technique.

Other Techniques for Retaining Sort Order?

My friend Robbie asked me this question about retaining sort order the other day, which sparked the idea for this post, and this index column method is the one I’ve always used.

I’ve also mentioned it before in my article on how to prevent Excel from freezing when deleting rows.

However, I’m curious to know if you have other techniques or methods to solve this problem. Is there a different or better way?

How To Sort Data By Color In Excel?

How to sort data by color in excel?

When you using a worksheet, sometimes you may fill the rows or cells with various colors to make the worksheet much readable. And sometimes you want to sort the cells by color in Excel. In this case, you can use the sort function to sort the data by color quickly as follows:

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier…

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.

How To Randomly Sort A List In Excel

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.

Bạn đang đọc nội dung bài viết Revert To Original Sort Order 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!