Excel Slicers for Easy Pivot Table Filtering

Excel Slicers for Halloween Horror Films

Slasher movies are a scary Halloween tradition, and you can fight back against these horror films, by using Excel Slicers to slash through piles of data.

The Guardian recently posted a list of Greatest Films of All Time. Let’s see how we can use Excel Slicers for Halloween horror films from that list.

You can download the Guardian’s data in this Greatest Films spreadsheet.

I’ve avoided seeing the Halloween movie, but long ago I saw another frightening holiday film — Black Christmas. Don’t take the kids to see that one! But it didn’t even make the Guardian’s list of top horror films (anti-Canadian bias?).

What are Excel Slicers?

Slicers are a new feature in Excel 2010, and while they aren’t as frightful as a slasher movie, they are very powerful, and help you rip into your Excel data.

In this example, I created a pivot table from the greatest films data, and put Genre and Actor into the Report Filters area. When I selected Horror from the Genre drop down, the pivot table showed the top 25 horror movies.

pivot table from the greatest films data
pivot table from the greatest films data

Built-In Pivot Field Filter

When I clicked the drop down for Actors, it showed all the actors, not just the ones in the top horror films.

James Stewart isn’t in any of those names, but he shows up with Jamie Lee Curtis and Janet Leigh.

SlicersMovies00b

Add Excel Slicers

Instead of Report Filters in Excel 2010, you can use Excel Slicers.

To add one, select a cell in the pivot table, and on the Ribbon’s Insert tab, click Slicer.

SlicersMovies01

In the Insert Slicers dialog box, there’s a list of fields from the pivot table. Add a check mark to the fields you’d like to use as slicers.

SlicersMovies01b

Use the Excel Slicers

To use the slicers, click on one of the items. Other slicers connected to the same pivot table will change, to highlight items in the filtered data.

For example, Horror is selected in the Genre slicer.

  • In the ActorName slicer, actors from those horror films are listed at the top.
  • Below those actor names, in grey font, are the remaining actors, from films in other genres.

I clicked Vera Miles in the ActorName slicer, and in the Genre slicer you can see that she is listed in films for both the Action and Horror genres.

Genre Slicer and ActorName Slicer
Genre Slicer and ActorName Slicer

Dependent Lists for Pivot Items

This is a helpful new feature, and provides the dependent type of filter that pivot table users have wished for.

Also, unlike Report Filters, you can position the slicers anywhere in the workbook — even on a different worksheet.

Have you used slicers? Do you like them better than Report Filters?

Watch the Excel Slicers Video

To see the steps for using Excel Slicers, watch this short Excel video tutorial.

_____________

6 thoughts on “Excel Slicers for Easy Pivot Table Filtering”

  1. Debra – I loved your integration of horror movies and slicers! Just this past week I added a slicer to a pivottable in a financial tracking worksheet and love it because they now work similar to filters on a regular worksheet. i.e., data that would appear after other filters were applied appears at the top. I also customized the display of the slicer to show four or five columns of data because the amount of vertical space I had was very limited while the horizontal space was not.

  2. Debra,
    Nice segue. For your information, Black Christmas is ranked #1911 of the 9000+ greatest movies of the 20th century.
    Brad Bourland, a grocery produce clerk in Austin Texas USA, spent the last ten years actually watching and ranking all 9000+ movies on the list.
    Just recently, I put the list on an Excel 2003 worksheet (rank, title, director, year) and included a one-click column sort feature.
    The sort code (by Dave Peterson) is from your “Excel Tips and Excel Tutorials” web page.
    The workbook can be downloaded from… http://excelusergroup.org/media/

  3. @Bob, thanks for describing how you’ve used slicers. I like the feature, but can’t use them in client files yet, because my clients are using older versions of Excel.

    @Jim, thanks for the info on Black Christmas, and for the link to your file. I made the clickable rectangles a bit narrower in your headings, so I could use the filter drop downs too.
    That let me filter for the director of Black Christmas, and reminded me that he also directed a slightly less violent holiday movie — A Christmas Story.

  4. Hi Debra,

    Thanks for the tutorial
    I can seem to sort the pivot table based on the rank column
    It is alpha sorted on the title column.
    No matter what I do, I can teem to change it

    Any ideas?
    Thanks
    Winston

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.