Buzz
When working with data tables in Excel, filtering data is a crucial task. There are various methods and functions to filter data in tables.
The Advanced Filter function is one of the most effective tools for data filtering, allowing versatile filtering conditions for quick and efficient data refinement.
This article provides a comprehensive guide on utilizing the Advanced Filter function to filter data in Excel.
Requirements for using Advanced Filter
To utilize the Advanced Filter function for data tables that require filtering, ensure the following prerequisites:
- The data table header must occupy only a single row.
- No cell merging should exist within the data table to be filtered.
- Leave at least 3 visible rows at the top of the data table.
For example, consider the following data table:
To use the Advanced Filter function, you need a filter criteria table.
Create a filter criteria table.
Creating a filter criteria table is as follows:
Step 1: Select the column header to be used as the filter condition, then choose Home -> Copy (or Ctrl + C).
Step 2: Paste (Ctrl + V) into any cell in Excel.
Step 3: Enter the filter condition.
- OR conditions are arranged vertically. For example:
Days worked <= 26 or days worked >= 29.
- AND conditions are arranged horizontally, so if filtering 2 AND conditions based on the same criterion, a single header must be used for 2 cells. For example, the AND conditions are:
Days worked >= 26 and <= 29.
Days worked >28 and Gender is Male.
- With only one condition, simply input it under the header cell of the filter criteria table.
- Additionally, you can combine AND and OR conditions together.
After creating the filter criteria table, you can start using the Advanced Filter function to filter data.
Utilize the Advanced Filter function
Step 1: Select Data -> Advanced.
Step 2: The Advanced Filter dialog appears, with 2 options in the Action section:
- Filter the list, in-place: Filter and return results within the same filtered data table.
- Copy to another location: Filter and return filtered results to a different location of your choice.
For example, select Copy to another location.
Step 3: In the List range section, click on the icon at the end of the row as shown below:
Then, select the main data table to be filtered by clicking, holding, and dragging to the last cell in the table.
Release the mouse and click on the icon as shown below to return to the Advanced Filter dialog box.
Step 4: In the Criteria range section, perform the same action to navigate to the filter criteria table.
Step 5: Since selecting Copy to another location, you need to choose a cell in the Copy to section.
Then press OK.
The ultimate outcome after filtration is:
The Advanced Filter feature can combine various conditions, making data processing in your Excel spreadsheet much easier. Wishing you all success!
