from Pierre Leclerc at http://www.excel-vba.com





Excel Lesson 5 of 30: Filtering Data in Excel

If Excel recognizes your set of data as a database
You can do this

Filtering data is one of the most powerful tools to analyze data. It is easy to learn and to use.

What is filtering data?

Let's say that you have a very simple database with 5 fields (date, name, product, quantity and amount). For analytical purposes you don't want to see the records (rows) for ALL the clients but only for one.

That is when you start using filters.

How hard is this? Two clicks on the button.

Autofilter

Discover the AUTOFILTER. Place the cursor anywhere within the database in your spreadsheet and go to the menu "Data/Filter/AutoFilter" and small arrows appear in the title cells of each field. Click again and they disappear.

When you click on the small arrow in the field (Name) you see a drop-down list containing all the names in the field. Notice that the names are in alphabetical order. At this point you see all the names in the database and you can find names that are incorrectly spelled. This first step allows you to clean your database. If you use the filter on "Amounts" they are shown to you in ascending order in the drop-down list and you can identify numbers that could be wrong. SO without even activating the filters you can analyze the quality of the data and make any correction before you start filtering the database.

Let's say you select "Peter" in the drop-down list of the filter. Here is what you see:

Notice that the small arrow in the field "Name" has turned blue and that the row numbers are also blue. This means that a filter is active.

To deactiveate the filter go back to the drop-down list of the "Name" field and select "(All)". IF you have activated filters in many fileds you can also go to the menu ""Data/Filter/Show All".

You now know how to use a filter in one field using a single criteria. You can also activate a filter in many different fields or use more than one criteria in a single field. You can use arguments for numerical values like "Equal to", "Greater than", Smaller than". With alphabetical values you can use arguments like "Contains", "Does not contain", "Begins with" and others.

We will see all these filtering techniques but let's start with filtering a database using a single criteria in many fields.

Single Criteria Multiple Fields

You might want to see only the records (rows) showing "Peter" buying "Bikes". To do so you will select "Peter" in the drop-down list of "Name" and "BIke in the drop-down list of "Product". You should then see only two records.

Copy/Paste Filtered Data

If you want to copy/paste this set of records on another sheet click anywhere in the database, go to the menu "Edit/Go to/Special/Current Region". Copy and paste on the other sheet.

Single Field Multiple Criteria

You can use up to two criteria in a single field ("Peter" and "Mary"). In the drop-down list of the "Name" field choose "Custom". The following dialog window appears:

There are 4 text boxes and two radio buttons in the window. The first text box is set at "Equal to". Select "Peter" in the second one, select the "Or" radio button, select "Equal to" in the third text box and "Mary" in the last one. Click on "OK".

Remember that although you want "Peter" AND "Mary" you must use the OR argument. If you ask for "Peter" AND "Mary" no records will be filterd because there are no single record where the "Name" field is "Peter" AND "Mary"

If you want to use more than 2 criteria you will need to use the "Advanced Filters".

Other Custom Filters

In two of the text boxes of the "Custom" autofilter window you can select any of the 12 consitions. You will use some with numerical values and others with text values. You can use the question mark or the asterisk as wild characters or series of characters. For example all three "Equals to...Pet*er", "Equals to...Pe**er"and "Equals to...P?r" will filter the records for "Peter".