Highline Excel 2016 Class 03-02 Filter


Highline Excel 2016 Professional Class taught by Michael excelisfun Girvin

WORKBOOKS

Highline Excel 2016 Class 03 Start Workbook
Highline Excel 2016 Class 03 Finished Workbook
Highline Excel 2016 Class 03 2015 Data
Highline Excel 2016 Class 03 2016 Data
Highline Excel 2016 Class 03 2017 Data

Filter

  • What does Filtering do?
    • For a Proper Data Set, the Filter feature allows you to specify conditions/criteria to display only the records that match the given conditions/criteria, while hiding the records that do not match.
    • You apply conditions/criteria to the data set to get a “Filtered Data Set”.
    • Filter is perfect for extracting records from a Proper Data Set that meet a set of conditions or criteria.
      • After you filter, use keyboards to copy and paste into a new workbook:
        • Ctrl + * (Number Pad) or Ctrl + Shift + 8 (Highlight Whole Table)
        • Ctrl + C (Copy)
        • Ctrl + N (Create New Workbook)
        • Ctrl + V (Paste Filtered Data Set)
        • F12 (Save As)
        • Type Workbook Name
        • Enter to activate Save button.
  • Add Filter Drop-Down Arrows to each Field in a Proper Data Set:
    • Filter Button:
      • Editing group in Home Ribbon Tab
      • Sort and Filter group in Data Ribbon Tab
    • Keyboard for Filter:
      • Ctrl + Shift + L = Filter (or Alt, D, F, F)
    • If you Convert the Proper Data Set to an Excel table, Filter drop-down arrows appear.
  • Filter dropdown arrows allow you to filter based on:
    • Check boxes for each item in the unique list of items from the field.
    • Special Data Type Filters:
      • Date Filter
      • Number Filter
      • Text Filter
    • Search textbox
  • Different Types Logical Constructs For Applying Criteria:
    • OR Logical Test (using OR Criteria):
      • You can have two or more criteria for an OR Logical Test.
      • If we select the check the boxes for “Alma” and “Rina” in the Sales Rep Field:
        • For each record we are asking two questions:
          • “Is the Sales Rep Alma?”
          • “Is the Sales Rep Rina?”
        • For each Record we can get these possible answers:
          • TRUE, FALSE
          • FALSE, TRUE
          • FALSE, FALSE.
      • For an OR Logical Test you must get “At Least 1 TRUE”, in order for the record to be included in the filtered data set.
      • For Filtering, when we are asking the OR Criteria Question, we are often asking the question of only ONE Column.
    • AND Logical Test (using AND Criteria):
      • You can have two or more criteria for an AND Logical Test.
      • If we select the check the boxes for “Alma” on the Sales Rep Field and “Chevy” on the Auto Field:
        • For each record we are asking two questions:
          • “Is the Sales Rep Alma?”
          • “Is the Sales Rep Rina?”
        • For each Record we can get these possible answers:
          • TRUE, FALSE
          • FALSE, TRUE
          • FALSE, FALSE
          • TRUE, TRUE
      • For an AND Logical Test you must get “All Are TRUE”, in order for the record to be included in the filtered data set.
    • BETWEEN Logical Test is a form of AND Logical Test that has an upper and lower limit:
      • Only items that are between the upper and lower limit are included.
      • Example: Date Filters that only want records that are between January 1, 2016 and Jan 5, 2016.
    • NOT Logical Test
      • When you specify NOT Criteria, all records that match the NOT Criteria are hidden.
    • When you get a Filter Result with NO RECORDS, it means:
      • There are no records that match your criteria.
      • Your query was incorrect, meaning, the criteria you applied when creating the filter were incorrect.