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.
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 requirements. 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 Undo Sort in Excel
Similar Readings
- Auto Sort in Excel Without Macros
- How to Sort in Excel by Number of Characters
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
- How to Add Sort Button in Excel
- How to Sort by Last Name in Excel
- How to Perform Custom Sort in Excel
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.
Read More: Excel Auto Sort when Data is Entered
Similar Readings
- Excel Auto Sort when Data Changes
- How to Sort Data in Excel by Value
- How to Sort Data in Alphabetical Order in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Sort Multiple Columns in Excel
- How to Auto Sort Multiple Columns in Excel
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.
Read More: How to Sort Two Columns in Excel to Match
Similar Readings
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Sort Alphabetically in Excel and Keep Rows Together
- How to Sort by Date in Excel
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
- How to Sort Numbers with Letter Suffix in Excel
- How to Sort Dates in Chronological Order in Excel
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 in performing multi-level sorting in Excel.
Read More: How to Sort by Month in Excel
Similar Readings
- How to Sort Excel Sheet by Date
- Sort IP Address in Excel
- Random Sort in Excel
- How to Sort Birthdays in Excel by Month and Day
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
- Advanced Sorting in Excel
- How to Sort Duplicates in Excel
- Excel Sort Unique
- How to Sort Numbers in Excel
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter 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 Sort by Name in Excel
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:
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 in filtering the data based on the Total Sales.
Read More: How to Remove Sort in Excel
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: Sort and Filter in Excel Not Working
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.
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.
Read More: Excel Sort and Ignore Blanks
💬 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.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
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 for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
- Sorting Columns in Excel While Keeping Rows Together
- How to Use Excel Shortcut to Sort Data
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- How to Sort Columns in Excel Without Mixing Data
- How to Arrange Numbers in Ascending Order with Excel Formula
- How to Sort Dates in Excel by Month and Year
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working