Ratul Khan
Excel has advanced sorting options that can help you to perform multi-level sorting within a large database. Sometimes alphabetical or numerical sorting do not suffice. In such cases, advanced sorting options are required. In this article, you are going to learn several effective techniques for Advanced Sorting in Excel.
How to Do Advanced Sorting in Excel (9 Examples)
In most cases, you must sort a single column or row. But there are some other cases where you might have to perform Sorting two-column or more than two columns. In such cases, Advanced Sorting Options become convenient.
We are going to discuss three Advanced Sorting Options in this article. These are given below.
- Sort top to bottom
- Sort left to right
- Multi-level sorting
- Case-sensitive sorting
- Sorting based on cell color and font color
- Sorting using conditional formatting
- Using a custom list
- Using SORT, and SORTBY functions
Before discussing these options look at the dataset below.
1. Sorting Top to Bottom
- First, select a column that you want to sort. Say, for example, we select Column C for sorting.
- Then, press the Data tab. Click on that tab & the Advanced Sorting option appears.
- If you press the option marked red, the Sort Warning menu appears.
- Click on the Expand the selection option and press the Sort button.
- Then, the ascending alphabetical order of Column C appears.
2. Sorting from Left to Right
Prices of different types of shirt sizes are given in the chart. We want to sort these shirt sizes according to the ascending alphabetical order from left to right.
📌 Steps:
- Select Rows 4 to 6 and press the Data bar. Now the Sort option appears.
- We untick the My data has headers option.
- Then, click on the Options button.
- Choose Sort left to right option from the Sort Options window.
- Click on that Sort by button and select Row 4 among all the options, Cells Values as Sort On and A to Z as Order.
- Look at the dataset. The ascending alphabetical order of row 4 from left to right appears.
Read More: How to Undo Sort in Excel
Similar Readings
- Auto Sort in Excel Without Macros
- How to Sort in Excel by Number of Characters
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
- How to Perform Custom Sort in Excel
- How to Add Sort Button in Excel
- How to Sort by Last Name in Excel
3. Multi-Level Sorting in Excel
If you want to sort multiple columns of a large database under specific conditions, you can do it by using the Advanced Sorting option in Excel. Consider the column used in method 1.
📌 Steps:
- Press on the data bar and click Sort. A menu bar appears. Select Sort by option & click Region. Click the Order option and select A to Z.
- Now click the Add Level button and another option Then by appears. Select the Sales volume option & click the Largest to Smallest option in the order menu.
- Press Ok. Then you can get the alphabetic order of the region with the largest to smallest sales volume.
Read More: Excel Auto Sort when Data is Entered
Similar Readings
- Excel Auto Sort when Data Changes
- How to Sort Data in Excel by Value
- How to Sort Data in Alphabetical Order in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Auto Sort Multiple Columns in Excel
4. Case Sensitive Sorting
In this section, we will sort data from the Product column. This column has the same product name with a case difference. We have to sort them out.
📌 Steps:
- Select the whole dataset. As we want to sort and then go to the Data tab and click Sort as shown before.
- Then, choose Product as Sort by, Cell Values with Sort On, and A to Z as Order fields.
- Click on the Options button.
- Mark on the box of the Case sensitive.
- Then, click on OK on the two windows.
- The result of the Case Sensitive sorting appears.
Read More: How to Sort and Filter Data in Excel
Similar Readings
- How to Sort Two Columns in Excel to Match
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Sort Alphabetically in Excel and Keep Rows Together
- How to Sort by Date in Excel
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
- How to Sort Numbers with Letter Suffix in Excel
- How to Sort Dates in Chronological Order in Excel
- How to Sort by Month in Excel
5. Sorting Based on Cell Color and Font Color
Assume, our data is filled with a different color. So, we want to sort this data based on cell color or font color.
📌 Steps:
- We go to Data => Sort.
- Now, customize the Sort window.
- Press Sort by => Region, Sort On => Cell Color, Order=> Choose any color.
- Finally, press OK.
We added three levels for three cell colors.
- Column D appears as a Color Sorting.
Read More: How to Sort Dates in Excel by Month and Year
Similar Readings
- How to Sort Excel Sheet by Date
- Sort IP Address in Excel
- Random Sort in Excel
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
- How to Sort Duplicates in Excel
- Excel Sort Unique
6. Advanced Sorting by Using Conditional Formatting
In this section, we will use Conditional Formatting and then apply the sort operation.
📌 Steps:
- Select Column E. Press Conditional Formatting => Data Bars.
- Choose a color from the Solid Fill section.
- We can see bars are added with the data.
- Now, go to Data => Sort Largest to Smallest.
- Choose Expand the selection from the Sort Warning window.
- Finally, click on the Sort option.
- Sorting based on Color Formatting appears based on sales values in descending order.
Read More: How to Sort by Name in Excel
Similar Readings
- How to Sort Numbers in Excel
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter in Excel
- Sort and Filter in Excel Not Working
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working
7. Sorting Based on a Custom List
Let’s Sales volumes less than 40 are marked with low performance. Sales volumes greater than 40 but less than 60 are marked with medium performance. Sales volumes greater than 60 are marked with high performance.
📌 Steps:
- Choose the data range first.
- Press Sort & Filter => Custom Sort option.
- Press Sort by => High Medium Low option =>ADD.
- Click on the OK button then.
- The dataset is arranged with the Sales performance column.
8. Sorting by Using SORT Function
In this section, we will use a formula based on the SORT function to sort data in Excel. We will sort the data of Column E on Column F. To do that, put the following formula on Cell F5 to sort the whole column.
=SORT(E5:E14)
We can see data sorted in ascending order.
Read More: How to Remove Sort in Excel
9. Excel SORTBY Function for Advanced Sorting
Here, we will use the SORTBY function for advanced data sorting.
📌 Steps:
- Look at the dataset.
- We can see our dataset is divided into two parts. 1st one is before sorting, and the 2nd one is after sorting.
- Now, put the following formula on Cell F6.
=SORTBY(B6:D15,D6:D15,-1)
We can see data has been sorted considering the Sales Volume column in descending order.
Read More: How to Arrange Numbers in Ascending Order with Excel Formula
Download Practice Workbook
You can download the following practice workbook to practice while you are reading this article.
Conclusion
So, we learned how to sort data by using Advance Sorting Options. I hope you find this article quite helpful. Feel free to ask questions or share your opinion in the comment section. Please have a look at our website ExcelDemy for further problems.
Related Articles
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
- Sorting Columns in Excel While Keeping Rows Together
- How to Use Excel Shortcut to Sort Data
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- Excel Sort and Ignore Blanks
- How to Sort Columns in Excel Without Mixing Data
- How to Sort Birthdays by Month and Day in Excel
In the last example, the table is supposed to be sorted by Selling performance (column E) using a custom order list. However, the result is not in the correct order. The column should be in the order: High Medium Low. Instead, it’s in standard alphabetical order.
Hello JACK,
You can easily do that using the Custom Sort command. Let’s follow the instructions below:
Let’s Sales volumes less than 40 are marked with low performance. Sales volumes greater than 40 but less than 60 are marked with medium performance. Sales volumes greater than 60 are marked with high performance.
First of all, select cells range E5 to F14, and press right-click. As a result, a window pops up. From that window select Custom Sort under the Sort option.
After that, do like the below screenshot.
Finally, you will get your desired output.
Please download the below Excel file for your practice.
https://www.exceldemy.com/wp-content/uploads/2022/08/Advanced-Sorting-Options.xlsx
Thank you for your comment.
Regards
Md. Abdur Rahim Rasel(Exceldemy Team)