The key to filtering data in Excel is to master the use of basic filtering, advanced filtering, search box and multi-column joint filtering. 1. Basic filtering: Set the conditions by clicking the drop-down arrow, the shortcut key is Ctrl Shift L; 2. Advanced filtering: Used to deal with complex conditions of the "As" and "OR" relationship, the condition area needs to be set, the same row represents "As", and different rows represent "OR"; 3. Search box: It is suitable for quickly finding specific keywords, and you can use the search function in the drop-down menu; 4. Multi-column joint filtering: Set the conditions in each column separately, and the "As" relationship between all columns, and multiple options in a single column are "OR" relationships, which can flexibly respond to multiple filtering needs.

It is actually not difficult to filter data in Excel, but many people only know basic operations and are easily stuck when encountering complex situations. In fact, as long as you master a few key points, whether it is simple screening or multiple condition combinations, you can easily deal with it.

Basic filtering: quickly find the data you want

Excel's automatic filtering function is the most commonly used tool. Click any cell in the data area, then press Ctrl Shift L , or select "Start" → "Sort and Filter" → "Filter" in the menu bar to enable the filtering function.

At this time, a drop-down arrow will appear next to each column of titles. After clicking, you can check the content to be displayed, or you can set conditions for numbers, text or dates, such as "greater than", "include", "equal to", etc.

Tips: If the table has a frozen pane or title row, be sure to make sure the filter button appears on the correct row, otherwise data will be missed or errors will occur.

Advanced Filtering: Handle more complex condition combinations

When you need to meet multiple conditions at the same time (such as "the department is a sales department and the salary is greater than 8,000"), or meet a certain group or multiple groups of "or" relationships, ordinary filtering is not enough. At this time, you can use "advanced filtering".

The operation steps are as follows:

• Prepare a conditional area: Copy the field name to a blank area, and fill in the filter conditions in the following line
• The condition represents "and" on the same line, and different lines represent "or"
• Click the "Data" tab → "Advanced Filter" → Select List Area and Conditional Area

For example, if you want to filter the records of "department = sales department" and "salary>8000", write these two conditions on the same line; if you also want to include people with "department = marketing department" and "salary>9000", put this set of conditions in the next line.

Use the search box to quickly locate content

If you just want to temporarily view a certain part of the content, such as finding all records that appear in "Zhang San", you can open the filter and click the drop-down menu in a certain column, and directly use the search box above to enter keywords in the pop-up window.

This method is especially suitable for situations where the data volume is large but you only want to find it quickly, without setting complex rules.

Multi-column joint filtering: Flexible combination of your needs

Sometimes you want to filter together according to multiple fields, such as "Product Type=A", "Region=South China", "Month>=March". At this time, you only need to set the conditions in the filter menu of each column separately, and Excel will automatically help you find all matching rows.

What should be noted is:

• All conditions are "and" relationships
• If multiple options are set in one column (such as multiple regions are checked), it is equivalent to a "or" relationship

Basically these commonly used methods. Although filtering seems simple, it can save a lot of time to sort data if you use it well, especially when facing thousands of records. Many novices are prone to ignore the format requirements of the conditional area or misoperation, resulting in the wrong results. Paying attention to these details can almost cope with most scenarios.

The above is the detailed content of how to filter data in excel. For more information, please follow other related articles on the PHP Chinese website!