How to Use Advanced Sorting Options in Excel

How to Use Advanced Sorting Options in Excel

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.

Download Practice Sheet

Excel’s Advanced Sorting Options

In most cases, you have to do sorting of 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.

  1. Sort top to bottom
  2. Sort left to right
  3. Multi-Level Sorting
  4. Case sensitive sorting
  5. Cell Color and Font Color Sorting
  6. Sorting Based on Conditional Formatting
  7. Custom Sorting List

Before discussing these options look at the dataset below.

How to Use Advanced Sorting Options in Excel

1. Sort Top to Bottom (Advanced)

Step-1

Select a column that you want to sort. Say, for example, we select column B for sorting. Then, press the Data bar. Click on that bar & the Advanced Sorting option appears. If you press the option marked red, the Sort Warning menu appears.

Step-2

Click on the Expand the selection option and press the Sort button. Then, the ascending alphabetical order of column B appears.

2. Sort left to right  (Advanced)

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.

Step-1

Select row 2 and press the data bar. Now the Sort option appears.

Left to Right

Step-2

Press the Sort option. As a result, the Advanced Sorting option appears. Click on that Sort by button and select Row 2 among all the options.

Step-3

If you want to sort row 2 in the alphabetical ascending order then press the option A to Z among other options. Then, the ascending alphabetical order of row 2 from left to right appears.

3. Multi-Level Sorting

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.

Step-1

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.

Step-2

Press Ok. Then you can get the alphabetic order of the region with largest to smallest sales volume.

4.Case Sensitive Sorting

Select a cell within the data that you want to sort and then go to the Data tab and click Sort.

Step-1

Press Data  => Click Sort button => Sort menu opens

Step-2

Press Sort by button=> select Product => Click  A to Z=> press Ok

Result of the Case Sensitive Sorting appears.

How to Use Advanced Sorting Options in Excel

 

5. Cell Color and Font Color Sorting

Step-1

Press Sort & Filter option=> select Custom Sort

Step-2

Custom Sort menu appears=> press Sort by => select Region

 Press Sort On => select Cell Color => Choose any color => press OK

Column C appears as a Color Sorting.

6. Sorting Based on Conditional Formatting

How to Use Advanced Sorting Options in Excel

Step-1

Press Conditional Formatting => select Top/Bottom Rules => click Top 10 Items

Step-2

Format cells that are greater than Any Number => select any Color => press Ok

Sorting based on Color Formatting appears where sales volume which is more than 50 is bolded with Light Red Color in column D.

7. Custom Sorting 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.

Step-1

Press Sort & Filter => select  Custom Sort

 

Step-2

Press Sort by => select High Medium Low option => Click ADD

Dataset is arranged with Selling performance

Conclusion

So, we learned how to sort data by using Advance Sorting Options. Hope you find this article quite helpful. Feel free to ask any questions or share your opinion in the comment section.


Further Readings:

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo