How to Sort in Excel (All You Should Know)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will demonstrate some easy methods to sort data in Excel. Sometimes it’s difficult to find data of interest in a random dataset. Sorting data can be helpful to search for desired values in an Excel sheet.

Manually sorting a large worksheet is a tiresome job. Excel provides some useful tools and features that you can use to sort datasets with ease.

Knowing how to sort is essential for data analysis, as it helps you analyze data easily. Sorting can make data more visually appealing and intelligible.


Download Practice Workbook

You can practice on your own by downloading the free Excel worksheet.


How to Sort in Excel (6 Use Cases)

1. Sort in Ascending Order

  • To sort the dataset in Ascending order according to the Writer Names, select any cell in the Writer column and go to Data Tab > Sort & Filter Group > Sort A to Z.
  • The dataset is sorted accordingly.
Note: You can also access the Sort & Filter Group by going to Home Tab > Editing Group> Sort & Filter or by clicking the Right-button on the mouse and going to Sort.

Sort in ascending order


2. Descending Sort in Excel

  • To sort the dataset in descending order according to Quantity Sold, select any cell in the Quantity Sold and go to Data Tab > Sort & Filter Group > Sort Z to A.
  • You will notice that the dataset is sorted accordingly.

Sort in descending order


3. Custom Sorting

  • We have created a column with the heading Characteristics next to the dataset.
  • The books that have sold more than 25 copies are given the characteristic of Best Seller, the books that have sold more than 15 but less than 25 copies are given the characteristic of Midlist, and the books that have sold less than 15 copies are given the characteristic of Backlist.
  • We will sort the books according to their Characteristics. To facilitate creating a custom list, we have kept the Characteristics in a separate column.

Characteristic column for custom sort

  • Go to File > Options > Advanced. Scroll down and find the Edit Custom Lists.

Select Edit Custom List for custom sorting

  • This will bring out the Custom Lists
  • Click on the Import List button to import the custom list. The list will be shown in the List Entries Click on OK to add the list in the custom list options.

Import custom list

  • Select any cell in the Characteristics column and go to Data Tab > Sort & Filter Group > Sort.

Select Sort from Sort & Filter

  • Select Characteristics as Column Select Custom List from Order.

Select Custom list to filter with custom list

  • Select the custom list that we created and click on OK.

Select custom list to sort with

  • Click on OK in the sort window.

Click on OK to sort with custom list

  • The dataset is now sorted according to the custom list that we created.

Custom sorted dataset


4. Sorting with Multiple Levels

  • To sort with multiple levels, go to Data Tab > Sort & Filter Group > Sort.

Select sort from the Sort & Filter group

  • Select Writer as Column input and the order is A to Z.

select column and order

  • Click on Add Level in the top left corner and select Book as Column input and the order is A to Z.
  • Click on OK to sort the dataset.

Sort with multiple levels

  • The dataset is now sorted according to multiple levels.

Sorted data according to multiple levels

Note: You can use this method to sort with multiple rows or columns in Excel.

5. Using Filter to Sort Data

  • Select any cell in the dataset and go to Data Tab > Sort & Filter Group > Filter. The headers will have a down arrow next to them indicating the filter is on.

turn on Filter from Sort & Filter

  • Select the down arrow next to the column header by the column you want to sort.
  • We select Sort Z to A from the Book column down arrow. The dataset is now sorted.

Using filter to sort data


6. Sort by Color

  • The best-seller books are colored green and the backlist books are colored yellow.
  • Select any cell from the books column and go to Data Tab > Sort & Filter Group > Sort.
  • Select Book as Column input and Cell Color as the Sort On option.
  • Select the color Green in the order panel and from the dropdown list, select On Top to show green colored books on top.

select appropriate inputs to sort by color

  • Copy this level by clicking on Copy Level and for the color Yellow, select On Bottom.
  • Click on OK to sort the dataset.

copy level and select appropriate inputs

  • The dataset is now sorted according to color.

Sorted dataset according to color


Things to Remember

  • Make sure there are no blank cells in the column that you want to sort by otherwise, you will get unwanted results.
  • If the column headers are in the same format as the dataset, they are most likely to end up somewhere in the middle of the sorted data. You can prevent this by selecting only the data without headers.
  • If there are any merged cells in the dataset, this may lead to unexpected results.

Frequently Asked Questions

  1. Can sort filtered data?

A: Yes you can sort filtered data, it is more easy to sort filtered data than unfiltered data.

  1. If I have any duplicate entries in my dataset, how will Excel sort those?

A: Excel does not sort duplicates in any particular order. The data that occurs first will be shown first after sorting.

  1. Can I sort data in Excel based on cell color?

A: Yes, you can sort data based on cell color by utilizing the custom sort feature.


How to Sort in Excel: Knowledge Hub


Conclusion

We hope you find this article useful in solving your problems regarding sorting in Excel. Sorting is frequently used in Excel to make the dataset visually appealing. Go over the methods described here to get a better understanding of how to sort in Excel.


<< Go Back to 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.

Tags:

Maruf Niaz
Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo