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

No matter how big your spreadsheet database is, filters can find what you need.

Credit: Rob Schultz

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.

download
This is a workbook with multiple spreadsheets whose content can be used to practice Excel tasks in relational databases, reports, and pivot tables. JD Sartain

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.

01 use sort filters menus to sort fields 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.

02 from number filters select greater than or equal to 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.

03 filter out all florida drivers with 4 and 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.

04 use text filters to extract specific text data 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.

2. Uncheck Select All, then recheck 2016 and 2015 and click OK.

3. Excel removes all years not equal to (Does Not Equal) 2017.

4. Click Clear Filter From Violation Date on the Sort/Filters submenu.

5. On same menu, select MONTH from the Search box dropdown menu.

6. Click the plus sign beside 2017, 2016, and 2015, then uncheck March, April, and May. Then uncheck Blanks and click OK.

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

05 use negative logic for all dates not equal to something 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.

06 select from a long list of date filters 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.

07 use the date filter between to extract a range of dates JD Sartain / PC World

Use the Date Filter BETWEEN to extract a range of dates

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags ExcelOffice 2013Office 2016

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

JD Sartain

PC World (US online)
Show Comments

Father’s Day Gift Guide

Brand Post

Most Popular Reviews

Latest Articles

Resources

PCW Evaluation Team

Luke Hill

MSI GT75 TITAN

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.

Emily Tyson

MSI GE63 Raider

If you’re looking to invest in your next work horse laptop for work or home use, you can’t go wrong with the MSI GE63.

Laura Johnston

MSI GS65 Stealth Thin

If you can afford the price tag, it is well worth the money. It out performs any other laptop I have tried for gaming, and the transportable design and incredible display also make it ideal for work.

Andrew Teoh

Brother MFC-L9570CDW Multifunction Printer

Touch screen visibility and operation was great and easy to navigate. Each menu and sub-menu was in an understandable order and category

Louise Coady

Brother MFC-L9570CDW Multifunction Printer

The printer was convenient, produced clear and vibrant images and was very easy to use

Edwina Hargreaves

WD My Cloud Home

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.

Featured Content

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?