Join Dennis Taylor for an in-depth discussion in this video Using text filters, part of Excel 2013: Managing and Analyzing Data.
When you apply a filter to a list of data, as I'm doing here on this worksheet call Text Filter. Excel recognizes the kind of data that you have in a column. For example, if I click the drop arrow for Department. You will see the term text filters. This is a numeric field that's going to say numeric filters. And we see these choices off to the right, related to text type entries. So if for example, ifwe wanted to find departments that might have the word training in them. And let's say we don't necessarily know if there's one or more of those.
We could, using one of these options, and in this case it would be contains. This brings up the custom auto filter all set up for us we'll just type in train. And if I'm thinking maybe train, training, possibly plural, I might just put in the word train, see what happens. Click OK. And it looks like we've got training appearing in two different names here. That's certainly one use of this. Take another example, let's bring back all the data by clicking the Clear button just to the right of Filter.
And consider another option here under Text Filters. Sometimes we know that we want all departments here that do not contain a certain entry. So if we wanted to see the non-training departments, now we've already figured out let's say that we see training here. Let's put in training here, and now we will get a list of all the departments that do not contain the word training. I think you can imagine different kinds of situations here. It looks, as we look down the left hand side, like most of the data is still there, but because the row numbers are blue, that suggests that some information is not being shown here.
So as we look at this list, we are not seeing the word training anywhere. And the lower left corner tells us that we're seeing 708 of 741 records. So, it works that way too. Let's clear the data again, and take a look at Column A. It has employee names. Imagine we're trying to track someone down. This person is named Rick. So we'll click the arrow here. There are actually two ways to do this with a text entry. First way, is the way I think most people would drift toward as we look at Text Filters.
Now if we're looking for the name Rick, in the background the names are last name, first name, so we can't say equals Rick. That would mean the entire cell would have to be R-I-C-K. It doesn't begin with Rick, it possibly ends with Rick. How about ends with, Rick, R-I-C-K. And okay. And we have our list. And we probably are getting a few more names than we thought. If the list is short enough it probably wouldn't bother us. But with, much, much more data, possibly, we didn't quite narrow this down the way we might have wanted.
So it's Rick and Patrick and, of course, these all do end in R-I-C-K. And maybe that's the Rick we were looking for. Quickly pick up on the idea of how these work. If we had used contains, that probably would've been almost as good. Text Filters, how' bout contains? And if we choose Rick now, we'll get a few more entries, but not enough to worry about. And recognize here that it does contain Rick. And even in the name Strickland we do see r-i-c-k within that. Now here's a completely different approach.
I'll bring back all the data. And sometimes this might be a little better. I wouldn't say it's better in this case. So clicking on the drop arrow again for employee name, there's a search panel right here. Now as I type R, it's not obvious but all the information below that consists of names that contain the letter R. And then as I add I, all of those contain an RI and then a C. And now the list is somewhat shorter. But, all the names you see below contain R-I-C and then K.
Now, normally if you use this approach you're not pausing each time but, had we typed R-I-C-K this way, this is in effect the same as using the Text Filter that contains. But it's sometimes a faster way of getting to the data. And so in a real life situation, I could click the drop arrow, type r-i-c-k, and enter. And there's our list. So that could be a tad faster at times. So using Text Filters automatically sensed by Excel when you're using filtering, is a great tool for narrowing down certain kinds of lists, like we're seeing here.
- Multiple key sorting
- Filtering single and multiple columns
- 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
- Converting lists to tables