How to use the Data Aufofilter in Excel to filter out records or identify key records based on a single or multiple criteria.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Data Autofilter in Excel
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,
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
Otherwise there’s a quick way,
you click on Data
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,
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,
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