Filter Data in Excel

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

Excel Chapter 6 of 24: Filter Data in Excel

NOTE: If Excel recognizes your set of data as a database you can sort the data

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.

Excel database

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.

Excel autofilter setting

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.

Excel autofilter selection

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

Excel autofilter arrows

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 deactivate the filter go back to the drop-down list of the " Name" field and select " (All)" . IF you have activated filters in many filed's 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:

Example Excel auto filters

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 filtered 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 conditions. 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*r" , " Equals to...P**er" and " Equals to...P?r" will filter the records for " Peter" .

Excel Auto Filter

 

Next Chapter: Reporting with Excel

Excel Table of Contents
or use the site quick links below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Filter Data in Excel