Master how to use excel's advanced filtering function

How to use the advanced filtering function in excel

First, enter the data that needs to be filtered in the Excel table for use, as shown in Figure 1. This step is to ensure we have data to sift through.

2. In the Excel table, first select the table content that needs to be filtered. Next, click the "Data" option bar at the top (if it is the 2010 version of the Excel table). In the Sort and Filter area, select Advanced options.

3. Let’s first take a look at the main content to be set in the “Advanced Filtering” dialog box, as shown in Figure 3. There are two options in the method, which means where your filter results appear. There is also a "list area", "condition area", and "copy to" below, which mean respectively: the area where you filter; what are the conditions for filtering; and where to put the filter results.

4. Based on the content in the table, assuming we want to filter information with midterm scores greater than 80 points, we now enter the conditions into any table.

5. Then select the condition just entered in the condition area, as shown in the figure. Then click the location shown in the picture to return to the advanced filtering dialog box.

6. The last step is to set the selection of the "Copy to" area, where we want to display the filter results. Let's click on the position shown in Figure 7, then click on a certain position in the blank space below the table, and finally click as follows The position shown in Figure 9 returns to advanced filtering.

7. Finally click OK, and then the filter results will be displayed.

How to use advanced filtering in excel

Advanced filtering is very simple. Just enter the conditions and then you can perform advanced filtering. Here is a simple example, I hope it will be helpful to you.

1. Data area:

Department No. Name Title Company Profile Rules and Regulations Legal Knowledge

Development Department 101 Li Hong None 85 80 83

Development Department 102 Wu Shiqing Secretary 69 75 84

Development Department 103 Zhang Mingxiu Supervisor 81 60 80

Development Department 104 Huang Yumei Manager 72 80 74

Sales Department 105 Xie Yuanyuan Secretary 82 89 79

Sales Department 106 Zheng Guanghui Supervisor 50 79 82

Sales Department 107 Lin Wenjun None 83 52 45

Sales Department 108 Huang Shanshan None 89 78 58

Sales Department 109 Chen Hongtao None 80 85 80

Human Resources Department 110 Jiang Xiaoyan Secretary 80 84 68

Human Resources Department 111 Li Jianbo Supervisor 90 77 84

Human Resources Department 112 Zeng Jia Manager 88 90 91

Human Resources Department 113 Wang Xiaoning Supervisor 92 78 90

2. Operation steps:

1. Enter the conditions to be filtered.

2. Select a cell in the data area.

3.Data/Filter/Advanced Filter

3. Example: To filter out "those in charge who failed the company profile", enter the conditions first (any blank cell will do):

Professional Title Company Profile

director

Select any cell in the data area.

Use "Filter"/"Advanced Filter" in the "Data" menu to pop up the dialog box.

List area input: the absolute address of the data area.

Conditional area input: the absolute address where the condition is located (steps 4, 5, and 7 can be directly dragged with the mouse during operation)

Copy to input: The absolute address of the location where you want to generate a new table.

Remember to select "Method" as "Copy filter results to other locations"

After completing the above operations, click OK directly to generate the new results to the location you want.

4. Result display:

Department No. Name Title Company Profile Rules and Regulations Legal Knowledge

Sales Department 106 Zheng Guanghui Supervisor 50 79 82

Advanced filtering in excel

You may be familiar with the "Auto Filter" function in Excel. It can basically handle filtering operations with simple conditions. However, the final results that meet the conditions can only be displayed in the original data table, and those that do not meet the conditions will be automatically hidden. If you want to filter records containing specified keywords and display the results in two tables for data comparison or other situations, "automatic filtering" is somewhat limited. After all, "point-and-shoot cameras" have limited functions, so let's try an "advanced camera"! Proficient in using "advanced filtering", no matter how complex the conditions are, you can screen them all.

1. One-step screening of specific characters

Now in this form, Xiao Li wants to find all employee records with the surname "Chen". He thought about it and quickly got the result.

As shown in Figure 1, enter the filtered field name "Name" in any cell outside the data area (such as B17), and enter the filtering condition "Chen*" in the B18 cell immediately below it. . Then click the "Data→Filter→Advanced Filtering" command, and in the pop-up "Advanced Filtering" dialog box, select the "Copy filter results to other locations" radio button in the filtering method. Set "List Range" to "$A$1:$F$15", "Condition Range" to "$B$17:$B$18", and "Copy to" to "$A$20:$F$20". Click the "OK" button, and the system will automatically filter out the records that meet the conditions and copy them to the specified cell range starting from A20 (as shown in Figure 2).

Tips: If you enter the filter condition "*Chen" in cell B18 in Figure 1, you can filter employee records containing the word "Chen" in their names (that is, the word "Chen" is not necessarily the first one in their names) character, which can be anywhere in the name).

2. Smart screening of blank data

Next, Xiao Li also needs to search for records of employees without professional titles. How to proceed?

As shown in Figure 3, he first entered the filtered field name "Professional Title" in any cell outside the data area (such as E17), and then entered the filtering conditions "in the E18 cell immediately below it" *".

Next step, open "Advanced Filtering → Copy Filter Results to Other Locations", set the "List Area", "Conditional Area" and "Copy to" locations, click the "OK" button, and the system will automatically Records that meet the conditions are filtered out and copied to the specified cell range (as shown in Figure 4).

Tips: In this example, if the field to be filtered is numerical, you need to change the filtering condition to "=" (directly enter the "=" sign and press Enter). Conversely, if you want to filter employee records with non-empty job titles, you only need to change the filtering conditions in Figure 3 to "*". If the specified filter field is a numeric field, enter the filter condition "".

3. Screen together if multiple conditions are met

Here comes a more complex task. This form needs to find results that meet multiple conditions: employee records that are male, aged no less than 30 years old, and contain the keyword "engineer" in the title field. This is not a problem for Xiao Li either.

As shown in Figure 5, enter the filtered field names "Gender", "Age" and "Professional Title" in any cell area outside the data area (such as A17:C17), and click Enter the filter conditions "Male", ">=30" and "*Engineer" respectively in the cells A18:C18. Then enter "Advanced Filtering → Copy Filter Results to Other Locations", set the locations of "List Area", "Conditional Area" and "Copy To", and confirm. The system will automatically filter out the records that meet the conditions and copy them to the specified location. in the cell range (as shown in Figure 6).

Tips: If you enter the filtering conditions in the same row, the system will automatically find records that meet all specified conditions at the same time and filter them out. If you want to find employee records where all field values ​​are non-empty, you only need to use "*" for the text type and "" for the numeric type of the specified filtering conditions, and enter these filtering conditions in the same line.

4. "Select one from multiple" can also filter

Sometimes when searching, if one of several conditions is met, for example, if you want to find records of employees who are not less than 30 years old or whose job title is "senior engineer", how should you filter?

As shown in Figure 7, Xiao Li enters the filtered field names "Age" and "Professional Title" in any cell area outside the data area (such as A17:B17), and in A18 immediately below it: Enter the filter conditions ">=30" and "Senior Engineer" respectively in cell area B18. Similar to the above method, after he selects the "Copy filter results to other locations" button, he sets the "List area", "Condition area" and "Copy to" locations, and confirms that the records that meet the conditions will be filtered out and copied. to the specified cell range (as shown in Figure 8).

Tips: In all the above filtering operations, if you want to make the filtering results non-duplicate, just select the "Select non-duplicate records" check box in the "Advanced Filtering" dialog box and then perform the corresponding filtering operation. Can.

The above is excerpted from Computer Enthusiasts

How to use advanced filtering in excel

Advanced filtering is very simple. Just enter the conditions and then you can perform advanced filtering. Here is a simple example, I hope it will be helpful to you.

1. Data area:

Department No. Name Title Company Profile Rules and Regulations Legal Knowledge

Development Department 101 Li Hong None 85 80 83

Development Department 102 Wu Shiqing Secretary 69 75 84

Development Department 103 Zhang Mingxiu Supervisor 81 60 80

Development Department 104 Huang Yumei Manager 72 80 74

Sales Department 105 Xie Yuanyuan Secretary 82 89 79

Sales Department 106 Zheng Guanghui Supervisor 50 79 82

Sales Department 107 Lin Wenjun None 83 52 45

Sales Department 108 Huang Shanshan None 89 78 58

Sales Department 109 Chen Hongtao None 80 85 80

Human Resources Department 110 Jiang Xiaoyan Secretary 80 84 68

Human Resources Department 111 Li Jianbo Supervisor 90 77 84

Human Resources Department 112 Zeng Jia Manager 88 90 91

Human Resources Department 113 Wang Xiaoning Supervisor 92 78 90

2. Operation steps:

1. Enter the conditions to be filtered.

2. Select a cell in the data area.

3.Data/Filter/Advanced Filter

3. Example: To filter out "those in charge who failed the company profile", enter the conditions first (any blank cell will do):

Professional Title Company Profile

director

Select any cell in the data area.

Use "Filter"/"Advanced Filter" in the "Data" menu to pop up the dialog box.

List area input: the absolute address of the data area.

Conditional area input: the absolute address where the condition is located (steps 4, 5, and 7 can be directly dragged with the mouse during operation)

Copy to input: The absolute address of the location where you want to generate a new table.

Remember to select "Method" as "Copy filter results to other locations"

After completing the above operations, click OK directly to generate the new results to the location you want.

4. Result display:

Department No. Name Title Company Profile Rules and Regulations Legal Knowledge

Sales Department 106 Zheng Guanghui Supervisor 50 79 82

The above is the detailed content of Master how to use excel's advanced filtering function. For more information, please follow other related articles on the PHP Chinese website!