How to Do Advanced Sorting in Excel (9 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to apply Advanced Sorting Options in Excel


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.

Advanced Sorting of data top to bottom in Excel

  • 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.

Advanced Sorting of data left to right in Excel

  • 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.

Advanced Multi level Sorting in Excel

  • 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.

Advanced Sorting with case sensitivity

  • 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.

Advanced Sorting with cell color

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.

Advanced Sorting with conditional formatting

  • 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.

Advanced Custom Sorting in Excel

  • 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

The SORT Function is used to sort ranges or arrays.

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)

Advanced Sorting with Excel Formula

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

The SORTBY function sorts a range or array based on the values in a corresponding range or array. One of the benefits of using the SORTBY function is that the main data will not change. Another benefit is this function will sort data in ascending or descending order based on one of the ranges of the total sorting range.

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)

Advanced sorting using SORTBY function in Excel

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Ratul Khan
Ratul Khan

Hello! Here is my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and I am very interested in research.

2 Comments
  1. 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.

  2. Reply Avatar photo
    Md. Abdur Rahim Rasel Aug 30, 2022 at 10:51 AM

    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.
    Custom Sort
    After that, do like the below screenshot.
    Custom Sort
    Finally, you will get your desired output.
    Custom Sort
    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)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo