How to Use Number, Text and Date Filters to Extract the Data?

Excel filters are indispensable tools for handling extensive spreadsheet databases. Through the use of number, text, and date filters, the exact data you need can be pinpointed, regardless of the database’s size. This article explores how to leverage these filters to extract precise information, focusing on text and date filters. These functionalities not only streamline the search process but also enable the user to manipulate and analyze data in a more nuanced way. Whether you’re a seasoned data analyst or a beginner in data management, understanding how to employ these filters efficiently can drastically enhance your proficiency and productivity with Excel.

How to Use the Sort & Filters Menu?

Number Filters (one condition, one field)

• Clicking the Points Field for Filtering Options:
  • Click the arrow beside the ‘Points’ field column.
  • Scroll down and select ‘Number Filters.’
  • Choose ‘Greater Than or Equal To’ from the submenu.
• Custom AutoFilter Dialog Window:
  • Upon opening, notice ‘Points’ displayed under the prompt: ‘Show Rows Where > Points > Is greater than or equal to’ (your original Logical Operator) in the first Input box.
• Choosing Logical Operators:
  • Click the down arrow on the Input box’s right side.
  • All Logical Operators are listed in the drop-down menu, and you can reselect if you want a different one.
• Confirming the Selection and Viewing Results:
  • Confirm the original selection (Greater than or equal to).
  • Click OK, and the table reappears with records that match your filter only.
  • If ‘Greater than or equal to 4’ was chosen, records equal to 4 and above (through 12) would display.
• Canceling the Filter:
  • Click the ‘Points’ arrow again and then ‘Clear Filter From “Points”‘ from the Sorting/Filters drop-down menu to view all records again.
• Notes on Filters:
  • ‘Points’ is a numeric field, so available Filters include Logical Operators like Equals, Does Not Equal, Greater Than, etc.
  • Different filters exist for Dates (e.g., Tomorrow, Last Week) and Text (e.g., Begins With, Contains).
  • Some filters overlap between Text and Number fields, but Date filters are unique.
• Extracting Specific Records:
  • Use the data under the Search Input box to extract records equal to values in the table.
  • Click ‘Points,’ then uncheck the box that says ‘Select All.’
  • Check the values you want displayed (such as 4 and 12), then click OK.
• Final Filtering Outcome:
  • Excel removes records that don’t match your filtered query.
  • Only the drivers with 4 points or 12 points remain.

Text Filters (two conditions, multiple fields)

• Accessing Text Filters:
  • Click the arrow beside the field column labeled ‘Violations’ (a Text field).
  • Scroll down and select ‘Text Filters.’
  • From the submenu, notice the available Text filters: Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom Filter.
• Opening the Custom AutoFilter Dialog Window:
  • Notice ‘Violations’ under the prompt that says ‘Show Rows Where—Violation Type: Equals’ in the first Input box.
  • Click the arrow on the Input box’s right side and choose a Violation Type from the list.
• Extracting Specific Driver’s License Data:
  • Follow the same steps as 4-8 under ‘Number Filters’ to extract needed driver’s license data.
  • This could include all drivers with Violation Types like “Running a Red Light,” “Seat Belt Violation,” or “Speeding.”
• Extracting Records for Specific Violations like DUI and/or Reckless Driving:
  • Choose ‘Equals’ in the top left Input box.
  • Select ‘DUI’ from the top right Input box.
  • Click the ‘OR’ circle (tick mark).
  • Choose ‘Equals’ (again) in the bottom left Input box.
  • Select ‘Reckless Driving’ from the bottom right Input box.
• Important Note on Using ‘OR’ instead of ‘AND’:
  • Choosing ‘OR’ means any record with DUI OR Reckless Driving.
  • ‘AND’ would mean records containing both violations.
  • In this database/table, individual records don’t contain multiple violations, though some drivers might.

Date Filters (using custom conditions)

• Overview of Excel’s Date Filters:
  • Offers comprehensive options to extract dates by day, week, month, year, quarter, or year-to-date.
  • Allows dates within ranges and negative logic (e.g., all dates not equal to 2017, or excluding specific months).
• Filtering by Year:
  • Select the ‘Violation Date’ field.
  • Click the arrow beside the Search box under ‘Date Filters’ (on the Sort/Filters submenu) and select ‘YEAR.’
  • Uncheck ‘Select All’, recheck 2016 and 2015, and click ‘OK.’
  • Excel shows all years except 2017 (Does Not Equal 2017).
  • Click ‘Clear Filter From Violation Date’ on the Sort/Filters submenu.
• Filtering by Month:
  • On the same menu, select ‘MONTH.’
  • Click the plus sign beside 2017, 2016, and 2015, and uncheck March, April, May, and Blanks.
  • Excel shows all records excluding March, April, and May.
• Understanding the Extensive List of Date Filters:
  • The filters include Equals, Before, After, Between, Tomorrow, Today, and many more time frames, including Custom Filters.
  • Most filters are one-step processes, displaying results instantly.
• Working with Specific Filters:
  • If you choose Equals, Before, After, Between, or Custom Filters, the Custom AutoFilter dialog window opens for further inputs.
  • For ‘Between,’ Excel adds the conditions automatically.
  • Select the dates from the drop-down lists like ‘Is After or Equal To: 2/22/2017’ and ‘Is Before or Equal To: 5/1/2017,’ then click ‘OK.’
  • Excel displays the requested date range.
• Note on Boolean Operator:
  • Use ‘AND’ for the ‘Between’ Filter to get ALL dates between (A) AND (B).
  • Using ‘OR’ would display the entire table because all dates would match the criteria.

More from us:

• How to Enable Dark Mode in Microsoft Office? A Brief Guide
• The Ultimate Guide to Updated Shadow Fight 3 Promo Codes

Conclusion

Using text and date filters in Excel is essential for any professional handling large datasets. Whether filtering through candidates in an HR database or sorting violations in traffic records, understanding how to properly utilize Excel filters ensures efficiency and precision. Mastery of these filtering techniques can transform data management, enabling quick identification of trends, patterns, and specific details within vast information pools. Moreover, these tools not only save valuable time but also enhance decision-making processes, providing insights that drive strategic planning and execution.

Frequently Asked Questions