Enhancing table filtering with slicers

- When you work with large amounts of data there's no question that filtering is a great tool for seeing just what you want to see. But one of its drawbacks is that you're not always reminded of what you are not seeing. In Excel 2010 a new feature's introduced, but it was only available in pivot tables, it's called a Slicer. It's now available if you've got data that's been converted into a table. Now not everybody's familiar with the table concept, but at any time you're working with lists of data, like this with no embedded subtotals, no empty rows, you can convert the data into a table.

You can actually do it four ways. Two quick keystroke shortcuts are ctrl + t, think of table, ctrl + l, possibly you're thinking of list. You can also get to the feature on the HOME tab, Format as Table, or possibly on the INSERT tab, Table. And most cases Excel immediately figures out the extent of your data, click OK and the data has been converted into a table. Now filter arrows are present and if we wanted to see just certain departments here we could do that.

We could click the arrow for Department here, unselect them all, maybe I'm interesting in seeing some training groups. Here's Admin Training, here's Executive Education, farther down the list there's another training group called Professional Training Group, there it is, click OK. So we've got those in place. Now we don't always need to know what we're not seeing, but we don't know or we're not seeing on the screen what we're not seeing. Some times that's important. If you know the data well, that's fine, but what about your audience? There are four kinds of Status.

We could do similar filtering there too. But let's say instead of straight filtering, and I'll simply here press on the DATA tab, Clear to clear the filter, let's introduce Slicers for this particular worksheet. INSERT tab, now if the data is not a table you won't be able to use the feature, Slicer, and let's use Slicers possibly for the fields that we're gonna be most interested in. And we could have many of them actually. I'm gonna choose Department, also Status, maybe Job Rating, and maybe we'll come back and choose others later time.

If there's a downside to Slicers it's that they do use up some screen space. So as we click OK here we begin to see this. Now I'm gonna resize these by dragging the lower right-hand corner, and as you do this at different times sometimes you make it too narrow, too wide, and it doesn't hurt to have a scroll bar necessarily, I prefer not to see them. So there are different Job Ratings. Status, there we go, something like this. Now there are about 25 departments here, so we can't really make this smaller, but because it is a Slicer, it has its own contextual tab here, we see the OPTIONS tab up here.

Off to the right Columns, maybe we'll put this into 3 Columns and then make it a bit wider. We don't necessarily have to see the entire name, we probably wanna see enough of it to make sense out of it though. But this is a slight downside because it does take up screen space. So we can move these around a little bit. Let's imagine this idea, we only want to see Full Time people, let's click Full Time, our list only shows Full Time. Lower left corner reminds us we're seeing 387 out of the 726 actual entries there. If we wanted to include Half-Time with these we'll hold down the ctrl key and click Half-Time.

There we are. If we're interested in only those people who have the highest Job Rating, under Job Rating we'll click 5. So we see that we are choosing Full Time and Half-Time, we see that we are not choosing Contract and Hourly. And perhaps that would be a bit more pertinent as we focus on the departments. Now again, we have to move this around a little bit at different times. Because we're only seeing people with certain Status's and certain Job Ratings we're automatically not seeing people in certain groups. But let's shift the focus, bring back all Job Ratings.

When you clear the filter it's as if you're saying, "Do not filter." So at first maybe this seems a little bit strange. Here's a filter with an x on it, that means don't filter, let's see all Job Ratings. And in the other Slicer here for Status let's see all Status's. But let's do what we did before, choose only the training groups. So I'm gonna click Admin Training right here, and with the ctrl key held down I'll choose Executive Education, and also with the ctrl key still held down choose Professional Training.

And now in our list here off to the left we're seeing only those, just those three categories. And our list is constantly here to remind us what we're not seeing. If someone says, "Could you "show everybody except Manufacturing?" Let's clear the filter, we've got two manufacturing groups here. Using the ctrl key I'm going to select Manufacturing, and now we're down to 580 records. I'm also going to choose Major Mfg Projects, using the ctrl key. So we're seeing all but those two in our list.

I think you can begin to see how valuable these are. Again, this issue is sometimes we have to make room for these on the screen, but this gives you a good visual. And think of situations too where it might be appropriate for a manager at a certain level, who isn't necessarily too adept at using Excel, nevertheless could be, with a few minutes of instruction here, could be taught how to use this feature effectively for presentation purposes. Ideal for using filtering in a visual way.