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

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

### 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

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)`

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)`

We can see data has been sorted considering the Sales Volume column in descending order.

## 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

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.

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.

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.

After that, do like the below screenshot.

Finally, you will get your desired output.