Scribd Logo
Upload

Welcome to Scribd!

  • 16 views

    Filter

    Uploaded by

    Deepak Sharma
    This document provides instructions for using different types of filters in Microsoft Excel, including basic filters to select specific criteria, advanced filters to select multiple criteria using "AND" or "OR" logic, and number filters to select numeric data that meets certain conditions. It explains how to filter data to display only records from the USA in the 4th quarter, records from the USA or UK using advanced filters, and records where the sales amount is greater than $10,000 using a number filter. Steps are provided for setting up and applying each type of filter.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Filter

    Uploaded by

    Deepak Sharma
    16 views10 pages
    This document provides instructions for using different types of filters in Microsoft Excel, including basic filters to select specific criteria, advanced filters to select multiple criteria using "AND" or "OR" logic, and number filters to select numeric data that meets certain conditions. It explains how to filter data to display only records from the USA in the 4th quarter, records from the USA or UK using advanced filters, and records where the sales amount is greater than $10,000 using a number filter. Steps are provided for setting up and applying each type of filter.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for using different types of filters in Microsoft Excel, including basic filters to select specific criteria, advanced filters to select multiple criteria using "AND" or "OR" logic, and number filters to select numeric data that meets certain conditions. It explains how to filter data to display only records from the USA in the 4th quarter, records from the USA or UK using advanced filters, and records where the sales amount is greater than $10,000 using a number filter. Steps are provided for setting up and applying each type of filter.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    16 views10 pages

    Filter

    Uploaded by

    Deepak Sharma
    This document provides instructions for using different types of filters in Microsoft Excel, including basic filters to select specific criteria, advanced filters to select multiple criteria using "AND" or "OR" logic, and number filters to select numeric data that meets certain conditions. It explains how to filter data to display only records from the USA in the 4th quarter, records from the USA or UK using advanced filters, and records where the sales amount is greater than $10,000 using a number filter. Steps are provided for setting up and applying each type of filter.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 10

    Dr.

    KHIMYA S TINANI
    [email protected]
    B.Sc.(Sem-V)-Data Analysis using Microsoft Excel
    Filter:
    Filter your Excel data if you only want to display records that
    meet certain criteria.
    1. Click any single cell inside a data set.
    2. On the Data tab, in the Sort & Filter group, click Filter.

    Arrows in the column headers appear.

    3. Click the arrow next to Country.


    4. Click on Select All to clear all the check boxes, and click the
    check box next to USA.

    1
    5. Click OK.
    Result: Excel only displays the sales in the USA.

    6. Click the arrow next to Quarter.


    7. Click on Select All to clear all the check boxes, and click the
    check box next to Qtr 4.

    2
    8. Click OK.
    Result: Excel only displays the sales in the USA in Qtr 4.

    9. To remove the filter, on the Data tab, in the Sort & Filter
    group, click Clear. To remove the filter and the arrows,
    click Filter.

    3
    Advanced Filter
    And Criteria | Or Criteria | Formula as Criteria
    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 Criteria
    To 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
    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:
    We can achieve the same result with the normal filter. We need
    the Advanced Filter for Or criteria.
    Or Criteria
    To 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.

    5
    Result.

    Formula as Criteria
    To 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.

    6
    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.
    Number Filter:
    This example teaches you how to apply a number filter and
    a text filter to only display records that meet certain criteria.
    1. Click any single cell inside a data set.
    2. On the Data tab, in the Sort & Filter group, click Filter.

    7
    Arrows in the column headers appear.

    Number Filter
    To apply a number filter, execute the following steps.
    3. Click the arrow next to Sales.
    4. Click Number Filters (this option is available because the
    Sales column contains numeric data) and select Greater Than
    from the list.

    8
    5. Enter 10,000 and click OK.

    Result: Excel only displays the records where Sales is greater


    than $10,000.

    9
    Note: you can also display records equal to a value, less than a
    value, between two values, the top x records, records that are
    above average, etc.

    10

    You might also like