Create and format PivotTables and charts

Slice data in PivotTables

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

Slicers are a quick and effective way to filter large amounts of data in your PivotTable. Slicers are buttons you can click to filter the data. They stay visible, so you always know what fields are shown or hidden in the filtered PivotTable.

Insert slicers
  1. Select a PivotTable.

  2. Select Analyze > Insert Slicer Slicer .

  3. In the Insert Slicers box, select the fields you want to create slicers for.

    Note:  You can select fields in the PivotTable, as well as fields in the source data that are not part of the PivotTable.

  4. Select OK.

  5. Select a slicer and do one of the following:

    • Drag the slicer where you want.

    • Resize the slicer.

    • Select a color or style in Options > Slicer Styles.

    • Change the number of Columns in the slicer in Options > Columns.
      SlicerStyle

  6. In each slicer, select an item to view results in the PivotTable.
    To select more than one item, hold the Ctrl key, and then select the items that you want to view in the PivotTable.

  7. To clear a Filter, select the X on the Filter icon in the slicer.

Want more?

Discover more courses like this at LinkedIn Learning

Filter data in a PivotTable

Slicer is available on the Analyze tab. It's a visual tool.

The problem sometimes is it takes up a good deal of screen space.

I'm going to Insert a slicer for some fields here.

First of all, for the fields that are already in the PivotTable. Customer and Salesperson.

Salesperson there, Customer there. Those are already in the PivotTable.

I also want to show with slicers product and region, so there's another element of this we're about to see. Click OK.

Now, if you're making a presentation, ideally you'd be doing what I'm doing ahead of time, but I do want to show you how to manipulate these a little bit.

It can take up some space depending upon the field, move these around in different ways.

Notice the contextual tab appears in the ribbon.

I could go up there and make this one green and this one yellow and so on, move the product over here, shrink it a bit also so that it takes up less space, and also Salesperson.

We can even make these two-column if necessary, not a bad idea here on the options tab up there, columns to the right too, and we can make this wider.

Now again, emphasizing this idea, two of these fields are currently present in the PivotTable: Customer and Salesperson.

Region and product are certainly part of our source data but not in the PivotTable, and again slight color differentiation here just to bring this out.

Here's how the slicer's going to work. Someone is asking us to see this layout but only for the Northeast region.

I'm gonna click Northeast over in the Region slicer, and that's all we're seeing.

Some of our salespersons don't work out of the Northeast.

They never sell to that region.

That's why we're seeing fewer salespersons, and the slicer for Salespersons is reminding us of which people are not active in the Northeast as well as those who are.

So they're working in sync. I didn't touch the Salesperson slicer here.

I made the choice on region. Let's include another region.

How about the Southeast region?

I'll use the control key and click the region slicer entry for Southeast. And now what do we see? Southeast and Northeast.

Now as we're looking at the PivotTable itself there's no indication whatsoever about regions, but we can clearly see from the slicers what's going on.

Let's go over to Customer.

Maybe we only want to see the first three customers here.

Let's click B&B Spaces, and then with the Shift key, I'll click Fabulous Homes, and we'll be seeing those three together.

Now that is being shown in the PivotTable, and so, I wouldn't call it redundant, but we are reminded, and we were not before, when looking at filters in general, we are reminded on our customer list which two customers are not currently being shown.

So I think you can sense here there's a lot of creativity.

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!

×