Display large values

- [Instructor] On this HR list worksheet, we might want to see just the department research center. We see it cell C4. Now there are a couple ways to get to this and the most direct way is by way of the filter. In Excel, we can go to the data tab, choose Filter. That puts arrows at the top of every column. I'll click the department arrow right here, and we could unselect them all and choose, after scrolling downward here, research center. Click okay, and we're only seeing those.

Just five people. But there can be a faster way. And the way we get to this faster way is a bit unusual, so let me remove the filter. And what I'm going to use is a feature that allows us to click a cell, for example, research center, and then click a filter button and immediately have just those rows appear. It's referred to as filter by selection. And what we need to do is to add a new button to the quick access toolbar. Most people keep the quick access toolbar above the ribbon.

On the right side of the quick access toolbar, you'll see a drop arrow. None of the choices we want is here. We choose More Commands. Now, because this is a data tab like option, one approach could be Choose Commands From. Click the drop arrow up here and let's go to the Data tab. And that's where we see Filter, but what we're looking for here is, and this is not something you would know intuitively, we're looking for Auto Filter. That's a term out of the past.

We used to use that term in Excel. It was called Auto Filter. We don't see it here. Okay, where can we find it? Back to this arrow here. Either Commands Not in the Ribbon or All Commands. This is a shorter list, the first one. Commands Not in the Ribbon, let's go here and scroll down to Auto Filter. Well, there it is right there, Auto Filter. Nothing is said about it here. Nothing is reminding us of what this does. Add, it's now on the quick access toolbar, click okay.

I'm going to click on research center and there's that button, the quick access toolbar. As I slide over it, it says Auto Filter. That's all, it doesn't say anything else. I'm going to click it. What happens? We're only seeing research center. That's a great tool and it puts filter arrows in all the column headings so it activates the filter and gives us the data we want. Let's use the clear button just to the right. We could try this again. Here's manufacturing, much bigger group. Not the Filter button we want to click, but that Auto Filter button up above.

There we are, we're just viewing the manufacturing people. What if we happened to click on full time off to the right? Click full time, and then that Auto Filter button. We're just seeing full time people, but since we already used that to just show the manufacturing people, now by clicking those two buttons, and of course we could have done that in succession relatively quickly. Now we're seeing only the full time people in manufacturing. Lower left corner reminds us how many people there are, 80 out of the 741 records in this list.

So that's a valuable tool. And I wish it were part of standard Excel usage, but we can easily add this button. You don't need to do it more than once. It can stay up there indefinitely if you want it to. The name is called Auto Filter. The feature actually is described as filter by selection. And just one more quick example here. I'll clear this here. I'm looking at all the people in the west building in this campus-like setting. So I'll click west, click Auto Filter. And we're only seeing the people in that building. It's easy to use and easy to apply.