Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, Dennis Taylor shares easy-to-use database commands and methods for maintaining an Excel database. The course covers sorting, adding subtotals, auto-filtering, and using the Excel Advanced Filter feature and specialized database functions.
When you have a list of data, large or small, there certainly are going to be times when you say to yourself, "I don't want to see all of this data; I just want to see certain portions of it." For example, in this list here, maybe you just want to view the people that are in the Quality Control department or maybe you want to focus on the manufacturing people, or maybe there are two or three departments whose names you'd like to see. Along with simple filtering, we also can include different columns in play. We maybe only want to focus on the full-time people.
Now possibly, you might have converted data into a table. I have taken a copy of this data and displayed it as a table. It's in the worksheet to the right called Employees-Table. If you have used this feature, and more and more people are starting to use this because they like the easy color applications you can use and other features, it automatically applies or gives you filter arrows as we see here. I'm going to be using filtering on another table, the one we first saw, to start the process from the beginning. But everything we do with a filter there, certainly can be done in a table like this as we'll see as we go back to Employees Sheet.
The data doesn't have to be in any particular order. We do want to make sure or let's say that we have made sure in this case, there are no empty rows within the data and as I scroll to the right here, no empty columns within columns A through L. So we want some control over this to see just the data we want. The active cell can be anywhere. Go to the Data tab, click the Filter button, and immediately we see drop rrows for every field. Let's focus on department. Click the drop arrow here. If it's only one department we need to see here or want to see at any given time, first step unselect all of these.
Now if you have worked with this feature in the past, you'll notice this is a huge improvement introduced in Excel 2007. In prior versions trying to see more than two departments was not easily handled. Now it is. We might want to focus on just those people in training type departments. here's Admin Training, here's Executive Education. We have another one down here, Professional Training Group. Maybe there are some more, but there's three of them. Let's click OK and we are only seeing people in these departments.
Now, notice two big visual changes on the screen. The row numbers are blue, and of course they're not all there because not everybody is in one of these three departments. Also, in the lower left-hand corner it tells us that there were 52 of the 741 records that meet these criteria. It's either Admin Training, Executive Education, or Professional Training Group. Notice also, the filter arrows that we see on the other columns are still there, but the filter arrow for this particular column now actually has a filter icon right there to remind us that this particular column is at play in filtering.
And we can certainly apply filters to other columns at the same time. Let's suppose that now we also want to narrow the list by choosing just the full-time people in these departments. So the arrow for Status, let's unselect them all and choose Full Time. Click OK. And instead of 52 records, we're now seeing, as we look in the lower left-hand corner, 29 records and we might want to narrow it even further by just focusing on the top performers, those whose Job Rating-- and let's click the arrow for that.
Unselect them all. Job Ratings 4 and 5, the two top in our categories here. Click OK and instead of 29 records, we're now seeing 16. We might want to print this data. Before doing so, maybe it would make sense to sort. You can use regular sorting tools, if you're familiar with the AZ and the ZA buttons. You can certainly do that. But if we click the drop-arrow for department, notice here's Sorting here too. Perhaps it's a little more pertinent. What we're about to do here is sort this perhaps for printing or just visual reasons.
Only the data in the visible rows is going to be sorted as we click this option. You saw how we used different fields here. If we change our minds again, we might go in two different directions. One might be that we want to hold on to some of our filtering criteria and get rid of others. So if that's the case, we possibly would go back for example to Status and select them all, so that's no longer a criterion. But if we want to get rid of all the filters, we've two other options as well. If we'd like to keep the filter arrows here, we can simply click the Clear button and that will bring back all of the data while keeping the filter arrows in place.
You can see how that looks. I am going to press Ctrl+Z to undo, to go back. Suppose we like to get rid of all filtering for the moment, including the filter arrows, then we'll just click the Filter button. All the data will be returned and the filter arrows are gone. So it's a very easy for the most part feature to use, and in its simplest form as we saw it here on a single column or even expanding it into multiple columns, it's a great tool for paring down the data so you can focus on just what's important to you at the moment.
Find answers to the most frequently asked questions about Managing and Analyzing Data in Excel 2010.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.