And Criteria | Or Criteria | Formula as Criteria Show
This example teaches you how to apply an advanced filter in Excel to only display records that meet complex criteria. When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be sure there's at least one blank row between your Criteria range and data set. And CriteriaTo display the sales in the USA and in Qtr 4, execute the following steps. 1. Enter the criteria shown below on the worksheet. 2. Click any single cell inside the data set. 3. On the Data tab, in the Sort & Filter group, click Advanced. 4. Click in the Criteria range box and select the range A1:D2 (blue). 5. Click OK. Notice the options to copy your filtered data set to another location and display unique records only (if your data set contains duplicates). Result. No rocket science so far. We can achieve the same result with the normal filter. We need the Advanced Filter for Or criteria. Or CriteriaTo display the sales in the USA in Qtr 4 or in the UK in Qtr 1, execute the following steps. 6. Enter the criteria shown below on the worksheet. 7. On the Data tab, in the Sort & Filter group, click Advanced, and adjust the Criteria range to range A1:D3 (blue). 8. Click OK. Result. Formula as CriteriaTo display the sales in the USA in Qtr 4 greater than $10.000 or in the UK in Qtr 1, execute the following steps. 9. Enter the criteria (+formula) shown below on the worksheet. 10. On the Data tab, in the Sort & Filter group, click Advanced, and adjust the Criteria range to range A1:E3 (blue). 11. Click OK. Result. Note: always place a formula in a new column. Do not use a column label or use a column label that is not in your data set. Create a relative reference to the first cell in the column (B6). The formula must evaluate to TRUE or FALSE. The tutorial explains the basics of Excel's Advanced Filter and shows how to use it to find the records that meet one or more complex criteria. If you had a chance to read our previous tutorial, you know that Excel Filter provides a variety of options for different data types. Those inbuilt filtering options for text, numbers, and dates can handle many scenarios. Many, but not all! When a regular AutoFilter can't do what you want, use the Advanced Filter tool and configure the criteria exactly suited to your needs. Excel's Advanced Filter is really helpful when it comes to finding data that meets two or more complex criteria such as extracting matches and differences between two columns, filtering rows that match items in another list, finding exact matches including uppercase and lowercase characters, and more. Advanced Filter is available in all versions of Excel 365 - 2003. Please click on the links below to learn more. Excel Advanced Filter vs. AutoFilterCompared to the basic AutoFilter tool, Advanced Filter works differently in a couple of important ways.
Below you will find the detailed guidance on how to use Advanced Filter in Excel as well as some useful examples of advanced filters for text and numeric values. How to create an advanced filter in ExcelUsing Excel Advanced Filter is not as easy as applying AutoFilter (as is the case with many "advanced" things :) but it's definitely worth the effort. To create an advanced filter for your sheet, perform the following steps. 1. Organize the source dataFor better results, arrange your data set following these 2 simple rules:
For example, here's how our sample table looks like: 2. Set up the criteria rangeType your conditions, aka criteria, in a separate range on the worksheet. In theory, the criteria range can reside anywhere in the sheet. In practice, it's more convenient to place it at the top and separate from the data set with one or more blank rows. Advanced criteria notes:
For example, to filter records for the North region whose Sub-total is greater than or equal to 900, set up the following criteria range:
For the detailed information about the comparison operators, wildcards and formulas that you can use in your criteria, please see Advanced Filter criteria range. 3. Apply Excel Advanced FilterIn the criteria range in place, apply an advanced filter in this way:
The Excel Advanced Filter dialog box will appear and you set it up as explained below. 4. Configure the Advanced Filter parametersIn the Excel Advanced Filter dialog window, specify the following parameters:
If you choose "Copy the results to another location", select the upper-left cell of the range where you want to paste the filtered rows. Make sure the destination range has no data anywhere in the columns because all cells below the copied range will be cleared.
In addition, the check box in the lower-left corner of the Advanced Filter dialog window lets you display unique records only. For instance, this option can help you extract all different (distinct) items in a column. In this example, we are filtering the list in place, so configure the Excel Advanced Filter parameters in this way: Finally, click OK, and you will get the following result: This is great… but the same result can actually be achieved with the normal Excel AutoFilter, right? Anyway, please don't hurry to leave this page, because we have only scratched the surface so you've got the basic idea of how Excel Advanced Filter works. Further on in the article, you will find a few examples that can only be done with advanced filter. To make things easier for you to follow, let's learn more about the Advanced Filter criteria first. Excel Advanced Filter criteria rangeAs you have just seen, there is no rocket science in using Advanced Filter in Excel. But once you learn the nitty-gritty details of the Advanced Filter criteria, your options will be almost unlimited! Comparison operators for numbers and datesIn the Advanced Filter criteria, you can compare different numeric values using the following comparison operators.
The usage of comparison operators with numbers is obvious. In the above example, we already used the numeric criteria >=900 to filter records with Subtotal greater than or equal to 900. And here's another example. Supposing you want to display the North region records for the month of July with Amount greater than 800. For this, specify the following conditions in the criteria range:
And now, run the Excel Advanced Filter tool, specify the List range (A4:D50) and Criteria
range (A2:D2) and you will get the following result: Note. Regardless of the date format used in your worksheet, you should always specify the full date in the Advanced Filter criteria range in the format that Excel can understand, like 7/1/2016 or 1-Jul-2016. Advanced filter for text valuesApart from numbers and dates, you can also use the logical operators to compare text values. The rules are defined in the table below.
As you see, creating an advanced filter for text values has a number of specificities, so let's elaborate more on this. Example 1. Text filter for exact matchTo display only those cells that are exactly equal to a specific text or character, include the equal sign in the criteria. For instance, to filter only Banana items, use the following criteria: ="=banana". Microsoft Excel will display the
criteria as =banana in a cell, but you can view the entire expression in the formula bar: As you can see in the screenshot above, the criteria ="=banana" shows only the Banana records with Sub-total greater than or equal to 900, ignoring Green banana and Goldfinger banana. Note. When filtering numeric values that are exactly equal to a given value, you may or may not use the equal sign in the criteria. For instance, to filter records with subtotal equal to 900, you can utilize any of the following Sub-total criteria: ="=900", =900 or simply 900. Example 2. Filter text values that begin with a specific character(s)To display all cells whose contents begin with a specified text, just type that text in the criteria range without the equal sign or double quotes. For example, to filter all "green" items with subtotal greater than or equal to 900, use the following criteria:
Excel Advanced Filter with wildcardsTo filter text records with partial match, you can use the following wildcard characters in the Advanced Filter criteria:
The following table provides a few criteria range examples with wildcards.
And here is the simplest wildcard criteria in action (*banana*), which finds all cells containing the word "banana": Formulas in the Advanced Filter criteriaTo create an advanced filter with more complex conditions, you can use one or more Excel functions in the criteria range. For the formula-based criteria to work correctly, please follow these rules:
For example, to filter rows where August sales (column C) are greater than July sales (column D), use the criteria =D5>C5, where 5 is the first row of data: Note. If your criteria includes just one formula like in this example, be sure to include at least 2 cells in the criteria range (formula cell and heading cell). For more complex examples of multiple criteria based on formulas, please see How to use Advanced Filter in Excel - criteria range examples. Using Advanced Filter with AND vs. OR logicAs already mentioned in the beginning of this tutorial, Excel Advanced filter can work with AND as well as OR logic depending on how you set up the criteria range:
To make things easier to understand, consider the following examples. Excel Advanced Filter with AND logicTo display records with Sub-total >=900 AND Average >=350, define both criteria on the same row: Excel Advanced Filter with OR logicTo display records with Sub-total >=900 OR Average >=350, place each condition on a separate row: Excel Advanced Filter with AND as well as OR logicTo display records for the North region with Sub-total greater than or equal to 900 OR Average greater than or equal to 350, set up the criteria range in this
way: To put it differently, the criteria range in this example translates to the following condition: (Region=North AND Sub-total>=900) OR (Region=North AND Average >=350) Note. The source table in this example contains only four regions: North, South, East and West, therefore we can safely use North in the criteria range. If there were any other regions containing the word "north" like Northwest or Northeast, then we would use the exact match criteria: How to extract only specific columnsWhen configuring Advanced Filter so that it copies the results to another location, you can specify which columns to extract.
As the result, Excel has filtered the rows according to the conditions listed in the criteria range (North region items with Sub-total >=900), and copied the 3 columns to the specified location: How to copy filtered rows to another worksheetIf you open the Advanced Filter tool in the worksheet containing your original data, choose "Copy to another location" option, and select the Copy to range in another sheet, you would end up with the following error message: "You can only copy filtered data to the active sheet". However, there is a way to copy filtered rows to another worksheet, and you have already got the clue - just start Advanced Filter from the destination sheet, so that it will be your active sheet. Supposing, your original table is in Sheet1, and you want to copy the filtered data to Sheet2. Here's a super simple way to get it done:
In this example, we are extracting 4 columns to Sheet2, so we typed the corresponding column headings exactly as they appear in Sheet1, and selected the range containing the headings (A1:D1) in the Copy to box: Basically, this is how you use the Advanced Filter in Excel. In the next tutorial, we will have a closer look at more complex criteria range examples with formulas, so please stay tuned! You may also be interested inHow do I copy the results of a different location in Excel?Copy and paste a formula to another cell or worksheet in Excel.... Select the cell with the formula you want to copy.. Press. + C.. Click the cell where you want to paste the formula. ... . To quickly paste the formula with its formatting, press + V. ... . Clicking the arrow gives you a list of options.. How do you perform the advanced filter by copying the records to the output area?In the Advanced Filter dialog, set the Criteria range. I am using just G1 through G2. To copy the results to a different location on the worksheet, click Copy to another location, click in Copy to, select a cell to copy to, click OK, and the filtered results are copied to the location.
How do you create an advanced filter in Excel?Select the entire data set (including the headers). Go Data tab –> Sort & Filter –> Advanced. (You can also use the keyboard shortcut – Alt + A + Q). This will open the Advanced Filter dialog box.
How do I filter data in Excel and copy to another sheet?Solution #1 - Copy only visible filtered data
To do this, open your Excel spreadsheet and select the filtered data. Press Ctrl + C to copy the data. Next, select the worksheet where you'd like to paste the data. Press Ctrl + V to paste the data into the new worksheet.
|