Hi, friends today we are talking on How to use Advanced Filter. Advanced filter is an important utility of Microsoft Excel. If you need to apply complex criteria on any worksheet than you have to use Advanced filter in Microsoft Excel. This option displays all those records which meet the complex criteria on any worksheet. In this guide we have to discuss how to use advanced filter in Excel.
There are lot of special features in Microsoft Excel. Advanced filter is one the most important feature on Microsoft Excel. If you have a large worksheet data and want to apply any complex criteria then you have to use Advanced filter option. Advanced filter option is used to check the criteria in the given list of range and displays all the records which match the condition.
By default Excel allows you to filter the list as per the given condition at the same place. You can also copy the filter result at another place with the help of “Copy to another location” option. If you have more than one same records after using the filter option then you can also able to view unique records only with the help of Advanced filter option.
Must Read: How to use Goal Seek feature in Microsoft Excel
For eg: If you have lot of product database in the worksheet and you want to view all those records which product name is equal to “Finegrip”, Rate is “>12” and Amount is “>=16000”. Now you want to view all those records which meet these given complex condition.
How to use Advanced Filter in Excel
Advanced Filter in Excel can easily filter a list in which have complex criteria. You can easily view the final result either on the same place or you can also copy the result at another location. During the advanced filter Microsoft Excel uses the separate criteria range of cells or an array. You have to take given steps to use advanced filter in Excel.
Step 1: Create a new blank worksheet to start the advanced filter process. Select the range of cells in which you want to apply advanced filter command in Microsoft Excel. You have to select range from A1:D16.
Step 2: You must have to turn on advanced filter option on selected range of cells by click in on the “Advanced” option from the “Data” tab. Press Alt+A+Q to directly open the “Advanced Filter” dialogue box.
Must Read: VLookup multiple criteria using Match function in MS Excel
Select 3: Select “Filter the list, in-place” option in the Action category. Set the range $A$1:$D$16 in the “List range“. You can also set the range $G$1:$I$2 in the “Criteria range“. Finally click on the OK button to get the final result.
I hope after reading this guide you can easily advanced filter in Microsoft Excel. If you have more than one complex conditions then you can use “Advanced filter” option at the place of filter command in MS Excel. If you have any suggestion regarding this article please write us in the comment box. Thanks to all.