Join Dennis Taylor for an in-depth discussion in this video Using filters, part of Excel Essential Training (Office 365).
- [Instructor] Any time you'd like to hide rows within a list and focus on just the rows of most importance for the moment, you can use a feature called Filter. It's widely used and generally pretty straightforward and easy. We've got a list here on the worksheet called Filter. It's got about 700 rows or so, in the Workbook 10 Data Management Features. The Filter option is found on the right side of the Home tab under Sort Filter, you could start there. There it is, Filter, or on the Data tab. Now, before doing this, you want to make sure that the list you're working with has no empty rows or columns within it, click anywhere within the list, activate Filter.
We see Filter arrows at the top of each column. I'm only interested in seeing the full time people here. I'll click the arrow for Status, this is a text field, notice that as we slide down we see the term Text Filters, and you possibly could imagine using some of these at different times. In the example here, all we want to do is make sure that only full time has a check on it. How about unselecting all, click Full Time, click OK. Lower left corner tells us, 393 of 741 records found. Now sometimes you'll do filtering on multiple columns.
Of all these full time people here, let's say we only want to focus on those who have a job rating of 5. Column I, now, that's a Number Filter. At different times we could consider using some of these, but say, in this case, all we care about, unselect all, 5, OK, there we are. Only 92 records, and not that we have to narrow it further, but along with this we could say, let's see, out of all these people here, how many of our full time people with a job rating of 5 have been here over 10 years. Number filters > Greater Than or Greater Than or Equal To, and of course, there is a difference, about 10 or more if we say Greater Than or Equal To, it's going to be 10 or more, there we are.
That list is down to 39. At any given time, when you see a list like this, if you were to copy and paste this, Excel would only pick up the visible data, so right now, if I were to copy and go to a different worksheet and paste, this is the only data we would see. At different times, you no longer want the list to be filtered, but yet, you want to keep the filter arrows, the button just to the right is clear. If we're working across a date field like column E, here as we click the drop arrow, we see the term Date Filters. Imagine if this were historical data of a transaction type nature, we might want to view Yesterday's data, Last Week's data, Last Month, Last Quarter, Last Year.
If it's predictive data, we can go in the opposite direction. All the way down to the bottom, All the Dates in the Period. If we want to make a little list here of all the people having an anniversary month in October, we'd choose October and here are all the people hired in October. I think you can imagine using a field like this, too, in other kinds of scenarios with other kinds of data, so all of our October data is visible, 58 out of 741 records. It's a relatively easy feature to use. If we no longer want filtering at all for the moment, we'll simply click Filter, and any filtering that's in place immediately disappears as does those filter arrows at the top of each column.
Different times we want to hide the rows we don't want to see, the Filter feature gets us there quickly.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.