{"id":545,"date":"2019-04-16T18:23:35","date_gmt":"2019-04-16T15:23:35","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=545"},"modified":"2019-05-06T12:49:00","modified_gmt":"2019-05-06T09:49:00","slug":"using-slicers-with-your-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/using-slicers-with-your-pivot-tables\/","title":{"rendered":"Using Slicers with your Pivot Tables"},"content":{"rendered":"\n<p>Since Excel 2010, a new and very interesting feature was added. Slicers. At first glance, they might seem like a beautified way to filter a <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/learning-about-pivot-tables\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"pivot table, (opens in a new tab)\">pivot table,<\/a><\/strong> but they can be a lot more than that.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/Kj4SdeqVZiA?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice FIle:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/slicers.xlsx\">slicers<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/slicers.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>In the current sheet, we can see a table along with a pivot table and a <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/creating-pivot-charts-in-excel\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"pivot chart (opens in a new tab)\">pivot chart<\/a><\/strong> based on it.<\/p>\n\n\n\n<p>The pivot table has\nalready been added to the data model. <\/p>\n\n\n\n<p>Let\u2019s add some\nslicers.<\/p>\n\n\n\n<p>First, we click on\neither the pivot table or the pivot chart and select the \u201cAnalyze\u201d tab on the\nribbon.<\/p>\n\n\n\n<p>There are two types of\nslicers. A general one suitable for all forms of data and the timeline, which\nwas introduced in Excel 2013.<\/p>\n\n\n\n<p>Let\u2019s add a simple\nslicer for the product field and a timeline for the date field.<\/p>\n\n\n\n<p>We can now use them to\nfilter our pivot table and chart. We select to show only data for months April\nto August, and not to show data for shoes and socks.<\/p>\n\n\n\n<p>There is an obvious\nadvantage on the filtering provided by slicers over the built-in filtering of\nthe pivot table. <\/p>\n\n\n\n<p>We can instantly see\nthe filters currently in effect.<\/p>\n\n\n\n<p>The timeline slicer\nhas a dropdown menu to choose how the dates are shown. <\/p>\n\n\n\n<p>By clicking on the timeline,\na new tab appears on the ribbon, named \u201cOptions\u201d.<\/p>\n\n\n\n<p>Clicking on it we have\naccess to various commands. We can change the caption, and the style of the\ntimeline, its size and alignment and we can even enable or disable various of\nits features.<\/p>\n\n\n\n<p>But the button called \u201cReport\nConnections\u201d is probably the most interesting.<\/p>\n\n\n\n<p>Before we click on it\nlets create one more pivot table based on our original table.<\/p>\n\n\n\n<p>It will show the\nquantity of sales per color and per product. We will have to add this table to\nthe data model as well.<\/p>\n\n\n\n<p>Now let\u2019s go to that\nbutton we talked about before.<\/p>\n\n\n\n<p>By clicking on it we\nsee a list on all the pivot tables currently on the data model.<\/p>\n\n\n\n<p>The first Pivot table\nis already selected. By selecting the second as well, the slicer will be\ncapable of filtering the two pivot tables simultaneously.<\/p>\n\n\n\n<p>We repeat the process\nfor our product slicer.<\/p>\n\n\n\n<p>Notice how the tables\nand the chart change, by altering the slicers.<\/p>\n\n\n\n<p>The general slicers\nare packed with some more features. We can access them via the slicer settings\nin the options tab.<\/p>\n\n\n\n<p>There are options to\nsort the slicer data, to hide those with no associated information or to flag\nthem. There is the option to change the caption, and the option to change the\nname. <\/p>\n\n\n\n<p>In summary, the slicers are a very welcome addition to the Excel arsenal, and are here to stay and evolve.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Since Excel 2010, a new and very interesting feature was added. Slicers. At first glance, they might seem like a beautified way to filter a pivot table, but they can be a lot more than that.<\/p>\n","protected":false},"author":2,"featured_media":546,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[38,11,43,37,42],"class_list":["post-545","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-analysis","tag-data","tag-filter","tag-pivot-tables","tag-slicers"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/slicers.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8N","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=545"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/545\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/546"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}