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 Perform Custom Sort 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: How to Perform Random Sort 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
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: Advantages of Sorting Data in Excel
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.
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 Sort Drop Down List 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.
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
<< Go Back to Sort in Excel | Learn 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)