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,
Methods to filter data in Excel
To filter the data, Excel provides various methods. The methods are as follows,
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,
The options present in Order are,
The option displayed by the Text filters is,
While clicking the equal option, there display several options called,
Does Not Equals
While clicking the Does Not Equal option, there display several options called,
While clicking Begins With the option, there display several options called,
While clicking ends With the option, there display several options called,
While clicking Contains option, there display several options called,
Does Not Contains
While clicking the Does Not Contain option, there display several options called,
Custom Auto Filter
While Clicking the Custom Auto Filter option, there display several options called,
The data is filtered based on the requirement by choosing any options.
Another method to filter the data is 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,
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.
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,
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,
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.
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.