Filter by using advanced criteria Excel 2007

Author: mety Labels::

To filter a range of cells by using complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.), use the Advanced command in the Sort & Filter group on the Data tab.

The Advanced command works differently from the Filter command in several important ways.

  • It displays the Advanced Filter dialog box instead of the AutoFilter menu.
  • You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table you want to filter. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

    Example: Criteria range (A1:C4) and data range (A6:C10) used for the following procedures

    The example may be easier to understand if you copy it to a blank worksheet.

    ShowHow to copy an example

    1. Create a blank workbook or worksheet.
    2. Select the example in the Help topic.

      Note Do not select the row or column headers.

      Selecting an example from Help

      Selecting an example from Help
    3. Press CTRL+C.
    4. In the worksheet, select cell A1, and press CTRL+V.
    5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544

Using the equal sign to type text or a value

Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

=''=entry''

Where entry is the text or value you want to find. For example:

What you type in the cellWhat Excel evaluates and displays
="=Davolio"=Davolio
="=3000"=3000

Considering case-sensitivity

When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search.

Using pre-defined names

You can name a range Criteria, and the reference for the range will appear automatically in the Criteria range box. You can also define the name Database for the range of data to be filtered and define the name Extract for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes, respectively.

Creating criteria by using a formula

You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) as your criterion. Remember the following important points:

Filter by using multiple criteria in one column where any criteria can be true

Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range. In the example, you would enter:
    ABC
    1TypeSalespersonSales
    2="=Davolio"
    3="=Buchanan"
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$C$3.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    8MeatDavolio$450
    9produceBuchanan$6,328
    10ProduceDavolio$6,544

Filter by using multiple criteria in multiple columns where all criteria must be true

Boolean logic: (Type = "Produce" AND Sales > 1000)

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range. In the example, you would enter:
    ABC
    1TypeSalespersonSales
    2="=Produce" >1000
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$C$2.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    9produceBuchanan$6,328
    10ProduceDavolio$6,544

Filter by using multiple criteria in multiple columns where any criteria can be true

Boolean logic: (Type = "Produce" OR Salesperson = "Davolio")

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in the different columns and rows of the criteria range. In the example, you would enter:
    ABC
    1TypeSalespersonSales
    2="=Produce"
    3="=Buchanan"
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$B$3.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    9produceBuchanan$6,328
    10ProduceDavolio$6,544

Filter by using multiple sets of criteria where each set includes criteria for multiple columns

Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate columns and rows. In the example, you would enter:
    ABC
    1TypeSalespersonSales
    2="=Davolio" >3000
    3="=Buchanan">1500
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$C$3.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    9produceBuchanan$6,328
    10ProduceDavolio$6,544

Filter by using multiple sets of criteria where each set includes criteria for one column

Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. To find rows that meet multiple sets of criteria, where each set includes criteria for one column, include multiple columns with the same column heading. In the example, you would enter:
    ABCD
    1TypeSalespersonSalesSales
    2>6000<6500
    3<500
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$D$3.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    8MeatDavolio$450
    9produceBuchanan$6,328

Filter by using wildcard criteria to find text values that share some characters but not others

To find text values that share some characters but not others, do one or more of the following:

  • Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."
  • Use a wildcard character.
    UseTo find
    ? (question mark)Any single character
    For example, sm?th finds "smith" and "smyth"
    * (asterisk)Any number of characters
    For example, *east finds "Northeast" and "Southeast"
    ~ (tilde) followed by ?, *, or ~A question mark, asterisk, or tilde
    For example, fy91~? finds "fy91?"

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. In the rows below the column labels, type the criteria that you want to match. In the example, you would enter:
    ABC
    1TypeSalespersonSales
    2Me
    3="=?u*"
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$B$3.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    7BeveragesSuyama$5,122
    8MeatDavolio$450
    9produceBuchanan$6,328

Filter by using a formula for values greater than the average of all values in the data range

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. In the rows below the column labels, type the criteria that you want to match as a formula that finds a value in the Sales column greater than the average of all the Sales values. In the example, you would enter:
    ABCD
    1TypeSalespersonSalesCalculated Average
    2=C7>AVERAGE($C$7:$C$10)
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $D$1:$D$2.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    7BeveragesSuyama$5,122
    9produceBuchanan$6,328
    10ProduceDavolio$6,544

Filter by using a formula for text in a case-sensitive search

  1. Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

    The example may be easier to understand if you copy it to a blank worksheet.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    ABC
    TypeSalespersonSales
    TypeSalespersonSales
    BeveragesSuyama$5122
    MeatDavolio$450
    produceBuchanan$6328
    ProduceDavolio$6544
  2. In the rows below the column labels, type the criteria that you want to match as a formula by using the EXACT function to perform a case-sensitive search . In the example, you would enter:
    ABCD
    1TypeSalespersonSalesExact Match
    2=EXACT(A7, "Produce")
  3. Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.

    Excel Ribbon Image

  5. Do one of the following:
    • To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
    • To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

      Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $D$1:$D$2.

    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog Button image.

  7. In the example, the filtered result for the range of data would be:
    ABC
    6TypeSalespersonSales
    10ProduceDavolio$6,544

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2