Using filters

- If you work with lists of data, there are certainly times when you are saying, "I don't need to see all this for the moment, "I want to focus on just the people "in a certain department, maybe marketing." Or at another time, "I just want to see "the full-time people here." Or at another time, "Let's focus on the people "with the best or the worst job rating." Any number of different reasons for not wanting to see all the data and focus on just the data you need to see, the feature is called Filter. You can, from the Home tab, far right, click Sort & Filter, and then Filter to get to it that way.

You could also jump over to the Data tab, and you'll see filter there as well. As with other data management tools, make sure that your data has no empty rows or columns within it. If you work with the data frequently, that's not a concern. Simply click on one of the cells within the data. Here is Filter right here, click it. And we see filter arrows at the top of each column. And it's almost self-explanatory, an easy feature to use. Let's click the drop arrow for Department.

We get an alphabetical list of all the departments. We might want to view one or two of these at the same time. Let's unselect them all and begin by maybe clicking one of these. How about Human Resources? Click that, click OK. We're only seeing those people. Lower left-hand corner tells us we're seeing nine of them out of 741, and the blue row numbers we see to the left accentuate the idea we're only viewing some of the rows. We want to get back the data? Clear, just to the right of Filter, brings back all the data.

Now sometimes, we might be looking at multiple columns at the same time. In other words, multiple filtering. Let's say we want to look at one of our larger departments, Manufacturing. Click the drop arrow. Unselect them all, and let's say we want to look at Manufacturing as well as Manufacturing Admin related departments. Click OK. 158 records. Now it's important because we're going to be considering some of the payroll changes we're going to be making, let's just focus on our full-time people for a while within this department.

Click the drop arrow here, unselect all of them and check Full Time. So instead of 158 records, we're now seeing 85. And for another reason, maybe at the same time or maybe later, we might be saying, "Let's focus on just our top performers, "those with the job rating of five." Click the drop arrow, unselect them all, select only 5. Instead of 85 records, now we're seeing 21 records. We see this in the lower left-hand corner.

Any time you see the blue row numbers, that means we're not seeing all the rows. Of course, that's what filtering really means. We're hiding rows for the data we do not want to see. And at different times, you can imagine putting together filtering from different columns. The columns that currently are in play, or the ones we're filtering as active, we can see a slightly different icon, it looks like a filter. Column C and D and I at the moment. We can again clear if we wish, come back here, bring back all the data.

Now Department is a text field, and when we click the drop arrow here, we see Text Filters. Equals, Does Not Equal, Begins With, Ends With. We can use this from time to time also. The Compensation column, column J or column H, either one, is a numeric column, and there we see Number Filters. And so we've got some different choices out there. Some are the same, but some are also different here. Greater Than, Less Than or Between. So we might choose between and a different dialog pops up.

Maybe we only want to focus on those people whose salaries are in the 60,000 to 70,000 range. So greater than or equal to 60,000. Less than or equal to? Well maybe. Or how about just less than? Another choice. Less than 70,000. Another way of saying this is all those salaries that begin with six, between 60,000 and 70,000, but not 70,000 exactly. Less than. Click OK, and we're seeing 119 records here. They're not in any particular order here, so we also have an option with the arrow here to sort largest to smallest.

Now this will only use the visible rows to move the data up and down. In other words, the rows will be shuffled up and down, but the row numbers to the left, rows five, 11, 12, 15, will stay the same but different data will be in them. Same row numbers, but now we've sorted the data, just the visible data to be in descending order. So sometimes that's necessary as well too. Back to the Clear button again. There's also a date column in here. As we click the drop arrow for Date, recognize Date Filters.

A ton of choices here. Now if this is historical data, we might want to see data from last week, next month, this month, last month, so on. Not next month if it's financial data, but if it's prediction data, of course next month. We see lots of choices in here. Quarters. Looking forward, looking backward, depending upon the nature of the data. And don't overlook, there's an arrow down here, All Dates in the Period. We can even narrow them down by month. Imagine how valuable this would be for analyzing sales data.

And we can do it by quarter as well. So lots of choices here. And we can do other things with this feature too. In Column A over here, we can narrow down a list. Now I'm looking for somebody named Rick. Possibly I could use text filters, or we choose here. It's actually at the end, ends with Rick? Possibly we could do that. We can also click in the Search panel right here. As I type R-I, the list below is being filtered. I only see entries were R and I appear.

C, the list has gotten smaller. K? It's even smaller. I could click OK now or that's it. There it is. But notice, all those contain R-I-C-K in different locations. The list is short enough, we wouldn't worry about it. You might have seen another movie called Find and Replace, where we did something similar with this. Filtering often is faster. We see these. Now we might want to print this or copy this if we highlight this and paste it, say, on an empty sheet. Automatically, we will get only the visible data.

We don't worry about having to select the visible data. It happens automatically. To get our data back again, Clear. Lots of choices here. Generally easy to use. We're doing no damage, we're simply hiding data as we use the Filter capability found on the Data tab in the ribbon.