How to Sort and Filter Data in Excel (A Complete Guideline)

Get FREE Advanced Excel Exercises with Solutions!

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):

Sort and Filter in Excel

Now, let’s filter the dataset based on the region “South”:

Sort and Filter in Excel


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.

Sort and Filter in Excel

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.

Sort in Alphabetical Order

  • Now, if you click on the Sort Z to A option, it will sort the dataset in descending order.

Sort in Alphabetical Order

As you can see, we are successful to sort our data based on alphabetical order in Excel.

Read More: How to Perform Random 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.

Sort by Smallest to Largest in Excel

  • After that, click on Sort Smallest to Largest.

Sort by Smallest to Largest in Excel

In the end, you will see it sorted the Total Sales column from smallest to largest order 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.

Sort by Largest to Smallest

As you can see, it sorted the Profit column from largest to smallest order in Excel.

Read More: How to Sort Excel Tabs


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”.

Multi-level Sort in Excel

  • 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.

Multi-level Sort in Excel

  • 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.

Multi-level Sort in Excel

As you can see, we are successful in performing multi-level sorting 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)

SORT Function

  • After that, press Enter.

SORT Function

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: Advantages of Sorting Data 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.

Filter in Excel

  • Now, uncheck the (Select All) option.

  • After that, check the box “TV” and click on OK.

Filter in Excel

As you can see, only the data of the product “TV” is shown here after the filtering.


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.

Filter in Excel

  • After that, select Number Filters and click on the Less Than option.

Filter in Excel

  • As we are filtering the data based on total sales of less than $3500, enter that in the field. After that, click on OK.

Filter in Excel

As you can see from the previous screenshot, we are successful in filtering the data based on the Total Sales.

Read More: How to Perform Custom 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.

Filter in Excel

As you can see, we have filtered the dataset using the Custom Filter command in Excel.

Read More: Advanced Sorting 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.

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:

Filter in Excel

Now, to remove the dropdown menu of filtering, follow these steps:

  • Click on any cell of your dataset.
  • Then, go to the Data tab.

Filter in Excel

  • Now, from the Sort & Filter group, click on Clear. It will take you to the original state of the dataset.

Read More: Difference Between Sort and Filter in Excel


💬 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


<< Go Back to Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo