A.N.M. Mohaimen Shanto
In Microsoft Excel, Sort & Filter is one of the essential tools. We use this for analyzing our data. If you have a large amount of data in your worksheets, you can use this to see data that fits your requirements. To find specific data from a large dataset, these tools help identify them effortlessly. In this tutorial, you will learn everything about Sort and Filter in Excel with suitable examples and proper illustrations.
Download Practice Workbook
Download this practice workbook.
What is Sort and Filter in Excel?
Basically, sort and filter options in Excel narrow down a dataset to analyze it better. It provides you with limited data that fits your requirement. The filter tool allows you to filter a column with your given criteria. The sorting tool allows you to sort data of datasets or a column based on alphabetical order, numbers, dates, etc.
Have a look at the following dataset:
Now, let’s see what it looks like after sorting based on Profit (Descending order):
Now, let’s filter the dataset based on the region “South”:
How to Enable Sort And Filter in Excel
Now, you can sort and filter data in various ways. If you noticed from the previous section, there was a dropdown menu beside the header. Basically, in this section, we will show you how to enable that.
First, click on any cell of the dataset. Then, go to the Data tab in the ribbon. From Sort & Filter group, click on Filter.
After that, you will see the dropdown beside every header of the column.
Now, why this is helpful? Isn’t it for the Filter just? Basically, you can perform both sorting and filtering from here. It will save you a lot of time.
Types of Sort in Excel
Now, you can sort your dataset in various ways. In the following sections, we are showing some of this that will give you a clear idea about sorting in Excel. To demonstrate this and other examples, we will use the previous dataset.
1. Sort in Alphabetical Order
Now, you can sort your data based on alphabetical order. If your column contains text, you can use this for sorting. There are two types of alphabetical sorting. A to Z sorting and Z to A sorting in Excel.
Let’s sort our dataset based on alphabetical order for the Names.
📌 Steps
- First, click on the dropdown menu beside the header “Name”.
- Then, click on the Sort A to Z It will sort the Name column in ascending order.
- Now, if you click on the Sort Z to A option, it will sort the dataset in descending order.
As you can see, we are successful to sort our data based on alphabetical order in Excel.
Read More: How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
2. Sort by Smallest to Largest in Excel
Now, it actually works the same as the A to Z sort we showed earlier. If you have numerical data, use this to sort them from smallest to largest. It will sort them in ascending order.
Here, we are going to sort the Total Sales column from smallest to largest.
📌 Steps
- First, click on the dropdown menu beside the Total Sales column.
- After that, click on Sort Smallest to Largest.
In the end, you will see it sorted the Total Sales column from smallest to largest order in Excel.
Related Content: How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)
3. Sort by Largest to Smallest
Similarly, you can sort the dataset from largest to smallest (descending order). Here, we are sorting the dataset for the Profit column.
📌 Steps
- At first, click on the dropdown menu beside the Profit column.
- Then, click on Sort Largest to Smallest.
As you can see, it sorted the Profit column from largest to smallest order in Excel.
Related Content: Excel Sort Dates in Chronological Order (6 Effective Ways)
Similar Readings
- Sum Using OFFSET and MATCH in Excel (With Alternative Options)
- How to Sort Rows in Excel (2 Simple Methods)
- Sort Column by Value in Excel (5 Methods)
- How to Sort by Color in Excel (4 Criteria)
- How to Sort Dates in Excel by Year (4 Easy Ways)
4. Multi-level Sort in Excel
Now, you can perform multi-level sorting in Excel. You can also call it “Custom Sort” Multi-level sorting means, you can sort the dataset based on multiple columns. This method will first sort the dataset based on the first criteria. After that, it will again sort that based on the second criteria over that sorted column. Let’s see it in action.
Here are our multi-level sorting criteria:
- First, sort based on Region (A to Z).
- Second, sort it again based on Total Sales (Largest to Smallest).
📌 Steps
- First, click on the dropdown menu beside “Region”.
- Then, select Sort by Color. After that, click on Custom Sort.
- Now, select Region from the Sort by dropdown menu. Also, select Cell values and A to Z from the respective dropdown.
- Next, click on Add Level.
- Now, select Total Sales from the Then by dropdown menu. Also, select Cell values and Largest to Smallest from the respective dropdown menu.
- After that, click on OK.
As you can see, we are successful to perform multi-level sorting in Excel.
Read More: How to Create Custom Sort List in Excel
5. SORT Function
Now, you can also sort any dataset by the SORT function of Excel. We do not use this function too often. Because this function is only available in Excel 365.
Make sure to check the sort function before you start using this.
Here, we are going to sort the dataset based on the Product column (Descending order).
📌 Steps
- First, type the following formula in Cell H5:
=SORT(B5:F15,3,-1)
- After that, press Enter.
As you can see, the SORT function sorted the dataset based on the product in descending order. Here, it returns a range or array. You won’t be seeing any header here. But, you can use this in various scenarios.
Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)
Types of Filter in Excel
Now, the filter tool allows you to narrow down the dataset based on your criteria. After filtering the data, it will only show you your desired values. In the upcoming sections, we will show you some type of filtering in Excel.
1. Regular Filter
A regular filter means you can filter the dataset based on any values. When you enable the filer dropdown menu in the dataset, you can easily filter them with it.
Let’s filter our dataset based on the Product “TV”.
📌 Steps
- First, click on the dropdown menu beside the Product.
- Now, uncheck the (Select All) option.
- After that, check the box “TV” and click on OK.
As you can see, only the data of the product “TV” is shown here after the filtering.
Read More: Excel Auto Sort When Data Changes (9 Examples)
2. Text Filters / Number Filters in Excel
Now, the Text Filters or the Number Filters option allows you to filter your data based on particular criteria. If your columns have text values, you can filter those using the Text Filters.
Excel will automatically show the Number Filters on columns containing numeric values.
Here, we will filter the dataset based on the Total Sales of less than $3500.
📌 Steps
- First, click on the dropdown menu beside Total Sales.
- After that, select Number Filters and click on the Less Than option.
- As we are filtering the data based on total sales of less than $3500, enter that in the field. After that, click on OK.
As you can see from the previous screenshot, we are successful to filter the data based on the Total Sales.
Related Content: How to Use Excel Shortcut to Sort Data (7 Easy Ways)
3. Custom Filter
Like the custom sort, you can also use the Custom Filter to implement criteria with the AND or the OR logic.
Here, our goal is to filter the dataset based on the Total Sales of greater than $2000 but less than $3000. So, our data will be between $2000 and $3000.
📌 Steps
- At first, click on the dropdown menu beside Total Sales.
- Next, select Number Filters and click on the Custom Filter.
- Here, we are filtering the data based on total sales of less than $3000 and greater than $2000. So, enter the values in the field. Next, select And After that, click on OK.
As you can see, we have filtered the dataset using the Custom Filter command in Excel.
Read More: [Fix:] Sort and Filter Not Working in Excel
How to Undo Sort and Filter in Excel
1. Undo Sort
Two ways you can undo the sorting in your worksheet:
- Click on the Undo Or by pressing the Ctrl+Z on your keyboard. But, remember, this method will only after you have just applied to sort. This is not a reliable method.
- Another way to undo sorting is by creating a temporary column in your dataset so that you can revert back based on this column.
Read More: How to Undo Sort in Excel (3 Methods)
2. Undo Filter
To undo any filtering from your dataset:
- First, click on the dropdown menu where you applied the filtering.
- Now, click on the Clear Filter From “Region” option (column name can be different based on your filtering). It will take you to the main dataset:
Now, to remove the dropdown menu of filtering, follow these steps:
- Click on any cell of your dataset.
- Then, go to the Data tab.
- Now, from the Sort & Filter group, click on Clear. It will take you to the original state of the dataset.
Related Content: Excel Sort and Ignore Blanks (4 Ways)
💬 Things to Remember
✎ Before sorting the dataset, try to make a copy of that. Or make an extra column of numbers in ascending order.
✎ Text Filters will appear only for Text values in a column.
✎ Number Filters will appear if there are numerical values in the column.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the sort and filter in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
