Microsoft Excel offers two widely used features: sorting and filtering. They are frequently used in data analysis to set up, organize, and subset your data according to particular criteria. You will discover how to sort data in Excel in this article. You will also get knowledge of data filtering.
What is Sorting in Excel?
Sorting is the process of arranging the strings or integers so that they can be placed in ascending or descending order. A text column can be sorted alphabetically (A-Z or Z-A). A number column can be sorted either from largest to smallest or from smallest to largest. A date and time column can alternatively be sorted from oldest to newest or from newest to oldest. Excel also has the option of sorting using a custom list or formats like cell color, font color, or icon set.
In this article, we will look into how we can do Sorting in Excel.
Sorting a Single Column in Excel
Consider the Employee dataset depicted below. It has information about the employees, the Job Title, Department, Gender, and so on.
Employee Dataset
Let’s sort the data based on the Annual Salary of each Employee in descending order.
- You can choose the data and the shortcut key Ctrl + Shift + L to sort just one column.
- Select the List Annual Salary column’s downward pointing arrow. From largest to smallest, choose.
Sorting Employee Dataset
Sorting by Multiple Columns
Most often, only one column needs to be sorted. However, there can be times when you need to sort across many columns. Data can be sorted by several columns using advanced sorting methods.
Let’s sort the Employee dataset in ascending order of Annual Salary and descending order of Age.
- Step 1: Select the dataset > Click on the Sort option in the Data tab
- Step 2: To sort, select the Annual Salary column
- Step 3: Select Values under Sort On
- Step 4: Under Order, choose Smallest to Largest/A-Z
- Step 5: Select Add Level, and choose the Age column
- Step 6: Now Sort on Cell Values and Order it by Largest to Smallest/Z-A
Multiple Columns Sorting
Sorting Strings
Step 1: Formatting data for sorting.
Step 2: Converting data from unsorted to sorted order in ascending order. First, highlight the data which we want to sort.
Step 3: Then, click to Data on the Ribbon. In the Sort & Filter group, click Sort.
Step 4:- In the Sort box, select A to Z in order to sort the data in ascending order, then click Ok.
Now, data are sorted in ascending order.
Step 5: Now, if we want to sort in descending then we have to select Z to A in order to sort the data in descending order, then click OK.
Now, data are sorted in descending order.
Sorting Integers
Step 1: Formatting data for sorting.
Step 2: Converting data from unsorted to sorted order in ascending order.
First, highlight the data which we want to sort.
Step 3: Then, click to Data on the Ribbon. In the Sort & Filter group, click Sort.
Step 4: In the Sort box, select Smallest to Largest in Order to sort the data in ascending order, then click Ok.
Now, data are sorted in ascending order.
Step 5: Now, if we want to sort in descending then we have to select Largest to Smallest in Order to sort the data in descending order, then click Ok.
Now, data are sorted in descending order.
Custom Sorting
You can create your own custom order in Excel by using custom sorting. Data that cannot be sorted alphabetically or ascending may occasionally need to be sorted. To sort data, Excel enables you to make your own unique lists.
Suppose you want to sort the dataset based on Department in the following order – IT, Sales, Marketing
- Select the dataset > Click on the Sort option in the Data tab
- Choose the Department column to sort
- Under Sort On, select Cell Values
- Choose the Custom List, under Order
- In the Custom Lists dialog box, add the List entries separated by commas – IT, Sales, Marketing
- Click on Add > Select OK.
Custom Sorting
The result of the same is displayed below.
Sorted Data
Data sorting using a single column was demonstrated. You were aware of how sorting functions when numerous columns are involved. Additionally, you learned about MS Excel’s capability for custom sorting.
FAQs on Sorting in Excel
1. What are the types of sorting in Excel?
There are 3 ways in which you can sort data in Excel.
- Sort A to Z (Sorts in Ascending Order)
- Sort Z to A (Sorts in Descending Order)
- Custom Sort (Sorts by applying different sort criteria)