Bạn đang xem bài viết Revert To Original Sort Order 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.
Bottom line: Learn how to retain and revert to the original sort order of a range or table in Excel.
Skill level: Beginner
Video TutorialOften 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 ColumnIt’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 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 TakeawaysDisclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2023-04-17
How To Use The Excel Sort Function
The SORT function sorts the contents of a range or array in ascending or descending order with a formula. The result from SORT is a dynamic array of values that will “spill” onto the worksheet into a range. If values in the source data change, the result from SORT will update automatically.
The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting. For example, to sort by the third column in a range of data, use 3 for sort_index. The optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, the SORT function will sort data vertically by rows. To sort a range horizontally by columns, set the fourth argument, by_col, to TRUE.
Basic ExamplesTo sort a range by the first column in ascending order:
=
SORT
(
range)
// sort by column 1, ascending
=
SORT
(
range,
1
,
1
)
// sort by column 1, ascending
To sort a range by the first column in descending order:
=
SORT
(
range,
1
,
-
1
)
// sort by column 1, descending
To sort a range by the second column in descending order:
=
SORT
(
range,
2
,
-
1
)
// sort by column 1, ascending
To sort a range horizontally (by column) using values in row 1 in descending order, set the fourth argument, by_col, to TRUE or 1:
=
SORT
(
range,
1
,
-
1
,
TRUE
)
=
SORT
(
range,
1
,
-
1
,
1
)
Example – sort by scoreIn the example shown above, data includes names in column B and scores in column C. In cell E5, the SORT function is used to sort the data by score in descending order:
=
SORT
(
B5:C14,
2
,
-
1
)
// sort by scores in descending order
The SORT function extracts all values, sorted in descending order by score, and results “spill” into the range E5:F14. To sort by score in ascending order, omit sort_order or to 1 like this:
=
SORT
(
B5:C14,
2
)
// sort by score in ascending order
=
SORT
(
B5:C14,
2
,
1
)
// sort by score in ascending order
Sort by more than one levelUnlike the SORTBY function, the SORT function does not provide a way to sort data by more than one level. However, by using array constants for sort_index and sort_order, it can be done. This video explains how.
SORT is a new function available in Excel 365 only.
How To Sort And Filter Data In Excel
Sorting and filtering data offers a way to cut through the noise and find (and sort) just the data you want to see. Microsoft Excel has no shortage of options to filter down huge datasets into just what’s needed.
Advertisement
The first and most obvious way to sort data is from smallest to largest or largest to smallest, assuming you have numerical data.
We can apply the same sorting to any of the other columns, sorting by the date of hire, for example, by selecting the “Sort Oldest to Newest” option in the same menu.
How to Filter Data in ExcelBecause our list is short, we can do this a couple of ways. The first way, which works great in our example, is just to uncheck each person who makes more than $100,000 and then press “OK.” This will remove three entries from our list and enables us to see (and sort) just those that remain.
Advertisement
We can also combine filters. Here we’ll find all salaries greater than $60,000, but less than $120,000. First, we’ll select “is greater than” in the first dropdown box.
In the dropdown below the previous one, choose “is less than.”
Next to “is greater than” we’ll put in $60,000.
Next to “is less than” add $120,000.
Advertisement
How to Filter Data from Multiple Columns at Once
In this example, we’re going to filter by date hired, and salary. We’ll look specifically for people hired after 2013, and with a salary of less than $70,000 per year.
Add “70,000” next to “is less than” and then press “OK.”
Type “2013” into the field to the right of “is after” and then press “OK.” This will leave you only with employees who both make less than $70,000 per year who and were hired in 2014 or later.
Advertisement
Excel has a number of powerful filtering options, and each is as customizable as you’d need it to be. With a little imagination, you can filter huge datasets down to only the pieces of information that matter.
Cập nhật thông tin chi tiết về Revert To Original Sort Order 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!