{"id":3564,"date":"2018-11-30T16:39:14","date_gmt":"2018-11-30T16:39:14","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=3564"},"modified":"2024-03-28T12:20:37","modified_gmt":"2024-03-28T12:20:37","slug":"pivot-table-slicers","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/","title":{"rendered":"Pivot Table Slicers"},"content":{"rendered":"\n
Pivot-Table-Slicers-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

With PivotTable slicers, you can filter a PivotTable using visual buttons. Slicers also show the current filtering state, by greying out positions that are not present in the result.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Pivot Table slicers vs filters<\/h2>\n\n\n\n

Slicers are not necessary to filter data inside a PivotTable, they are just a helpful way to make filtering more visually appealing than with standard filters.<\/p>\n\n\n\n

Creating a PivotTable and adding a slicer<\/h2>\n\n\n\n

Before you add a slicer, you have to create a PivotTable from a standard table. You can download the data I used under this link<\/a>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Insert a PivotTable (Insert >> Tables >> PivotTable<\/strong>).<\/p>\n\n\n\n

Click the PivotTable and on the right side, inside the PivotTable fields check: Film, Director, and Actor.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Drag Actor<\/strong> to filters and Director<\/strong> and Film<\/strong> to ROWS.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This configuration will create a list of directors and movies that they made. There is also a filter where you can choose only movies where the selected actor played a role.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, let\u2019s create a slicer. Click the PivotTable and go to PivotTable Tools >> Analyze >> Filter >> Insert Slicer<\/strong> or Insert >> Filters >> Slicer<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Select Director<\/strong> to create a slicer.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can click any button inside the slicer, and the filtered data will change accordingly.<\/p>\n\n\n\n

The multiple-column view<\/h2>\n\n\n\n

As you could see inside the Insert Slicers<\/strong> window, you can add more than just one slicer.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

All of the selected slicers will be connected to one another. This time select Film<\/strong>, Director<\/strong> and Actor<\/strong>. It will create three slicers, which you can drag and scale.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you choose one button, the available option will be highlighted.<\/p>\n\n\n\n

A few ways to filter a slicer<\/h2>\n\n\n\n

To select another position on the slicer just click it. You can also select multiple options by holding the Ctrl<\/strong> button and clicking the other option.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, you are going to see all the available options. In this case all movies from both directors and all actors on the list that played a role in these movies.<\/p>\n\n\n\n

Multiple columns in the slicer<\/h2>\n\n\n\n

The Actor<\/strong> slicer is a bit too tall and you can\u2019t see all the results. In Excel, you can choose to display the slicer buttons in more than one column.<\/p>\n\n\n\n

Click the slicer and go to Slicer Tools >> Buttons<\/strong>. Here, there is a column field. Change the number of columns to 3.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

It keeps the same width, so only part of the text will be visible. But you can easily control the height and width by dragging one of the edges.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Connect the slicer to multiple pivot tables<\/h2>\n\n\n\n

Copy the PivotTable and make a copy, by selecting all cells of the pivot table and pressing Ctrl + C<\/strong>. To paste click a cell where you want to paste and press Ctrl + V<\/strong>.<\/p>\n\n\n\n

Modify both PivotTables.<\/p>\n\n\n\n

First PivotTable<\/h3>\n\n\n\n

Select only the Director<\/strong> and Film<\/strong> options. Your order should look like this.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Second PivotTable<\/h3>\n\n\n\n

In the second PivotTable Genre<\/strong> and Film<\/strong>. This is the order of the second PivotTable.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This is going to give you the following result.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Click the first PivotTable and choose PivotTable Tools >> Analyze >> Filter >> Insert Slicer<\/strong>.<\/p>\n\n\n\n

If you click any of the buttons, you will see that only the first PivotTable changed and the second one is not affected. It works this way because you\u2019ve created slicer only for the first PivotTable.<\/p>\n\n\n\n

But there is a way to create a slicer that will affect any number of PivotTables.<\/p>\n\n\n\n

Click the slicer and go to Slicer Tools >> Options >> Report Connections<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Select both PivotTables and click OK<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, if you click a button, both pivot tables will change accordingly.<\/p>\n\n\n\n

Excel 365 Update<\/h2>\n\n\n\n
    \n
  • New Slicer Formatting Options:<\/strong> Excel 365 offers more options for customizing the visual appearance of slicers. You can now change the background color, font, and border styles, allowing for better integration with your spreadsheet design.<\/li>\n\n\n\n
  • Improved Slicer Filtering Experience:<\/strong> Excel 365 provides clearer visual indications of filtered items within slicers. When selections are made, the corresponding buttons are highlighted, making it easier to understand the impact of applied filters.<\/li>\n\n\n\n
  • Slicer Selection with Drill Down:<\/strong> In Excel 365, you can hold the ‘Ctrl’ key while selecting slicer items. This enables you to drill down into specific data points within the pivot table, providing a more granular level of analysis.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

    With PivotTable slicers, you can filter a PivotTable using visual buttons. Slicers also show the current filtering state, by greying out positions that…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nPivot Table Slicers<\/title>\n<meta name=\"description\" content=\";> Insert Slicer. Choose Actor, and the slicer will appear. You can now filter the movies list by clicking on the actors you want to include.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pivot Table Slicers\" \/>\n<meta property=\"og:description\" content=\";> Insert Slicer. Choose Actor, and the slicer will appear. You can now filter the movies list by clicking on the actors you want to include.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-30T16:39:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T12:20:37+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Pivot Table Slicers\",\"datePublished\":\"2018-11-30T16:39:14+00:00\",\"dateModified\":\"2024-03-28T12:20:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\"},\"wordCount\":729,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\",\"name\":\"Pivot Table Slicers\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png\",\"datePublished\":\"2018-11-30T16:39:14+00:00\",\"dateModified\":\"2024-03-28T12:20:37+00:00\",\"description\":\";> Insert Slicer. Choose Actor, and the slicer will appear. You can now filter the movies list by clicking on the actors you want to include.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pivot Table Slicers\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Pivot Table Slicers","description":";> Insert Slicer. Choose Actor, and the slicer will appear. You can now filter the movies list by clicking on the actors you want to include.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/","og_locale":"en_US","og_type":"article","og_title":"Pivot Table Slicers","og_description":";> Insert Slicer. Choose Actor, and the slicer will appear. You can now filter the movies list by clicking on the actors you want to include.","og_url":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/","article_published_time":"2018-11-30T16:39:14+00:00","article_modified_time":"2024-03-28T12:20:37+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Pivot Table Slicers","datePublished":"2018-11-30T16:39:14+00:00","dateModified":"2024-03-28T12:20:37+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/"},"wordCount":729,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/","url":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/","name":"Pivot Table Slicers","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png","datePublished":"2018-11-30T16:39:14+00:00","dateModified":"2024-03-28T12:20:37+00:00","description":";> Insert Slicer. Choose Actor, and the slicer will appear. You can now filter the movies list by clicking on the actors you want to include.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/11\/pivot-table-slicers.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-slicers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Pivot Table Slicers"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3564"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=3564"}],"version-history":[{"count":5,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3564\/revisions"}],"predecessor-version":[{"id":17218,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/3564\/revisions\/17218"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=3564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=3564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=3564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}