Excel filters: How to use number, text and date filters to extract only the data you need

Excel spreadsheet databases work because users can filter the data inside these workbooks. Filters are conditions you specify in databases and spreadsheets to extract only the precise, requested information.

For example, in a Human Resources spreadsheet database, the IT director may want to hire a candidate with a degree in Computer Science plus experience using HTML, Java, and C++ languages. The IT director uses filters based on logical operators to extract all the candidates with these credentials: equal to (Equals) degrees in "Computer Science" plus computer languages equal to (Equals) "HTML, Java, and C++." She may add more conditions, such as candidates equal to (Equals) "CS Software Developer," but not equal to (Does Not Equal) "CS Hardware Engineer," to minimize the list. This selective process can go on and on until the number of candidates is pared down to a reasonable number.

Note that the data extraction is based on the field columns in your database. You cannot extract applicants equal to "speaks Spanish" if the database does not contain a field for languages. Therefore, it's critically important that you create field columns to collect all the information that you plan to extract or filter by later.

In this how-to, we'll show you how to apply Date, Number, and Text filters to your spreadsheet. To make it easier for you to practice the filtering tasks we're about to describe, we've created a downloadable Excel workbook with all the data we use in this article. Just click the arrow or link below to start the download.

Using the Sort & Filters menu

1. Access the Violations table. Click the arrow beside the field column called Points. Notice the drop-down menu begins with Sort Smallest to Largest or Sort Largest to Smallest. Choose one of these sorting options and notice that Excel sorts the table using the field that your cursor resides on. For example, if your cursor is on the Points field column, Excel sorts the table by the Points field.

JD Sartain / PC World

Use the Sort + Filters menus to sort fields

2. In a small table like this, it's easy to quickly see how many Florida drivers have 3, 4, or 12 points. In reality, however, this table would likely have thousands of records and many different levels of points. Using the filters would be a much easier and more efficient way to determine how many (and which) drivers have Greater Than or Equal To 12 points.

Number Filters (one condition, one field)

1. Click the arrow beside the field column called Points again. Scroll down and select Number Filters. From the submenu that pops up, select Greater Than or Equal To.

2. When the Custom AutoFilter dialog window opens, notice the field name Points is displayed under the prompt that says Show Rows Where > Points >Is greater than or equal to (your original Logical Operator), which displays in the first Input box.

3. Click the down arrow on the right side of the Input box. Notice that all of the Logical Operators are listed in this drop-down menu, which you can re-select if you change your mind and want to use a different Logical Operator.

4. If you're satisfied with your original selection (Greater than or equal to), click OK and your table reappears with the records that match your filter only (in this case, only two records). If you had selected Greater than or equal to the number 4, then all records equal to 4 and above (through 12, on this table) would display.

5. To cancel this filter and view all the records in this table again, click the Points arrow again, then click Clear Filter From "Points" from the Sorting/Filters drop-down menu.

JD Sartain / PC World

From Number Filters, Select Greater Than Or Equal To

Note: Points is a numeric field; therefore, the Filters that are available on the Filters submenu (which says Number Filters) are Logical Operators that work with numbers: Equals, Does Not Equal, Greater Than, and so forth. The options for other filters are different yet again. For Dates, it's Tomorrow, Last Week, Next Quarter, etc. Text filters include Begins With, Contains, Does Not Contain, etc. Some filters overlap between the Text fields and the Number fields, but the Date filters are unique.

6. You can also use the data under the Search Input box to extract records that are equal to the values (or text items or dates) in the table. Click Points, then under the Search Input box, uncheck the check box that says Select All.

7. Check the values you want displayed for your query or report (such as 4 and 12), then click OK.

8. Excel removes all records from this view that do not match your filtered query, which means everything is gone except the drivers who have 4 points or 12 points.

JD Sartain / PC World

Filter out all Florida drivers with 4 points or 12 points

Text Filters (two conditions, multiple fields)

1. Click the arrow beside the field column called Violations (a Text field). Scroll down and select Text Filters. From the submenu that pops up, notice the Text filters that are available: Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom Filter.

2. When the Custom AutoFilter dialog window opens, notice the field name Violations is displayed under the prompt that says Show Rows Where—Violation Type: Equals (displays in the first Input box). Click the arrow on the right side of the Input box beside the Equals Input box) and choose a Violation Type from the drop-down list.

3. Follow the instructions above (4-8 under Number Filters) to extract the driver's license data you need for your report. For example, all drivers with Violation Types equal to "Running a Red Light," or "Seat Belt Violation," or "Speeding."

4. What if you want to extract all the records that show DUI's and/or Reckless Driving? Choose Equals in the top left Input box and then select DUI from the top right Input box. Then click the OR circle (tick mark). Next choose Equals (again) in the bottom left Input box and select Reckless Driving from the bottom right Input box.

Important note: Why choose OR instead of AND? OR means any record with DUI OR any record with Reckless Driving. AND means records that contain both of these violations. In this database/table, none of the individual records contain multiple violations, although some of the drivers do.

JD Sartain / PC World

Use Text filters to extract specific text data

Date Filters (using custom conditions)

Excel's Date filters are very comprehensive and allow you to extract individual or multiple dates by day, week, month, year, quarter, or year-to-date, plus dates within ranges. You can also use negative logic, such as all dates that do not equal 2017, or all dates that do not equal March, April, and May.

1. Select the Violation Date field. Click the arrow beside the Search box under Date Filters (on the Sort/Filters submenu) and select YEAR from the small dropdown menu.

7. Excel displays all records not equal to (Does Not Equal) March, April, and May.

JD Sartain / PC World

Use negative logic to show all dates NOT EQUAL to something

8. The long list of Date filters includes: Equals, Before, After, Between, Tomorrow, Today, Yesterday, Next Week, This Week, Last Week, Next Month, This Month, Last Month, Next Quarter, This Quarter, Last Quarter, Next Year, This Year, Last Year, Year-to-Date, All Dates in a Period (with four quarters and 12 months), plus Custom Filters.

9. Most of these filters are a one-step/one-click process: You click a filter, and the results appear instantly.

JD Sartain / PC World

Select from a long list of Date Filters

10. If you choose Equals, Before, After, Between, or Custom Filters, the Custom AutoFilter dialog window opens and prompts you for additional information such as Equals to, Before, or After a specific date; or Between two dates; or to create a Custom Filter.

11. Select Between and in the Custom AutoFilter dialog window, notice that Excel has already added the conditions for this filter. All you have to do is select the dates from the drop-down lists, such as:

Is After or Equal To: 2/22/2017

Is Before or Equal To: 5/1/2017

And then click OK. Excel displays only the dates you requested.

NOTE: For this Filter, AND is the correct Boolean operator because you want ALL dates between (A) AND (B). If you select OR, Excel displays the entire database table because ALL the dates are Before, After, OR Equal To the dates you selected.

PCW Evaluation Team

I need power and lots of it. As a Front End Web developer anything less just won’t cut it which is why the MSI GT75 is an outstanding laptop for me. It’s a sleek and futuristic looking, high quality, beast that has a touch of sci-fi flare about it.

I would recommend this device for families and small businesses who want one safe place to store all their important digital content and a way to easily share it with friends, family, business partners, or customers.

Reproduction in whole or in part in any form or medium without express written permission of IDG Communications is prohibited. Copyright 2013 IDG Communications.
ABN 14 001 592 650. All rights reserved.