Difference Between Sort and Filter in Excel

1. Introduction to Sorting in Excel

Sorting is the process of arranging data in a particular order.

1.1. Sorting Methods

You can:

Sort text data in alphabetical order.
Sort numeric data in numerical order.

There are Custom Sort methods, such as Sort by Cell Color, Font Color, Conditional Formatting Icon.

1.2. Sorting Examples in Excel

1.2.1. Sort Text Data into Alphabetical Order

Consider the following dataset. In Column B, there are Students’ Names in random order.

1.2.1.1. Sort Alphabetic Data in Ascending Order

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Sort A to Z in Editing.

Text data will be sorted in ascending order.

1.2.1.2. Sort Alphabetic Data in Descending Order

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Sort Z to A in Editing.

Text data will be sorted in descending order.

1.2.2. Sort Numeric Data in Numerical Order

Consider the following dataset. In Column B, there are random Numbers.

We will see how to sort these numbers numerically, both in smallest to largest and largest to smallest order.

1.2.2.1. Sort Numeric Data from Smallest to Largest

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Sort Smallest to Largest in Editing.

Numeric data will be sorted from smallest to largest.

1.2.2.2. Sort Numeric Data from Largest to Smallest

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Sort Largest to Smallest in Editing.

Numeric data will be sorted from largest to smallest.

1.2.3. How to Sort Text and Numeric Data

The dataset contains both text and numeric values.

Sort the dataset based on the numeric values:

Select the data -> Sort & Filter -> Sort Smallest to Largest/ Sort Largest to Smallest.

A Sort Warning message is displayed: the dataset contains Students’ Names in Column B and their Exam Marks in Column C. If you sort the Exam Marks column only and not the Students’ Names column, Students will be assigned with wrong Exam Marks.

To sort the whole dataset based on numeric values only, check Expand the selection.
Click Sort.

Excel will sort the whole dataset based on numeric values.

Exam Marks are sorted from smallest to largest and the order in the Students Name column is automatically modified.

To sort the selected region only, check Continue with the current selection.
Click Sort.

Excel will sort the selected column only based on numeric values.

In the image above, only Column C, the Exam Marks column, is sorted.

1.2.4. Custom Sort: Sort Data by Cell Color

The dataset has background color. To sort the dataset by cell color:

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Custom Sort… in Editing.

In the Sort window, select Cell Color in Sort On.

Click Order to see the colors in your dataset.

Here, Blue was selected to stay On Top.

Check My data has headers.
Click OK.

This is the output.

1.2.5. Custom Sort: Sort Data by Font Color

Sort data based on the Font Color.

In the following dataset, there are colored.

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Custom Sort… in Editing.

In the Sort window:
- Select Font Color in Sort On.
- In Order, select On Top.
- Check My data has headers.
- Click OK.

This is the output.

Finally, your dataset is sorted according to Font Color.

1.2.6. Custom Sort: Sort Data using Conditional Formatting

Sort this dataset based on the icons.

Steps:

Select the data to sort.
In the Home tab, select Sort & Filter -> Custom Sort… in Editing.

In the Sort window:
- Select Conditional Formatting in Sort On.
- Select the order of the Icon in Order.
- Select On Top.
- Check My data has headers.
- Click OK.

This is the output.

2. Introduction to Filtering in Excel

Filtering is the process of selecting only a part of the data based on conditions. The whole dataset is kept, but only the selected part is displayed.

2.1. Filtering Methods in Excel

Microsoft Excel offers two filtering methods:

AutoFilter
Advanced Filter

2.2. How to Filter data in Excel

2.2.1. AutoFilter in Excel

Use the AutoFilter.

2.2.1.1. AutoFilter Text Data

Consider the following dataset. To display specific students’ names:

Steps:

Select any cell in the dataset.
In the Home tab, select Sort & Filter -> Filter in Editing.

A drop-down arrow is displayed in the header cell.

Click the drop-down arrow to see the list of options.
Uncheck the names you want to filter.
Click OK.

This is the output.

You can also use the Text Filter option:

Click the drop-down arrow in the header to see the list of options.
Click Text Filter. Choose a filtering option.

To extract names that start with B.

Choose Begins With…

In the Custom AutoFilter box, enter B in begins with.
Click OK.

This is the output.

2.2.1.2. AutoFilter Numeric Data

AutoFilter numeric data:

Steps:

Select any cell in the dataset.
In the Home tab, select Sort & Filter -> Filter in Editing.

A drop-down arrow is displayed in the header cell.

Click the drop-down arrow to see the list of options.
Uncheck the numbers you want to filter.
Click OK.

This is the output.

You can also choose Number Filter:

Click the drop-down arrow in the header to see the list of options.
Click Number Filter. Choose a filtering option.

To extract numbers between 50 and 80.
Choose Between…

In the Custom AutoFilter box, enter 50 in is greater than or equal to the field, write 50. You can also select the numbers from the drop-down arrow.
Enter 80 in is less than or equal to.You can also select the numbers from the drop-down arrow.
Click OK.

This is the output.

2.2.2. Using an Advanced Filter in Excel

Before using an Advanced Filter, store the condition on a separate range based on which you want to filter data.

To extract the numbers greater than or equal to 80, enter (>=80) in D5.

Go to the Data tab.
Select Advanced in Sort & Filter.

In Advanced Filter, select Filter the list, in-place.
The List range field will be automatically detected by Excel. If it isn’t, select it manually.
In Criteria range, drag the criteria range stored in the dataset. Here, D4:D5.
Click OK.

To place the filtered data in a different location, check Copy to another location.
Enter List range and Criteria range.
In Copy to, select a range to export the filtered data. Here, D7.
Click OK.

2.3. Clearing the Filter

To clear any filter from the dataset, select Sort & Filter -> Clear in Editing in the Home tab.

Using Sort and Filter

You can sort and filter your data in Excel.

In a dataset with numeric data sorted from smallest to largest or largest to smallest, you can use the AutoFilter to omit numbers or the Advanced Filter to display numbers that meet specified conditions.