Transcripts for the above video clip:

AUTOFILTER BASICS

The Data AutoFilter allows you to find and work with subsets of a database, or a list in a quick and easy manner. It is useful in many situations, but extremely useful for data cleanup operations.

In this clip you will learn how to find and activate the feature. And use the feature to find the data that matches your criteria

Here is a typical spread where the data AutoFilter can be used. Column A we have the Sales Persons code, we have the Date on which sales where achieved, and we have the amount of sales for Product A, B and C generated for that person.
To activate the AutoFilter, you need to

• highlight the list that you want to apply the AutoFilter to,
• click on Data,
• Filter,
• and then AutoFilter, you will notice the little arrows appear in the top row,
• when you click on them, what it gives you is a number of options,
• in its simplest form, you can click on one of these,
• Lets say we’ll look at Sale Person 1 and you’ll be given the subset of that list, showing each time this SP001 is shown.
• To remove that you can go back and say
• show me All
• You can go to the date field
• Say well lets see what happened on the 1st of September, click on it
• Only the 1st of September results will be shown
• And again if you want you, can click and say All

It’s possible to actually combine all your criteria. So we can say

• we want Sale Person 002,
• and we’d like to know what happened on the 3rd of September, and only that will be shown

You can if you want to go back and say

• All, All

Otherwise there’s a quick way,

• you click on Data
• Filter
• There’s a show All button,
• and that will show all the data again

You will have noticed that when you click on the arrow, there are one or two other options that you can use.

• the Top 10 option when you click on it gives you this menu
• You can now say do you want to see the top or the bottom. We are happy with the top
• How many items do you want to see?
• Or it could be items or percent. In this case we are happy with items
• and when you click ok

It shows you the top ten items for product a sales, and in this way you can pick up some trends with regards to the sales person or maybe to the dates of sales

• If we go back to the arrow and just show All,

Another option is to

• click on the Custom button, and this allows to customize what sales you are looking for, as you can see if you click here there’s a number of options, so we can ask for all sales;
• that are equal to a number, or
• that do not equal a number,
• can be greater than, or
• greater than and equal to
• less than, and,
• less than and equal to

For text it is quite useful to look at words that

• begin with something or
• do not begin with it,
• end with it or
• don’t end with it, or
• maybe they contain letters or certain key words, or
• do not contain it

For the purposes of this example, lets look for product A sales between 600 and 700, so we can say the Product A sales;

• must be greater than, 600 and
• they must be less than 700
• and when we click ok, only the items that match those criteria will be shown.

You could of course have used the OR function, so

• if we go back to the All situation,
• we customize,
• lets say we want to look for the extremes, so we want to say if this number is greater than lets say 900, OR
• if it is less than 100, show me that list
• and you’ll see it brings up the list where the numbers are greater than 900 or less that 100.
• If we go back to the showing All the data,
• and we scroll down a bit, you’ll see that we have a blank row here, and this example we’ve go only one blank row, but in other situations you may have many blank rows, what happens if you want to bring those up maybe to delete them,
• you can click on one of the arrows,
• if you go right to the bottom it gives you option to bring up all the Blanks, all the NonBlanks.
• So If you click on Blanks all the banks will be shown,
• and you can quickly and easily highlight that row,
• delete it
• and when you go back to the All situation, you’ll see that your blanks have been removed. This is a very nice way of cleaning up your data

Just a word of warning, when you click on these arrows you’ll see that Excel has gone through the list and itemized the unique value so you can search on them. However it only goes through the first 1000 rows or so for this section. So if your unique item is significantly down the list or if it’s a very large list, you may find that its not listed here, so what you need to do there is;

• go to the Custom button
• and search for it where it, equals that number,
• and Excel will work through the entire list and find all the rows that match that criteria.

If you want to remove the AutoFilter, it’s as simple as;

• going to Data,
• clicking on Filter,
• and switching off the AutoFilter and you’ll see the arrows will disappear