Filter data in Excel with Advanced Filter
In the process of working with data tables in Excel, the filtering of data in the data tables is very necessary and often done. There are many ways to filter and functions to help you filter data in data tables.
Advanced Filter function is one of the functions that help you filter data, Advanced Filter does not limit the filtering conditions, so it will help you filter data faster and more efficiently.
Filter data in Excel with Advanced Filter Picture 1
The following article will guide how to use the Advanced Filter function to filter data in Excel.
Required when using the Advanced Filter
To use the Advanced Filter function, the data table to filter needs to meet the following requirements:
- Data sheet header must use only one line.
- Do not merge any cells in the data table to filter.
- Leave at least 3 lines in the same table.
For example, we have the following data table:
Filter data in Excel with Advanced Filter Picture 2
To use the Advanced Filter function, you need a filter condition table.
Create filter conditions table
How to create filter conditions as follows:
Step 1: Select the column header to be filtered, then select Home -> Copy (or Ctrl + C ).
Filter data in Excel with Advanced Filter Picture 3
Step 2: Paste ( Ctrl + V ) into any cell in Excel.
Filter data in Excel with Advanced Filter Picture 4
Step 3: Enter filtering conditions.
- OR conditions are arranged vertically. For example:
Number of workdays <= 26 or number of workdays> = 29.
Filter data in Excel with Advanced Filter Picture 5
- AND conditions are arranged in horizontal rows, so if you need to filter 2 AND conditions under the same criteria, you must use a title for 2 cells. Examples of AND conditions:
Number of workdays> = 26 and <= 29.
Filter data in Excel with Advanced Filter Picture 6
Number of workdays> 2 = 28 and Gender is Male.
Filter data in Excel with Advanced Filter Picture 7
- With only one condition, you only need to enter it under the title box of the filter table.
- Also you can combine AND and OR conditions together.
After creating filter conditions, you start using the Advanced Filter function to filter data.
Use the Advanced Filter function
Step 1: Select Data -> Advanced .
Filter data in Excel with Advanced Filter Picture 8
Step 2: The Advanced Filter dialog box appears , in the Action section there are 2 options:
- Filter the list, in-place: filter and return results in the filter data table itself.
- Copy to another location: filter and return filter results in other locations, you choose.
For example, select Copy to another location .
Filter data in Excel with Advanced Filter Picture 9
Step 3: In the List range section , click the icon at the end of the line as shown below:
Filter data in Excel with Advanced Filter Picture 10
Then you click to select the main data table to be filtered by clicking, holding and dragging to the last cell in the table.
Filter data in Excel with Advanced Filter Picture 11
Release the mouse button and click the icon as shown below to return to the Advanced Filter dialog box .
Filter data in Excel with Advanced Filter Picture 12
Step 4: In the Criteria range section , you also do the same to select the filter condition table.
Filter data in Excel with Advanced Filter Picture 13
Step 5: Because of selecting Copy to another location , you need to select the box in the Copy to section .
Filter data in Excel with Advanced Filter Picture 14
Then you click OK .
Filter data in Excel with Advanced Filter Picture 15
The final result after filtering:
Filter data in Excel with Advanced Filter Picture 16
Advanced Filter function can combine many different conditions, the processing of data in your Excel spreadsheet will be easier. Good luck!
You should read it
- Advanced data filtering in Excel
- How to use Advanced Filter to filter data on Excel
- Guide to Automatic Filter and Filter detailed data in excel
- MS Excel 2007 - Lesson 8: Sort and Filter
- Data filtering (Data Filter) in Excel
- Filter data that doesn't overlap in Excel - Filter for unique values in Excel
- How to filter data in Excel
- Filter PivotTable report data in Excel
- How to filter duplicate data from 2 Sheets in Excel
- Filter duplicate data, delete duplicate data in Excel
- How to use Filter function on Google Sheets
- Photoshop CS: Eclipse effect