Sorting and filtering are two popular features available in Microsoft Excel. They are widely used in data analysis to organize, arrange, and subset your data based on specific conditions. In this article, you will learn how to sort data in Excel. Additionally, you will also learn how to filter data.
Given below are the topics that you will learn in this article on how to sort data 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.
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.
Ctrl + Shift + L.
Fig: Sorting by List Price
Let’s now sort the data based on the date on which we listed the houses.
Fig: Sorting based on Date Listed column
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.
Fig: Sorting based on Area and Agent columns
The next topic in this article on how to sort data in Excel is 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.
Fig: Custom Sort list
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.
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.
Find all the houses in the Central Area.
Fig: Houses in the Central Area
Find all the houses with 3 or 4 bedrooms.
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
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.
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.
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
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!
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.
Shruti is an engineer and a technophile. She works on several trending technologies. Her hobbies include reading, dancing and learning new languages. Currently, she is learning the Japanese language.
Business Analytics with Excel
*Lifetime access to high-quality, self-paced e-learning content.
Explore CategoryBig Data Career Guide: A Comprehensive Playbook to Becoming a Big Data Engineer
Data Analysis in Excel: The Best Guide
How to Become a Big Data Engineer?
A Guide on How to Become a Site Reliability Engineer (SRE)
How to Transpose Excel Data? Three Techniques You Need to Know
The Best Guide On How To Become A Business Analyst