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. 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 Sorting by Multiple ColumnsIn 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 SortingCustom 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. 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 ConditionFind 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 Filtering Based on Multiple Columns and ConditionsYou 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! ConclusionThrough 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. What is the use of sort data?Sorting is the process of arranging data into meaningful order so that you can analyze it more effectively. For example, you might want to order sales data by calendar month so that you can produce a graph of sales performance. You can use Discoverer to sort data as follows: sort text data into alphabetical order.
What is the sorted data?Data sorting is any process that involves arranging the data into some meaningful order to make it easier to understand, analyze or visualize. Data is typically sorted based on actual values, counts or percentages, in either ascending or descending order, but can also be sorted based on the variable value labels.
What is sorted 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.
What is the use of sort in spreadsheet?Sorts the rows of a given array or range by the values in one or more columns.
|