“AutoFilter”

“AutoFilter” is a handy tool that allows us to “Filter” data from a “List”.

To apply “AutoFilter”, we must have column headings.

If you have created a “List” in Excel 2003, the “AutoFilter” is automatically made available.

But even in earlier versions of Excel it is easy to filter data.

Select a cell in your “Worksheet”.

Choose “Data” from the menu bar, select “Filter” and choose “AutoFilter”.

An arrow will be displayed next to our column titles.

Clicking on one of these arrows gives us a dropdown menu with the following choices:

– (“All”)
– (“Top 10 …”)
– (“Custom”)
And the full “List” of the different values from that “Field’.

When you use Excel 2003, you also choice of sorting the “List” in “Sort Ascending” or “Sort Descending” order.

When we select one of these values, Excel will “List” only those values which satisfy our “Filter” criterion on the records.

All other records are hidden, not removed.

You can see a “Field” where it is filtered. The arrow next to the title column shows blue.

We can use multiple filters on a “List”.

If you want to see all records again, choose (“All”) in the dropdown menu, and all your records will be displayed again.

The use of “Top 10 AutoFilter”

The “Top 10 AutoFilter” can only be used in columns of numbers or percentages, not text.

So choose a column from the data which consists of numbers or percentages.

Click on the arrow beside the column title and choose (“Top 10 …”).

This opens the “Top 10 AutoFilter” dialog:

In the first box, or we choose “Top” or “Bottom”.

In the later we choose a number i.e. the number of “Records” we want to see.

And in the third box we choose “Items” or “Percent”.

Click OK when you are satisfied with the settings.

The use of a “Custom AutoFilter” (“Custom AutoFilter”)

We can also use a modified filter, by making use of comparison operators.

By making use of the “Custom AutoFilter”, we filter data on the criteria that we have established ourselves.

For “Custom AutoFilter” option we select “Custom. .” from the dropdown menu.

This shows us the “Custom AutoFilter” dialog:

In the left box, we give our comparison operator from the several choices we have: greater than, less than, equal to, etc…

In the right box, we can either enter a value or choose a value from the dropdown menu.

We can enter a second criteria if we wish.

Select “And” or “Or”, depending on whether the “Records” must satisfy both criteria, or any one of them.

We can also use “Wildcards” (“Wildcards“) in the fields where we have to give a value.

‘?’ stands for one character.
“*” stands for one or more than one character.

When you have finished your criteria, just click on OK.

We see only the “Records” with sales in January, where the values lie between 10,000 and 12,000 Euros.

Turn “AutoFilter” off.

To turn off “AutoFilter”, we again choose “Data” in our menu bar, select “Filter” and click “AutoFilter”.

Then “AutoFilter” becomes disabled.

This is the same for every version of Excel.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats