Excel Advance Filter and Auto Filter with Example
Excel Advance Filter and Auto Filter is an important tool to fetch out data form worksheets. Microsoft Excel provides us two types of filters one is auto filter (basic filter) and other is advance filter. In auto filter we have option to apply filter based on color, based on text and based on numbers. Where as in advance filter we have to design some criteria or condition to fetch data. So we are discussing what is filter, how to use filter in excel.
In practical scenario we have many datasheet where we want data filter based on some condition. Here Excel’s filter option comes in the picture. Let us discuss it.
What is Filter in Excel?
As you know filter is an operation to fetch data based on some condition. The excel auto filter option known as basic filter allows us to see particular rows from worksheet based on applied condition.
How to apply auto filter in Excel?
To apply basic filter or auto filter, first of all select all data range in spreadsheet and find an option in Home tab as well as data tab.
filter button on Home Tab
filter button on Data Tab
Auto filter commend apply arrow heads to each selected row in worksheet. When you click on arrow head of a particular rows you can see a drop-down list to apply condition to filter data based on color, text or numbers.
After selecting condition, excel fetch out or display only those rows which meet up our condition or criteria and all other rows get hided. Alter it we can reapply filter condition or clear our filter using buttons on sort and filter section.
Advance Filter in Excel-
In advance filter, just its name suggests it is advance version of auto filter. Using advance filter we can apply multiple criteria on selected data range in worksheet. We can also fetch out filtered data from one worksheet to another worksheet. In this function excel do not show any arrow head on row headers.
How to use Advance filter in Excel-
Go to data tab->click on Advance filter option. Filter dialog box displays.
Filter the list, In Place-
If we want to use advance filter result in same place or in same worksheet then we select the default option filter the list, in place.
Example- Suppose we want to filter only purchase department employee’s record.
In the dialog box there is two text boxes-
- List Range- put the cursor here and selects all the data form worksheet which we want to filter.
- Criteria Range- Select the specific condition to filter the selected data range and press ok.
Copy to another location-
If we want to use advance filter result in other worksheet then we select the second option copy to another location from dialog box.
Example- This time suppose we want to filter and show those employee’s record which have department purchase and basic salary having grater then 10,000.
When we select second option form dialog box there is three text boxes shown –
- List Range- put the cursor here and selects all the data form worksheet which we want to filter.
- Criteria Range- Select the specific condition to filter the selected data range.
- Copy to- Select the location form another worksheet to display the filtered data then press OK.
If you have any problem or confusion and want to more examples on advance filter then watches our below YouTube video to clear the concept.
you may also interested to read out our another excel based tutorial -
