# Highline Excel 2016 Class 03-02 Filter

Highline Excel 2016 Professional Class taught by Michael excelisfun Girvin

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