How to Sort Data in Excel: A Step by Step Guide

What is Sorting in Excel?

Sorting is a feature in MS Excel that helps you organize data. You can sort a text column in alphabetical order (A-Z or Z-A). We can sort a numerical column from largest to smallest or smallest to largest. We can also sort a date and time column from oldest to newest or newest to oldest. Sorting in Excel can also be done by a custom list or by formats, such as cell color, font color, or icon set.  

Become a Data Scientist With Real-World Experience

Data Scientist Master’s ProgramExplore Program

Sorting a Single Column

Consider the housing dataset depicted below. It has information about the agent, the date on which the house was listed, the price, number of bedrooms, and so on.

Fig: Housing Dataset

Let’s sort the data based on the Price of each house in descending order.

• To sort a single column, you can select the data and use the shortcut key - 

Ctrl + Shift + L.

• Click on the down arrow on the List Price column. Select Largest to Smallest.

Fig: Sorting by List Price

Let’s now sort the data based on the date on which we listed the houses.

• Select the dataset > Click on the Sort option in the Data tab.  
• Choose the column you want to sort.
• Select Cell Values under Sort On.
• Under Order, choose Oldest to Newest.

Fig: Sorting based on Date Listed column

Sorting by Multiple Columns

In most cases, you need to sort a single column. But there may be instances where you need to sort two or more columns. You can use advanced sorting techniques to sort data by multiple columns.

Let’s sort the housing dataset in ascending order of Area and descending order of Agent.

• Select the dataset > Click on the Sort option in the Data tab.  
• Choose the Area column to sort.
• Select Cell Values under Sort On.
• Under Order, choose A-Z.
• Select Add Level, and choose the Agent column.
• Now sort on Cell Values and Order it by Z-A.

Fig: Sorting based on Area and Agent columns

The next topic in this article on how to sort data in Excel is Custom Sorting. 

The Ultimate Ticket to Top Data Science Job Roles

Post Graduate Program In Data ScienceExplore Now

Custom Sorting

Custom Sorting in Excel is used to define your own custom order. At times, you may need to sort data that is not an alphabetical or ascending sort. Excel lets you create your own custom lists to sort data. 

Suppose you want to sort the dataset based on Area in the following order - S. County, Central, and N. County.

• Select the dataset > Click on the Sort option in the Data tab.
• Choose the Area column to sort.
• Under Sort On, select Cell Values.
• Under Order, choose the Custom List.

Fig: Custom Sort list

• In the Custom Lists dialog box, add the List entries separated by commas - S. County, Central, N. County. 
• Click on Add > Select Ok.

Shown below is the output of the same. 

Fig: Sorting based on customized Area column

Now that you learned how to sort data in Excel, let’s now understand how to filter data.

What is a Filter in Excel?

We use filters in Excel to temporarily hide some of the data in a table. It helps you subset the data and display records that meet certain criteria. Here, you have the flexibility to focus on the data that interests you.

Filtering Based on a Single Condition

Find all the houses in the Central Area.

• Select the data > Hit Ctrl + Shift + L OR Select the data > Data tab > Under Sort and Filter, choose the Filter icon. 
• Click on the drop-down present in the Area column.
• Uncheck Select All and select the Central Area.

Fig: Houses in the Central Area

Find all the houses with 3 or 4 bedrooms.

• Select the data > Under the data tab, choose the Filter icon.
• Click on the drop-down present in the Bedrooms column.
• Uncheck Select All and select the 3 and 4.

Fig: Criteria based on bedrooms

Attached below is the filtered housing data with 3 or 4 bedrooms selected.

Fig: Houses with 3 or 4 bedrooms

Become a Data Scientist With Real-World Experience

Data Scientist Master’s ProgramExplore Program

Filtering Based on Multiple Columns and Conditions

You can use multiple filters to narrow down your results. When there are multiple columns and conditions involved, you need to use the advanced filtering option. Excel provides advanced filtering tools, such as search, date, text, and number filtering.

Let’s display the list of all the houses in the Central region with Pool and S. County without Pool.

• Create a criteria range with a list of all the columns in the table. 
• Add Central and S. County under the Area column. 
• Add TRUE for Central under the Pool column.
• Add FALSE for S. County under the Pool column.

• Go to the Data tab > Under Sort and Filter, select Advanced.

• Choose filter the list, in-place, or Copy to another location.
• The List range has the original data selected.
• Choose the Criteria range which is shown above, click OK.

Fig: Houses in the Central region with Pool and S. County without Pool

Find the Agents with a house in the N. County area having 2 bedrooms and a single type family.

Depicted below is the criteria range for the above problem.

• In the Data tab, under Sort & Filter, select Advanced.
• Choose filter the list, in-place, or Copy to another location.
• The List range has the original data selected.
• Choose the Criteria range with Area - N. County, Bedroom - 2, and Type - Single Family.
• Click on OK.

Fig: Houses in N. County with 2 bedrooms and a single type family

Display all the houses whose list price is between 3,00,000 to 4,00,000

To solve this problem, you need to create two List Price columns, with one column having a value “>=300000” and the other having “<=400000”.

Give the right criteria for range in the Advanced Filter dialog box and click OK.

Below is how the filtered data would look like:

      Fig: Houses with a list price is between 300000 to 400000

Find all the houses in the N. County area with a list price greater than 300000 and having 3 or 4 bedrooms.

Here is how the criteria range should be shown.

Below is the final filtered data based on the appropriate conditions.

Fig: Houses in N. County with a list price greater than 300000 and having 3 or 4 bedrooms

Conclusion

Through this article, you have understood how to sort data in Excel. You saw how to sort data on a single column. You understood how sorting works when there are multiple columns involved. You also got an idea about the custom sorting feature of MS Excel. In addition to this, you also learned to filter data in Excel. 

Do you have any questions about this article on How to sort data in Excel? If so, then please put your queries in the comments section of the video. We’ll be happy to answer them. Watch this video to learn more: Sorting and Filtering data in Excel.

Start your data science journey by becoming an expert in Data with Simplilearn's Data Science Bootcamp, delivered in partnership with IBM. Designed for working professionals, the program features masterclasses by distinguished Caltech instructors & IBM experts, with exclusive hackathons and Ask Me Anything sessions by IBM.