How to Sort and Filter Data in Excel – A Complete Guideline

What is Sort and Filter in Excel?

Sort and filter options in Excel narrow down a dataset allowing a deeper analysis.

Using the filter tool, you can filter a column based on given criteria. The sorting tool allows you to sort data based on alphabetical order, numbers, dates, etc.

This is a sample dataset:

After sorting data based on Profit, in descending order:

Filter the dataset based on “South”:

How to Enable Sort And Filter in Excel

• Click any cell in the dataset.
• Go to the Data tab.
• In Sort & Filter, click Filter.

You will see the dropdown beside every column header.

Types of Sort in Excel

1. Sort in Alphabetical Order

Sort data in alphabetical order. Choose A to Z or Z to A.

Steps

• Click the dropdown menu beside “Name”.

• Click Sort A to Z to sort the Name column in ascending order.

• If you click Sort Z to A, data will be sorted in descending order.

Read More: How to Perform Random Sort in Excel

2. Sort by Smallest to Largest in Excel

If you have numerical data, sort it from smallest to largest.

Steps

• Click the dropdown menu beside the Total Sales column.

• Click Sort Smallest to Largest.

3. Sort by Largest to Smallest

Sort data in the Profit column.

Steps

• Click the dropdown menu beside the Profit column.

• Click Sort Largest to Smallest.

Read More: How to Sort Excel Tabs

4. Multi-level Sort in Excel

You can also call it “Custom Sort” Multi-level sorting means sorting based on multiple columns.

Steps

• Click the dropdown menu beside “Region”.

• Select Sort by Color.
• Click Custom Sort.

• Select Region in Sort by.
• Select Cell values and A to Z.

• Click Add Level.

• Select Total Sales in Then by.
• Select Cell values and Largest to Smallest.
• Click OK.

5. SORT Function

Use the SORT function.

To sort data based on the Product column (Descending order):

Steps

• Enter the following formula in H5:

• Press Enter.

Read More: Advantages of Sorting Data in Excel

Types of Filter in Excel

1. Regular Filter

A regular filter means you can filter data based on any values.

Filter the dataset based on “TV”:

Steps

• Click the dropdown menu beside Product.

• Uncheck Select All.

• Check“TV” and click OK.

2. Text Filters / Number Filters in Excel

Filter text with Text Filters. Excel automatically shows Number Filters in columns containing numeric values.

Filter the dataset based on the Total Sales less than $3500.

Steps

• Click the dropdown menu beside Total Sales.

• Select Number Filters and click Less Than.

• Enter $3500.
• Click OK.

Read More: How to Perform Custom Sort in Excel

3. Custom Filter

You can use the Custom Filter with the AND or the OR logic.

To filter data based on the Total Sales greater than $2000 but less than $3000:

Steps

• Click the dropdown menu beside Total Sales.

• Select Number Filters and click Custom Filter.

• Enter the values.
• Select And and click OK.

Read More: Advanced Sorting in Excel

How to Undo Sort and Filter in Excel

1. Undo Sort:

• Click Undo or press Ctrl+Z.

Or:

• Create a temporary column.

2. Undo Filter

• Click the dropdown menu to which you applied filtering.

• Click Clear Filter From “Region” (column name can be different based on your filtering).

To remove the dropdown menu:

• Click any cell in your dataset.
• Go to the Data tab.

• In Sort & Filter, click Clear.

Read More: Difference Between Sort and Filter in Excel

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Sort Drop Down in Excel
• How to Sort IP Address in Excel

<< Go Back to Sort in Excel | Learn Excel