Introduction to Excel filters and how to extract data

With the following article, I'll try to explain how to use number, text and date filters on your dataset to find exactly what you need, no matter how big your dataset is. .

Filters represent certain conditions you specify to your dataset and spreadsheet to extract only the specific information out from them.

For example, imagine a scenario where the IT manager wants to hire an applicant with a degree in Computer Science and experience using HTML, Java, and C++ languages. The IT manager will have to use the Human Resources spreadsheet database and based on logical operators to extract all the candidates who met the credentials.

To make it easier for you to practice the filtering tasks used in this article, I've created a downloadable Excel workbook with all the data you need.

In this Article, you will learn:

Using the Sort & Filters menu

Access the Violations worksheet and click the arrow by the field column name "Points".

The drop-down menu begins with Sort Smallest to Largest and Sort Largest to Smallest option. You can quickly sort columns by using this option.

In a small dataset like this, it's easy to determine how many points each Florida driver has just by looking at the raw table. In reality, this dataset would likely have thousands of records and by using the filters, we can determine much quicker in an efficient way, how many and which drivers have Greater Than or Equal To 12 points.

11

Types of Filters

There are three main types of filters, which all work a bit differently:

Using Number Filters

  1. Click the arrow by the field column called "Points". Select Number Filters and from the sub-menu choose Greater Than or Equal To.
  2. 11

  3. When the Custom AutoFilter dialog box opens, notice the field name structure of the first input box; Show Rows Where > Points > Is greater than or equal to > type your value. .
  4. 11

  5. To see all the available Logical Operators, click the down arrow on the right side of the Input box and all will be listed for you.
  6. 11

  7. After you set the logical operator and a value is entered into the selection box, click OK and your dataset will refresh now showing only the records that match your selection.
  8. To clear this filter and see all the records in the dataset, open the Sorting/Filters drop-down menu and click Clear Filter From Points.
  9. 11

Using Text Filters

  1. Open the filter drop-down menu beside the field column called "Violations Types" and Scroll down to select "Text Filters". From the sub-menu 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. 11

  3. Open Custom Filter and notice that the dialog box has the following structure: Show Rows Where > Violation Type > Equals > Input value. If you want to choose a different logical operator, Click the arrow on the right side of the Input box beside the Equals Input box and choose from the list.
  4. To see all the available Logical Operators, click the down arrow on the right side of the Input box and all will be listed for you.
  5. Click OK after you set the desired logical operator and a value in the selection box.
  6. To clear this filter and see all the records in the data-set, open the Sorting/Filters drop-down menu and click Clear Filter From Violation type.

Using OR/AND functions

In a situation where you want to use multiple logical operators or search for multiple values, you can use AND/OR option in the filter dialog box. For example, let's say we want to extract all the records that show DUI's and/or Reckless Driving?

  1. Choose Equals in the top left Input box and then select DUI from the top right Input box. .
  2. Click the OR circle (tick mark).
  3. Choose Equals (again) in the bottom left Input box and select Reckless Driving from the bottom right Input box.
  4. 11

Difference between OR and AND:

Using Date Filters

Date filters are quite extensive allowing you to use a wide range of options to extract your data: individual and multiple dates by day, week, month, year, quarter, or year-to-date, plus dates within ranges. There is a so-called negative logic option as well, which allows you to exclude dates that do not equal 2019, or all dates that do not equal certain month.

11

  1. Open the "Violate date" filter drop-down menu, beside the Search box, open the small drop-down menu and choose "Year" from the options.
  2. 11

  3. Uncheck Select All, then recheck 2018 and 2017 and click OK.
  4. Excel eliminates all records with years not equal to those years.
  5. 11

  6. Click Clear Filter From Violation Date filters sub-menu.
  7. While you are on the same menu, select "Month" from the Search box drop-down menu.
  8. Click the + plus sign beside 2018, 2017, and 2016, then uncheck March, April, May, and Blanks then click OK
  9. Excel will display all records which do not equal to March, April, and May.
  10. You can inspect the long list of Date filters such as 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.
  11. Majority of these filters are a one-click process. You just have to click a filter, and the result will appear.
  12. Filters which require more info from the user are Equals, Before, After, Between, or Custom Filters. After choosing them, a dialog box opens and prompts you for additional information such as Equals to, Before, or After a specific date or to create a Custom Filter.
  13. 11

  14. If you want to extract data recorded between two days, choose Between from Date Filters sub-menu and Excel will add the conditions for this filter in advance, you just have to select the dates from the drop-down lists and press OK
  15. 11




ABOUT

RECENT ARTICLE

SIGN UP

Stay up to date on the most popular data analysis and visualization products out there, and learn how to make data-driven decisions. All of our courses ensure quality, usability, and uniqueness of the content.

By creating an account you agree to our Terms & Privacy.