Difference Between Sort and Filter in Excel

This article will describe in detail about Sort and Filter tool of Excel. The introduction, methods, examples, uses, and advantages to sort and filter data in Excel will be covered in this article. So that after this article, you can easily differentiate between sort and filter tools in Excel. There is also a brief description of the difference between sort and filter in Excel at the end of this article.

Download Workbook

You can download the free practice Excel workbook from here. This workbook represents various methods of Sort and Filter in Excel.

1. Introduction to Sorting in Excel

In Excel, sorting is the process of arranging data in a particular order. It helps you analyze the data more effectively.

For instance, suppose you have a large dataset of the sales value of your company where all the data are in random order. To make the dataset of sales performance more user friendly, you want to organize the data by month, so that you can produce a graph chart of the sales performance. With the sorting tool, you can achieve that easily.

1.1. Methods of Sorting

There are two ways you can sort data.

• Sort text data into alphabetical order.
• Sort numeric data into numerical order.

On the other hand, there are some Custom Sort methods that also can be applied to the Excel dataset. Such as Sort on Cell Color, Font Color, Conditional Formatting Icon.

1.2. Sorting Examples in Excel

This section will show you some examples of how to sort data in Excel based on the methods discussed above.

1.2.1. Sort Text Data into Alphabetical Order

Consider the following dataset. In Column B of the dataset, we have some Student Name in random order.

We will see how to sort this text data alphabetically, both in ascending and descending order.

1.2.1.1. Sort Alphabetic Data into Ascending Order

To sort text data into ascending order, follow the steps below.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Sort A to Z from the Editing group in the ribbon. Sort A to Z means, your data will be sorted from lowest to the highest order.

Once you have done that, your text data will be sorted in ascending order.

As you can see from the picture above, after performing the sorting operation, our text data are now sorted into ascending order.

Read More: How To Sort Alphabetically In Excel And Keep Rows Together

1.2.1.2. Sort Alphabetic Data into Descending Order

To sort text data into descending order, follow the steps below.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Sort Z to A from the Editing group in the ribbon. Sort Z to A means, your data will be sorted from highest to lowest order.

Once you have done that, your text data will be sorted in descending order.

As you can see from the picture above, after performing the sorting operation, our text data are now sorted into descending order.

Read More: How to Sort Array with Excel VBA (Both Ascending and Descending Order)

1.2.2. Sort Numeric Data into Numerical Order

Consider the following dataset. In Column B of the dataset, we have some Numbers in random order.

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 into Smallest to Largest Order

To sort numeric data into smallest to largest order, follow the steps below.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Sort Smallest to Largest from the Editing group in the ribbon. Sort Smallest to Largest means, your data will be sorted from lowest to the highest order.

Once you have done that, your numeric data will be sorted into smallest to largest order.

As you can see from the picture above, after performing the sorting operation, our numbers are now sorted into the smallest to the largest order.

1.2.2.2. Sort Numeric Data into Largest to Smallest Order

To sort numeric data into largest to the smallest order, follow the steps below.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Sort Largest to Smallest from the Editing group in the ribbon. Sort Largest to Smallest means, your data will be sorted from highest to lowest order.

Once you have done that, your numeric data will be sorted into largest to smallest order.

As you can see from the picture above, after performing the sorting operation, our numbers are now sorted into the largest to smallest order.

Read More: How to Sort Numbers in Excel (8 Quick Ways)

1.2.3. How to Sort When Text and Numeric Data are Together?

Sometimes you have datasets where both text and numeric values are there, as shown in the image below.

If you want to sort the dataset based on the numeric value, then let’s see what is going to happen.

• As you already know from the previous section is that if you want to sort number values then you have to select the data -> Sort & Filter -> Sort Smallest to Largest/ Sort Largest to Smallest.

• When you do that, Excel will throw you a warning message in Sort Warning pop-up box. Excel does that because sometimes the data between columns are dependent on each other. So, if you sort one column and not the other, then the meaning of your dataset can be totally changed. For instance, in our dataset, we stored Student Names in Column B and their respective Exam Marks in Column C. So, if we sort only the Exam Marks column and not the Student Names column, then the whole dataset will be wrong. Students will be assigned with wrong Exam Marks.

To not let you go throw that problem, Excel notify you beforehand whether you want to expand the selection so that your whole dataset will be taken under consideration to sort or continue sorting only the current selection.

• If you want to sort your whole dataset based on only the numeric value, then check to Expand the selection option in the pop-up box.
• After that, click Sort.

Excel will sort the whole dataset based on the numeric values.

Like in the above image, we sorted the Exam Marks column in the smallest to largest order, and the order in the Student Name column is automatically modified according to it.

• On the other hand, if you want only to sort your selected region, then check to Continue with the current selection option in the pop-up box.
• After that, click Sort.

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

Look at the above image. After performing the sorting operation, only Column C, the Exam Marks column is sorted in the smallest to largest order.

1.2.4. Custom Sort: Sort Data on Cell Color

If you want to perform advanced sorting on your dataset, then Excel also has the option for you.

Suppose your dataset consists of background cell colour and you want to sort the dataset according to the cell colour.

Let’s see how to achieve that with Excel’s custom sorting feature.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Custom Sort… from the Editing group in the ribbon.

• Consequently, a pop-up Sort window will appear.
• From there, select Cell Color from the drop-down arrow in the Sort On section.

• After that, a new section will appear, Order. If you click on the drop-down arrow in that section, you will see that the colours your dataset has, are there in the Order You can organize the colour in any order that you want.

• You can also select whether you want the selected colour On Top of the list or On Bottom of the dataset. We choose our selected colour order Blue to stay On Top of the dataset.

• Before clicking OK, don’t forget about the checkbox beside My data has headers If you selected your dataset with the header, then check the box. But if you selected the dataset without the header, uncheck the box. If you keep the box checked, Excel will automatically ignore the first row from your selected dataset and start sorting from the second row.
• Lastly, click OK.

Now, look at the following image to see the result.

Finally, your dataset is sorted according to Cell Color.

Read More: [Fixed]: Sort by Cell Color Not Working in Excel (3 Solutions)

1.2.5. Custom Sort: Sort Data on Font Color

Just like you can sort data based on cell colour, you can sort data based on Font Color too. Look at the following image where the fonts of the cells are coloured with various colours.

In this section, we are going to show you how you can sort data based on Font Color. The procedures are quite similar to the procedure of sorting data based on cell colour.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Custom Sort… from the Editing group in the ribbon.

• From the pop-up Sort window, do the following:
  • Select Font Color from the drop-down arrow in the Sort On section.
  • Select the order of the Font Color according to which you want to organize the dataset from the drop-down arrow of the Order section.
  • If you want your selected colour to be on top of the dataset, then select On Top; Or if you want your selected colour to be on the bottom of the dataset, then select On Bottom from the drop-down arrow.
  • Make sure to check the checkbox beside My data has headers option if you selected your dataset with the header or uncheck it if you selected the dataset without the header.
  • Once you have done these all, then click OK.

Now, look at the following image to see the outcome.

Finally, your dataset is sorted according to Font Color.

Read More: How to Remove Sort by Color in Excel (With Easy Steps)

1.2.6. Custom Sort: Sort Data on Conditional Formatting Icon

If you want to know about how to work with Conditional Formatting Icons, then we have an excellent article on that from which you can know not only about the icons but also details of the Conditional Formatting in Excel.

In the following image, we have data consisting of conditional formatting icons. We will sort this dataset based on the icons in this section.

The steps to get that are given below. The steps are quite similar to the steps of sorting data based on cell colour and font colour that we have discussed above.

Steps:

• Firstly, select the data that you want to sort.
• Secondly, in the Home tab, select Sort & Filter -> Custom Sort… from the Editing group in the ribbon.

• From the pop-up Sort window, do the following:
  • Select Conditional Formatting Icon from the drop-down arrow in the Sort On section.
  • Select the order of the Icon from the drop-down arrow of the Order section according to which you want to organize the dataset.
  • If you want your selected icon to be on top of the dataset, then select On Top; Or if you want your selected icon to be on the bottom of the dataset, then select On Bottom from the drop-down arrow.
  • Make sure to check the checkbox beside My data has headers option if you selected your dataset with the header or uncheck it if you selected the dataset without the header.
  • Once you have done these all, then click OK.

Now, look at the following image to see the output.

Finally, your dataset is sorted according to the Conditional Formatting Icon.

Read More: How to Create Custom Sort in Excel (Both Creating and Using)

1.3. Uses of Sorting

Excel’s sorting tool is really efficient in either academic life or work life.

• When you have so much data in your Excel spreadsheet in a way that it makes it hard to understand the data, in that case, to organize those scattered data in spreadsheets, either in alphabetical or numerical order or based on colours or icons, you should use the Sorting tool of Excel.
• When your data are organized then it is easier to analyze them. Excel’s sorting feature helps you analyze data more precisely.

Read More: How to Use Advanced Sorting Options in Excel

1.4. Advantages of Sorting

There are various advantages that Excel’s sorting feature holds.

• It arranges data into a meaningful order so that data analysis becomes easier and more effective.
• It is capable of dealing with a huge amount of data This is one significant advantage of sorting.
• The results from sorting are thrown in place. As a result, no additional storage is required.

Read More: Advantages of Sorting Data in Excel (All Features Included)

Similar Readings

2. Introduction to Filtering in Excel

In Excel, filtering is the process of selecting only a part of the data and filtering out the rest based on certain conditions. The whole dataset is kept, only the part of it that we choose is displayed for viewing or analyzing data more easily.

For instance, suppose you have a large dataset of the sales value of your company. You want to see only the sales value from the month of January. To view that easily, you should use Excel’s filter tool. It will filter out all the months from your dataset except January so that you can calculate the sales of January more smoothly.

2.1. Methods to Filter in Excel

Microsoft Excel allows two types of filtering methods.

• AutoFilter
• Advanced Filter

2.2. Examples to Filter in Excel

This section will show you some examples of how to filter data in Excel based on the methods shown above.

2.2.1. AutoFilter in Excel

This section will show you how you can utilize Excel’s AutoFilter to filter your data.

2.2.1.1. AutoFilter Text Data

Consider the following example where Column B consists of so many Student Names. Suppose you want only some specific students to extract from there. Let’s see how to do that.

The steps to AutoFilter Student Names are shown below.

Steps:

• Firstly, select any cell from the dataset.
• Secondly, in the Home tab, select Sort & Filter -> Filter from the Editing group in the ribbon.

• As a result, you will see there is a drop-down arrow sign in the header cell of the dataset.

• After that, click on the drop-down arrow. A list of options will appear.
• Uncheck the names that you want to filter out from the dataset.
• Later, click OK.

Your dataset will be limited to the names that you provided in the list by selecting.

On the other hand, if you want something quicker, then you can consider the Text Filter from the option list.

• Click on the drop-down arrow from the header cell. A list of options will appear.
• From there, click Text Filter. You will see there are numerous amounts of filtering options that will turn up. You can pick any option that is suitable for your dataset.

• For the sake of this article, let’s consider that we want to extract only the names that start with B.
• So, from the list of options, we picked Begins With…

• A Custom AutoFilter box will come up. Right next to the begins with field, write the letter B.
• After that, click OK.

Once you have done all of that, now let’s check the output.

As you can see from the image above, names only start with the letter B are displayed in the dataset, while the rest of the names are hidden.

2.2.1.2. AutoFilter Numeric Data

The way you AutoFilter text data from your dataset, you can AutoFilter numeric data in the exact same way.

From the above dataset, we will learn how to AutoFilter with numeric values in Excel.

The steps to AutoFilter numeric values are shown below.

Steps:

• Firstly, select any cell from the dataset.
• Secondly, in the Home tab, select Sort & Filter -> Filter from the Editing group in the ribbon.

• As a result, you will see there is a drop-down arrow sign in the header cell of the dataset.

• After that, click on the drop-down arrow. A list of options will appear.
• Uncheck the numbers that you want to filter out from the dataset.
• Later, click OK.

Your dataset will be limited to the numbers that you provided in the list by selecting.

On the other hand, if you want something quicker, then you can consider the Number Filter from the option list.

• Click on the drop-down arrow from the header cell. A list of options will appear.
• From there, click Number Filter. You will see there are numerous amounts of filtering options that will turn up. You can pick any option that is suitable for your dataset.

• For the sake of this article, let’s consider that we want to extract only the numbers that are between 50 and 80.
• So, from the list of options, we picked Between…

• A Custom AutoFilter box will come up. Right next to the is greater than or equal to field, write 50. Or you can even pick the numbers from the drop-down arrow next to it.
• Later, right next to the is less than or equal to field, write 80. Or you can even pick the numbers from the drop-down arrow next to it.
• After that, click OK.

After you have done going through all the steps, let’s find out the result.

As you can see from the image above, only numbers between 50 to 80 are displayed in the dataset, while the rest of the numbers are hidden.

2.2.2. Advanced Filter in Excel

If you want to utilize Excel’s Advanced Filter, which is the quicker and more efficient feature of Excel, then follow this section.

• Before performing Advanced Filter, you have to store the condition on a separate range based on which you want to filter your data. In our case, we want to retrieve the numbers that are greater than or equal to 80, so we wrote the condition (>=80) in Cell D5.

• Then, go to the Data tab.
• Select Advanced from the Sort & Filter group in the ribbon.

• An Advanced Filter pop-up window will appear. If you want your current dataset to be filtered, then keep the checkbox beside the Filter the list, in-place option checked.
• The List range field will be automatically detected by Excel. If it doesn’t, then select the range manually.
• In the Criteria range field, drag the criteria range stored in the dataset. As we stored the criteria through range D4:D5, so we dragged through range D4:D5 to declare it as our criteria.
• After that, click OK. Your dataset will be sorted.

You can witness the whole procedure in the gif above.

• On the other hand, if you want your filtered data to be placed in a different location in your spreadsheet, then check the checkbox beside the Copy to another location The Copy to field will be enabled by doing this.
• Then, fill up the List range and Criteria range the way we showed you before.
• In the Copy to field, select the range where you want to export your filtered data. In our case, we selected Cell D7.
• Lastly, click OK. Your sorted dataset will be stored in a different location.

You can witness the whole procedure in the gif above.

2.3. Uses to Filter in Excel

There are various uses of filtering data in our daily activities either in academic life or work life.

• When you want to filter out any unwanted data from your dataset, then filtering is a very handy tool to execute the task.
• When you have only the part of the dataset that you want to view in front of you, then it is easier to analyze and perform any operation such as copy, print, format etc. on them. Excel’s filtering tool helps you analyze and perform any operation on the dataset more accurately.

2.4. Advantages to Filter in Excel

There are numerous advantages that Excel’s filtering feature carries.

• Filtering is a quick and efficient feature that gives you the option to view only the bits of data that meets certain condition provided by the user.
• After filtering you can spot the trends, analyze any costs, or even find any duplicate values if your dataset has.
• If you perform filtering after sorting data, then it even becomes a more powerful and better way to analyze the data. Only the rows or columns that meet certain criteria will be displayed while the rest will be hidden.
• With filtered data, you can copy, print, format etc. your data without modifying it first.

2.5. Clear Filter

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

This will clear all the filters that are there in your dataset.

Read More: [Fix:] Sort and Filter Not Working in Excel

Excel’s Sort and Filter Together

You can sort and filter your data together in Excel. You remember all the sorting examples (section 1.2) that we have shown you in the previous sections. In those sorted data, you can perform any types of filtering that we showed in the filtering examples (section 2.2).

Suppose you have a dataset, where numeric data are sorted into smallest to largest or largest to smallest order. You can perform AutoFilter to omit some numbers from there or Advanced Filter to retrieve some numbers that fulfil certain conditions (such as numbers between 50 to 80) in the dataset.

Sort and filter together in Excel make your dataset more specific which helps you to analyze and calculate the data more efficiently.

Summary of the Article/ Difference Between Sort and Filter in Excel

After describing the Sort and Filter tool of Excel, now we can differentiate them quite easily.

Subject

Sort

Filter

Purpose

The process of arranging data in a particular order. The process of selecting only a part of the data and filtering out the rest based on certain conditions.

Methods

Sort text values and numeric values in ascending or descending order. Sort based on Cell Color, Font Color, and Conditional Formatting Icons. Filter data using AutoFilter and Advanced Filter tool of Excel.

Use

• To organize data into a meaningful order.
• Analyzing data more accurately.

• To limit the dataset by filtering out unwanted data and displaying only the specified data.
• To analyze the data and perform any operation more precisely on them.

Advantages

• Easy to arrange data into a specific order.
• Easier to deal with a huge amount of data.
• No additional storage is required.

• Easy to view only the data that users want.
• Easier to spot any trend or find duplicates.
• Easy to copy, print or format data.

Examples

• To sort text data from highest to lowest or lowest to the highest order.
• Sort numeric data from smallest to largest or largest to smallest order.

To filter out certain numbers or text from a large dataset based on the condition given.

Conclusion

To conclude, this article described Sort and Filter tools and the difference between them in Excel. Sort and filter tools in Excel has so many uses in our daily life. The sort and filter feature of Excel also helps us with its numerous advantages in both our academic life and work life. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles