Excel worksheets typically contain a lot of data, usually much more than you can display on your monitor at one time. Sorting helps to group similar data together, but it still shows in the entire list. In this video, learn how to limit the data displayed
- [Instructor] Excel worksheets typically contain a lot of data, usually much more than you can display on your monitor at one time. Sorting helps group similar data points together, but it still shows the entire list. In this movie I will show you how to limit the data displayed in your worksheet by creating filters. My sample file is the Filter Workbook and you can find it in the chapter four folder of your exercise files collection. I said that Excel can contain a lot of data, but I'm working with a far simpler example here.
My goal in this movie is to create several different kinds of filters to limit the data that we see here. I'll start by creating what's called a selection filter. So let's say that I only want to see spring and summer. To create that filter I will click anywhere in column B, and when I say anywhere in column B I mean anywhere in the data list that contains the data. Then on the home tab of the ribbon I'll go to the sort and filter button at the far right, click it, and then click filter.
Doing so turns on the filter errors for my data list. I want to filter by season, so I will click that column's filter arrow. And you see that I have a number of tools, but the one that I'll concentrate on first is this series of check boxes here at the bottom. And you see that I have my values, fall, spring, summer, and winter. The first thing that I'll do in this case is to clear select all. That removes all the check marks. Then I want to see spring and summer, so I'll check spring and then I will check summer, and you see that in the background I have all of my data there.
And when I click in the display itself the sort and filter panel goes away and you see that I have values just for spring and summer. Ways that you can tell that a filter has been applied is that there is a filter icon on the filter arrow, it looks like a funnel, and also any displayed rows have a blue color for the number, and also hidden rows have green bars marking their locations.
So you can see that rows four and five, eight and nine, and 12 and 13 have all been hidden. If you want to clear a filter you can always press Command Z to undo the last action, but if it isn't the last action you can go to the filter arrow to which you applied the filter and then click clear filter and your values are back. Let's do it again, except this time let's create a rule filter. So I will go to the inquiries column, click its filter arrow.
And then I want to filter by creating a rule. So I will click the choose one control and there I have a list of conditions, equals, does not equal, and so on. Let's say that I only want to see seasons where there were values of 20,000 or greater. So I will select greater than or equal to and then in the box next to it I could select an existing value by clicking its down arrow or I can type one in, and in this case it'll be 20,000.
And press return. And you can see that the filter has been applied. And then I will click clear filter to bring all of the values back. Now let's say that I want to filter so that I only see seasons that start with the letter S. To do that I will click the season columns down arrow and then click in the search box, and I'll start typing with an S, and then you see that spring and summer are there.
If I only want to see summer for example I could type a U and I'm left with summer or if I press delete then they get spring and summer back. I'll go ahead and press return, there we go, and click away, and my filter is applied. And as always, if I want to undo the last action I can press Command Z, press Command Z again, press Command Z again, and as you can see I'm back to my full list.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.