Advanced Filter in Excel:: Really Basic Technique For Data Mining

Posted: April 26, 2013 by MaxFraudy in Excel
Tags: , ,

Most of us who have worked with data in MS Excel have had to use Filter option every now and then. It helps when sifting through huge amounts of data and when one wants to look at only part of it at a time. e.g. looking at one region’s data from the national database or filtering on values that are greater than (or smaller than) a particular number and so on.

While Filter is a mighty useful feature in itself, there are certain limitations. e.g. if one wishes to filter on two columns but want records that satisfy “either” column criteria then one cannot do so using simple filters. Neither, can one apply multiple combinations of criteria. This is where Advanced Filter option comes into picture. It can do most of what one would need from an Excel application. Advanced filtering, similar to filtering, displays a subset of the rows in a table or range of data.

Using Advanced Filtering

Step 1: Set up the database such that it satisfies all the conditions below:

  • Should have headings
  • No blank rows in between
  • Blank row at end & blank column at right

Step 2 – Set up the criteria range. The criteria range headings should match exactly with the headings of the database

Step 3 – Set up the extract range

  • If one plans to copy the data to another location, one can specify the columns that he wants to extract. By default, ALL columns are extracted.
  • Select the cell at the top left of the range for the extracted data.
  • Type the exact headings for the columns that you want to extract

Step 4 – Apply the filter

On launching the Advanced Filter (Data tab -> Sort & Filter section -> Advanced) a dialog box appears
adv
The criteria range is an area in a worksheet, separate from the range of data or Excel table, used to specify the criteria for the data to be displayed after the filter is applied to the table

 Options:

Filtering Unique Records : The list must contain a heading, or the first item may be duplicated in the results. Select if you want to extract unique records only

Understanding How Criteria Range Works

All criteria in one row is treated as AND while multiple rows are treated as OR

e.g.Criteria

AND OR
All criteria in one row Criteria in different rows
  • the customer must be MegaMart
  • AND the product must be Cookies
  • AND the total must be greater than 500
  • the customer must be MegaMart
  • OR the product must be Cookies
  • OR the total must be greater than 500
Using Wildcards in Criteria
The * Wildcard
  • Represents any number of characters in that position, including zero characters.
  • e.g. any customer whose name contains “mart”
The ? wildcard
  • Represents one characters in that position.
  • e.g. any 4-letter product that begins with c, and ends with ke.
  • Both Coke and Cake are in the filtered results.
The ~ wildcard
  • Lets you search for characters that are used as wildcards.
  • e.g. Good*Eats — any products that begins with Good and ends with Eats
  • To find only the product that is named Good*Eats, use a tilde character in front of the asterisk. — Good~*Eats

If you want to know more, or wish to see more examples of the application of Advanced Filters, leave a comment or email to us.

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s