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.
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:
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 ).
Step 2: Paste ( Ctrl + V ) into any cell in Excel.
Step 3: Enter filtering conditions.
- OR conditions are arranged vertically. For example:
Number of workdays <= 26 or number of workdays> = 29.
- 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.
Number of workdays> 2 = 28 and Gender is Male.
- 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 .
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 .
Step 3: In the List range section , click the icon at the end of the line as shown below:
Then you click to select the main data table to be filtered by clicking, holding and dragging to the last cell in the table.
Release the mouse button and click the icon as shown below to return to the Advanced Filter dialog box .
Step 4: In the Criteria range section , you also do the same to select the filter condition table.
Step 5: Because of selecting Copy to another location , you need to select the box in the Copy to section .
Then you click OK .
The final result after filtering:
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
- 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
May be interested
- How to filter duplicate data from 2 Sheets in Excelhow to filter duplicate data from 2 sheets in excel. in excel file, you have data in 2 different sheets but you want to filter duplicate data with a large number of records. here's how to use the vlookup function to filter duplicate data from 2 sheets in exc
- 4 Advanced PivotTable Functions for Excel Data Analysisdata analysis is not easy. so, to enhance your data, you can use a range of advanced pivottable functions in excel.
- How to use Filter function on Google Sheetsfilter functionality on google sheets will help users find the data they need in the data sheet, based on the conditions we use.
- How to fix filter errors in Excel spreadsheetsguide you how to best fix filter errors in excel, this is the error that users often encounter when using excel spreadsheets
- How to filter duplicate data on 2 Excel sheetsto filter duplicate data from 2 sheets in excel, you can use the vlookup function.
- Malicious software uses Gmail to receive commands and filter user dataan advanced version of the newly discovered backdoor comrat has the ability to use the gmail web interface to receive commands from hackers and filter sensitive data.
- Use the advanced search feature and create a filter in Gmailas we all know, gmail is one of google's online email services - the search giant. however, most users don't know or exploit advanced search features right inside gmail. in the following article, we will introduce you to these 'mysterious' features. besides, we can create filter system - filter based on user's search requirements.
- How to remove duplicates in excelto delete the same lines and figures in excel data sheet, we can use available features like remove duplicate or manually delete through the hightlight section.
- How to Insert Headlines in Excelthere are many ways to create titles in excel depending on different purposes. you can freeze a row so that it stays on the screen even if the reader scrolls down. if you want the title to appear on multiple pages, you can set up rows and columns to print on each page. if the data is organized into a table, you can use headers to filter the data.
- How to fix the error of Filter not filtering all the data?are you looking for a way to fix the error of filter not filtering all data? this tipsmake article will fully provide effective ways to fix errors