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.

Table of Contents hide

What is Sort and Filter in Excel?

How to Enable Sort And Filter in Excel

Types of Sort in Excel

1. Sort in Alphabetical Order

2. Sort by Smallest to Largest in Excel

3. Sort by Largest to Smallest

4. Multi-level Sort in Excel

5. SORT Function

Types of Filter in Excel

1. Regular Filter

2. Text Filters / Number Filters in Excel

3. Custom Filter

How to Undo Sort and Filter in Excel

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

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.

Similar Readings

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.

Similar Readings

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

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.

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.

Sorting and Filtering in Excel.xlsx

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!

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

What is Sort and Filter in Excel?

How to Enable Sort And Filter in Excel

Types of Sort in Excel

1. Sort in Alphabetical Order

2. Sort by Smallest to Largest in Excel

3. Sort by Largest to Smallest

4. Multi-level Sort in Excel

5. SORT Function

Types of Filter in Excel

1. Regular Filter

2. Text Filters / Number Filters in Excel

3. Custom Filter

How to Undo Sort and Filter in Excel

1. Undo Sort

2. Undo Filter

💬 Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply