How to Use Advanced Filter in Excel With Multiple Criteria

Here is an example to show you how to use an advanced Excel filter to limit the records that are displayed to those that satisfy stringent requirements. You must enter the criteria on the worksheet before using the Advanced Filter. Create a Criteria range above your data collection (blue border below is just for demonstration purposes). identical column headings should be used. Make sure your criteria range and the data set are separated by at least one blank row.

The Excel Advanced Filter is an improved version of the standard filter, as its name suggests. This can be used when you need to filter your data collection using more intricate criteria.

The following are some variations between the standard filter and the advanced filter:

While the existing data set will be filtered using the regular data filter, you can also extract the data set to another location using Excel's advanced filter.Use sophisticated criteria using Excel Advanced Filter. For instance, if you have sales data, you may filter it using the criteria that Bob is the sales representative and either the North or South region (we will see how to do this in examples)You can isolate distinct records from your data by using the Excel Advanced Filter (more on this in a second).

Criteria in excel online, 2016 and 2019

1.Execute the following procedures to display the sales in the USA and for Qtr 4.On the spreadsheet, enter the criteria shown below.

2.Select a data set cell by clicking on it.Click Advanced under the Sort & Filter group on the Data tab.

3.Choose the range A1:D2 by clicking in the Criteria range box (blue).Click on ok.

4.Take note of the choices to display just unique records and copy your filtered data set to another location (if your data set contains duplicates).Result:

5.So far, nothing revolutionary. The standard filter will produce the same outcome. The Advanced Filter is required for the Or criterion.

Criteria in Excel:

1.Execute the following procedures to display the sales in the USA in Qtr 4 or the UK in Qtr 1.On the spreadsheet, enter the criteria shown below.

2.Adjust the Criteria range to the range A1:D3 by clicking Advanced in the Sort & Filter group on the Data tab (blue).Click on ok.

Result:

Formula as criteria:

Follow these instructions to display sales in the USA for the fourth quarter that exceeded $10,000 or sales in the UK for the first quarter.Fill up the spreadsheet with the criteria (+formula) listed below.Adjust the Criteria range to the range A1:E3 by clicking Advanced in the Sort & Filter group on the Data tab (blue).Click on ok.

Result:

Always start a new column for a formula. Use a column label that is present in your data set; if not, don't use it. Make a relative reference to the column's top-left cell (B6). The equation must result in a TRUE or FALSE result.

Managing Filtered Data

Managing Filtered Data in Excel involves various tasks such as copying filtered data to a new worksheet, filtering data without disturbing the original dataset, and clearing/removing filters. Here's a brief overview of each:

1. Copying Filtered Data to a New Worksheet:

• After applying a filter to your data, select the filtered data range.

• Right-click on the selected range and choose "Copy" or press Ctrl+C.

• Move to a new worksheet or create a new worksheet.

• Right-click on the destination cell and choose "Paste" or press Ctrl+V to paste the copied filtered data.

2. Filtering Data Without Disturbing the Original Dataset:

• Apply the desired filter to your data range.

• Once the data is filtered, select the filter dropdown arrow for the desired column.

• Customize the filter criteria or select specific values to further narrow down the data.

3. The filtered results will be displayed without affecting the original dataset.

• Clearing and Removing Filters:

• To clear filters for a specific column, click on the filter dropdown arrow for that column.

• Select "Clear Filter" or choose the desired clear option to remove the filter for that column.

• To remove all filters applied to the entire dataset, navigate to the Data tab and click on "Clear" under the Sort & Filter group.

• This action will remove all filters and display the entire dataset.

When working with advanced filters in Excel, there are several tips and tricks that can enhance your filtering capabilities. Here are some valuable techniques:

Tips and Tricks for Advanced Filters

When working with advanced filters in Excel, there are several tips and tricks that can enhance your filtering capabilities. Here are some valuable techniques:

1. Filtering Data with Formulas:

• Instead of using fixed criteria, you can use formulas as criteria in advanced filters.

• Formulas allow you to create dynamic filtering conditions based on calculations, logical comparisons, or other functions.

• By incorporating formulas into your advanced filter criteria, you can perform more sophisticated data analysis and extract specific subsets of data.

2. Creating Dynamic Filters with Named Ranges:

• Named ranges in Excel allow you to assign a name to a specific range of cells.

• By using named ranges as criteria in advanced filters, you can create dynamic filters that automatically adjust when the underlying data changes.

• This technique simplifies the filtering process and makes it easier to maintain and update your filters.

3. Using Advanced Filter with PivotTables:

• PivotTables provide a powerful way to analyze and summarize data in Excel.

• You can leverage the advanced filter feature to filter data before creating a PivotTable.

• By applying advanced filters to your data before creating a PivotTable, you can refine the dataset and focus on specific subsets of data that are relevant to your analysis.

FAQs about using advanced filters in excel

1. What is the difference between basic and advanced filters in Excel?

The main differences between basic and advanced filters in Excel:

2. Can I apply multiple filters simultaneously in Excel?

Yes, in Excel, you can apply multiple filters simultaneously to refine and narrow down your data analysis. By using advanced filter options, you can specify multiple criteria and filter the data based on those conditions

3. How do I use wildcards in Excel filters?

In Excel filters, you can use wildcards to perform flexible and dynamic filtering based on patterns or partial matches. The two main wildcards you can use are:

1. Asterisk (*) Wildcard:

• Represents any number of characters (including zero characters).

• Use it to filter data based on a pattern or portion of a value.

• Example: "J*" filters names starting with "J".

2. Question Mark (?) Wildcard:

• Represents a single character.

• Use it to match any character in a specific position.

• Example: "?o??" filters names with four characters where the second character is "o".

Summary

In this ultimate guide to Excel advanced filter, you have learned how to utilize the power of multiple criteria to extract specific data subsets in Excel. By going beyond the limitations of basic filters, you can enhance your data analysis capabilities and make more informed decisions. Additionally, we introduced you to WPS Office, a feature-rich and cost-effective alternative to Microsoft Office, which can further simplify your Excel tasks. With its user-friendly interface and compatibility, WPS Office is a valuable tool for your spreadsheet needs.