Add and format tables

Filter data with slicers

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

When working with large amounts of data, you can use slicers in your table, to filter and view your data.

Convert data into a table
  1. There are four ways to convert data into a table:

    Note: In order to use a slicer, you must convert your data into a table first.

    • Press Ctrl + t.

    • Press Ctrl + l.

    • Select Home > Format as Table.

    • Select Insert > Table.

  2. Select OK.

Use a slicer to filter data
  1. Select Insert > Slicer.
    InsertSlicer

  2. Select the fields you'd like to filter.

  3. Select OK and adjust your slicer preferences, such as Columns, under Options.

    Note: To select more than one item, hold Ctrl, and then select the items that you want to show. Select and hold the corner of a slicer to adjust and resize it.

  4. Select Clear Filter Delete to clear the slicer filter.

Want more?

Discover more courses like this at LinkedIn Learning

Filter data in a PivotTable

What's new in Excel 2016 for Windows

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 it's drawbacks is that you're not always reminded of what you are not seeing.

In Excel 2010, new features 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 is 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 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 in most cases, Excel immediately figures out the extent of your data.

Click OK, and the data has been converted into a table.

Now, filter errors 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 interested 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. 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.

Sometimes that's important. If you know the data well, that's fine, but what about your audience? They're four kinds of status.

We get to do similar filtering there too.

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 we're going to be most interested in.

And we could have many of them actually.

I'm going to choose Department, also Status, maybe Job Rating, and maybe we'll come back and chose others at a later time.

If there's a down side 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 going to 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, but there are different Job Ratings, Status, there we go, something like this.

Now, there are bout 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 three columns and then make it a bit wider.

We don't necessarily have to see the entire name.

We probably want to see enough of it to make sense 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 five.

So we see that we are choosing Full Time and Half-Time.

We see that we are not seeing 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 statuses and certain job ratings, we are 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 statuses.

But let's do what we did before, choose only the training groups.

I'm going to click Admin Training right here, and with the Ctrl key held down, I'll chose 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 Manufacturing Projects using the Ctrl key.

So we're seeing all but those two in our list.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×