How to filter in Excel

What is called Filtering the data?

Filtering the data only displays the specified data based on certain criteria or conditions. Excel provides various filter options to filter the data based on the requirement.

Why filter data in Excel?

Filtering data is done in Excel for several reasons. Some of the reason is explained as follows,

• To view the specific data: If a large data set is present in the worksheet, filtering is essential to view the specified data set. It filters the data based on the criteria, which helps analyze it easily.
• Identify the data pattern: Filtering sorts the data in ascending or descending Order. Sorting the data helps to find the pattern or trend of the data.
• Time-saving: Finding a particular data in a large data set is difficult and time-consuming. By filtering the data, the specified data is easily found.
• Repetition is found: If data repetition or duplicate values occurs, it is founded using the filtering method.

Methods to filter data in Excel

To filter the data, Excel provides various methods. The methods are as follows,

1. Filter Function
2. AutoFilter feature
3. Advanced Filter Feature
4. Sort Functions

AutoFilter

The Auto Filter function present in Excel is used to filter the data based on the specified conditions.

How to use Auto Filter Function?

To use the Auto filter function, the steps to be followed are:

1. Enter the necessary data in the worksheet, namely A1:A11

2. Select the data range from A1:A11 and choose" Data" in the Ribbon tab.

3. In the Sort and Filter group, choose the "Filter."

4. After clicking the Filter option, there displays a drop-down option in the selected data.

5. Click on the drop-down list, there displays several options. In that, choose the required criteria based on the requirement.

There display several options, such as,

Sort A to Z - It alphabetically sorts the data from A to Z.

Sort Z to A- It alphabetically sorts the data, starting from Z to A.

Sort by Color- While clicking the Sort by color option, there displays an option called Custom Sort. Click the Custom Sort option. The Custom Sort displays several options, such as,

The options present in Sort On values are,

• Values
• Cell Color
• Font Color
• Cell Icon

The options present in Order are,

• A to Z,
• Z to A,
• Custom List.

The option displayed by the Text filters is,

1. Equals
2. Does Not Equals
3. Begins With
4. Ends With
5. Contains
6. Does Not Contain
7. Custom Filter

Equals

While clicking the equal option, there display several options called,

• Equals
• Does Not Equals
• Is greater than
• Is greater than or equal to
• Is less than

Does Not Equals

While clicking the Does Not Equal option, there display several options called,

• Equals
• Does Not Equals
• Is greater than
• Is greater than or equal to
• Is less than

Begins With

While clicking Begins With the option, there display several options called,

• Does Not Equals
• Is greater than
• Is greater than or equal to
• Is less than
• Is less than or equal to
• Begins With

Ends With

While clicking ends With the option, there display several options called,

• Is greater than or equal to
• Is less than
• Is less than or equal to
• Begins With
• Does Not begin WITH
• Ends With

Contains

While clicking Contains option, there display several options called,

• Is less than or equal to
• Begins With
• Does Not begin WITH
• Ends With
• It does Not End With
• Contains

Does Not Contains

While clicking the Does Not Contain option, there display several options called,

• Begins With
• Does Not begin WITH
• Ends With
• It does Not End With
• Contains
• Does Not Contains

Custom Auto Filter

While Clicking the Custom Auto Filter option, there display several options called,

• Equals
• Does Not Equal
• Is greater than
• Is greater than or equal to
• Is less than

The data is filtered based on the requirement by choosing any options.

Filter option

Another method to filter the data is as follows,

1. Enter the data in the worksheet ranges from A1:A11
   
2. Choose the Sort & Filter option in the Home tab.
   
3. By choosing the Sort and Filter option, there display several options as follows,
   
4. Clicking the Filter option displays the drop-down arrow in the first cell.
   
5. By clicking the drop-down arrow in the list, there display several options as follows,
   

Click on the necessary options based on the requirement of the data. The data will get filtered based on the data.

How to use Advance Filter Option?

The advanced Filter option is used to filter the set of data based on multiple criteria. It is one of the user-friendly options used to filter the data in the worksheet based on multiple criteria, which are combined using several logical operators. The Advance Filter option is used in a large set of data, which is used to find the duplicates and helps to solve the complex criteria based on the conditions.

The steps to use Advance Filter Option is as follows,

1. Enter the data in the worksheet, namely A1:C10
   
2. Select the data range from A1:C10. Click the option "Data." In that, choose Advanced in the Sort and Filter group.
   
3. The Advanced Filter options dialog box will display. In that, choose the required option,
   1. Filter the list,in-place
   2. Copy to another location

If the original data needs for future reference, the new data will be copied to another location. Hence choose the option called "Copy to another location." Or else choose the option "Filter the list, in place."

In the Advanced Filter Dialog box, enter the listed range criteria range. Click the unique record option to avoid duplicate values.

Sort Functions

As the name suggests, the Sort function in Excel is used to sort the data based on alphabetical order A to Z or Z to A. This option displays several other options to sort the data based on the requirement or criteria. Sorting helps the user to analyze, organize and refer to the data easily and quickly. Sorting may vary based on the criteria. The steps to be followed to use Sort Function are as follows,

1. Click the selected data which needs to be filtered
   
2. Click the Data option in the Home tab. In that, choose the Sort option in the Sort and Filter option.
   
3. While clicking the Sort options, the Sort dialog box will appear. In that, click the necessary option to sort the data. Click ok.
   
   Here the column header Fruits are sorted based on alphabetical order A to Z, and Price is sorted from Smallest to Largest. Click ok
   The required data will be sorted based on the criteria as follows,
   
   The sorted data are shown in the image.

How to clear the Filter?

Once filtering is done in the data, clearing the Filter option is done to get back to the original data. To clear the Filter in the specified data, the steps to be followed are,

1. To clear the Filter, click on the drop-down list of the necessary column. Here Column C is chosen.
   
2. Here the data present in Column C is sorted with the value from A to Z. To remove the Filter, click on the column's drop-down list, where several options are displayed. In that, choose the option called "Clear Filter."
   
3. Another option to clear the Filter is to, after selecting the data, click the Filter option in the Home tab. It will clear the Filter for the selected data.
   
4. Now the Filter is removed for the selected data.
   

The drop-down option is removed from the Header option. Hence, the Filter is removed for the specified data by choosing any of the specified methods.

Summary

Filtering is one of the techniques used in Excel, which is used to find duplicate values. It helps organize the data and clarify it for identifying patterns and solutions.