Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,971 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
Skill Level Appropriate for all
Excel's filtering feature covers lots of ground and it works in most situations for most filtering. But there is another tool called an Advanced Filter. Let's take a look at why we might need this kind of a filter. This is a little involved, a little more cumbersome setting up, and it does involve something called a criteria range. What I am about to do is not at all obvious, so now I'll have to explain a little bit ahead of time and a little bit later. When you see a criteria range, it is usually in most examples that you might see in an Excel book on Advanced Filtering above the data.
So what I am about to do here with this data is to highlight the first six rows and then right-click and Insert, and I am going to be copying the title row here, simply by dragging across these titles and I am going to drag this upward with the Ctrl key held down, put these into row 1. You might see in some examples a criteria range located to the right of the data. It could be there. It could be under the data, but there are convenient reasons for putting it here. Again, not all of this should fall into place immediately. Here's an idea.
Suppose looking at this list below here we need to pull out a list consisting of those records where either the years of service is 20 or more or the salary is 80 or more. You cannot do that with the regular standard filter in Excel. We can do this with an Advanced Filter. What I am about to do now isn't exactly obvious, but in cell I2 here, I am going to put-in the formula >=20 and then and certainly not obvious, in column K but in one row below this, I'm going to put in >=80000.
When we start to use the Advanced Filter, it will prompt us for a criteria range and this will be the criteria range. If you know Access, you maybe familiar with what's called a Query Grid and you would have some understanding of this layout. In no way is it intuitive but what it does mean is years of service >=20 or salary >=80000. So the active cell will be within the data, and we're going to click on the Advanced Filter located on the Data tab.
And in this dialog box, nearly every time the list range Excel figures out just perfectly. That's the entire list of data here. You do have the option of copying the results elsewhere. We don't need to do that here. Here is the indicator for criteria range, and rather than collapsing the dialog box here, let's simply scroll over to the cells in question and highlight these cells here. This is going to be the criteria range. There is a Unique records option only, which we won't use yet and we won't need it in this example.
But as we click OK, watch the list below row 7 change. And the indicator in the lower left-hand corner says 125 of the records meet our criteria. So what are we seeing here? Here is a situation where the years are 20 or more. Next record, high salary, next record, high years, and so on. In some cases potentially both criteria will be met, but based on the criteria range, if either one of these is true, we will see it appear in the list.
And there it is, there is the entire list. We might make more sense out of this a little more readily if we sort on one of the two fields. We could easily do that with the active cell in either location. Maybe we'll do a ZA sort of the toolbar this way. We see the data here. Here are all the people who make more than 80,000. Some of them do have 20 years, most of them don't, and so on. There is the entire list. an unusual construction, an unusual way to set this up. Now, could this criteria range have been to the right of the data? Yes, it could have been, but in performing the filter, because rows are hidden, we can see that row 8 is hidden and row 10 is hidden, then we couldn't quickly review and look at the criteria range to make sure it was working properly.
That's why more often than not when you see a depiction of an advanced filter in an Excel book, it's likely to have been setup above the data like this. So there are other situations as well, and it is unusual. The Advanced Filter usually covers situations where you just cannot get to these using the standard filter.