How to use Advance Filter in Excel

Download PC Repair Tool to quickly find & set up Windows errors automatically

We know that we can set filters on columns and filter the data with just a click in Microsoft Excel. Information technology can exist done past clicking on the 'Filter' under the "Data" tab. Apart from just filtering the data based on columns, we can even filter the data gear up completely based on some conditions. Suppose we want to filter the data prepare matching the specific criteria, so information technology tin exist done using Advance Filter. In this article, I will allow y'all know how to use Accelerate Filter in Excel.

How to utilise Advance Filter in Excel

Let me explain this with the sample data of Employees. I have the data fix containing Usual Hours Worked, Education in Years, Yearly Earnings, Sex and State. At present, I want to filter the data meeting the condition as follows,

Condition for Male:

Usual Hours Worked: xl
Education: 13
Yearly Earnings: Greater than 35000
Gender: Male
State: Alaska

Condition for Female:

Usual Hours Worked: Greater than 35
Didactics: Greater than 12
Yearly Earnings: Greater than 12000
Gender: Female
Country: Alaska

If nosotros see the weather condition, we need to filter the data of Male and Female employees separately. I hateful, there was an OR status, and within that, there was AND condition to be met.

To filter out the data meeting these conditions, we demand to deal with the Excel canvass differently. This is where advanced filter in Excel comes into the picture. At present, we volition prepare the criteria in the same Excel canvass itself. Create two rows above the original information prepare with the same column names as the already present data as shown below from A3 to E5.

Now, we volition fill the criteria columns with the data as mentioned in the conditions. As mentioned in Condition for Male, fill the unabridged row with the specified columns. So, Row 4 will be filled as shown below.

Echo the same for the side by side Row 5 based on Status for Female as shown below. Now, to clarify, if data in each row are linked with OR condition and data inside that row (column-wise information) are linked with AND condition. So, we merely created the rows coming together the criteria with which we demand to filter the information.

Now, it is time to use the advanced filter in Excel to filter the data. First, click any cell on your original dataset, click the "Information" tab, and click the "Advanced" button. It will automatically fill the List Range. Click on the small push button beside the Criteria range. At present, select the criteria range, i.east., A3 to E5, and click on the aforementioned modest button to set the standards range. Now, click "Ok." It will filter the data as you want to meet the criteria.

Note: Column Names of the criteria range should be precisely the same as the column names of the data set to go this worked.

Advance Filter in Excel allows u.s.a. to filter the data, meeting the complex queries. And so, what query or condition have you lot used to filter the data? Please, let us know through comments if you have annihilation to add.

Read: How to change the default Numbered List in Word.

How practice I utilize advanced filter unique records but in Excel?

Under the Data tab, select the Avant-garde Filter, and so ensure the filter is set to Unique records only. It will ensure to discard any duplicate record. It tin be besides used when copying data to some other cell or applying it at the exact location.

Which activity must you have before using the filter feature?

Ensure in that location is no blank row betwixt the set up of data. Else it will skip the rest of the rows. When setting up the filter, the offset row is taken header, and the residual of the rows under it are data. Any empty data will break the filter. Ensure to have at to the lowest degree goose egg in case of numbers.

Sridhar is a Software Engineer who loves to stay updated with developments in the world of technology. He is fond of writing everything related to Net, Computers and Mobile and Desktop Operating Systems.