Join Dennis Taylor for an in-depth discussion in this video Display large values, part of Excel Tips Weekly.
- [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.
Author
Updated
1/19/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 14m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Display large values