How to use advanced filter in excel with multiple criteria

This example shows 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).

And 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.Or 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

1.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.

Note: This above written article is an attempt to show you how to use advanced filter in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.