Excel Slicers are a very popular feature in Excel but what are slicers and why are they so popular? A slicer is simply a type of visual filter. You can use Excel Slicers to filter any data that you want such as a pivot chart or a pivot table. For example, let’s say that you are looking at a pivot report at the sales for any particular region. There are basically two options for you to consider. The two options include to introduce a slicer to a region and then click on the region you desire. You can also introduce a region as a type of report filter and then filter the region that you desire.

It should be mentioned, that Excel Slicers will only work in Excel 2010 or newer. If you want to add a slicer in Excel 2010 you will only be able to add it to the pivot tables. You can go to two locations to add a slicer:

1. Insert a ribbon and then click on insert slicer.
2. Or select options ribbon and then click on insert slicer.

In Excel 2013 / 2016 you can put a slicer into either a regular table or a pivot table. To put a slicer into a regular table, use the insert ribbon then use the slicer insert button. When adding a slicer to a regular Excel table it will perform like an auto-filter for your table. To add a slicer to a pivot table you can either right-click the pivot table field and then choose add as slicer or use either of the insert ribbon or analyze functions to add the slicer.

You will have the option of selecting multiple items for single items in slicers. To select multiple items, and if the items are all together, you simply drag from the first item to the last. If all of the items that you want to select are not together then use the CTRL key and choose them one at a time.

Since Excel Slicers have the ability to talk to pivot tables you will be able to utilize them to create interactive charts. For example, you can set up pivot tables that will provide you with data for your chart. You will be able to create a regular or pivot chart from your pivot table data. You can move your slicer beside the chart and format all of them together to your whatever particular taste you want.

You also will be able to add multiple slicers to a pivot report. As you add multiple slicers you will notice that each one of them plays a role in telling a pivot table what the subset of data will be necessary for calculating the numbers.

You also have the ability to link any single slicer to various pivot reports. This feature will make it possible to construct powerful, interactive and cross filtered reports using Excel. You will need to follow these steps to connect any multiple pivot tables to a single slicer.

1. Give a name to each of the pivot tables. To do this you only need to click anywhere on the pivot. Travel to the Analyze ribbon and enter the pivot table name field in the top left corner. If you decide not to name your pivot table, the Excel program will provide them with a default name. Unfortunately, this may be quite confusing if you have more than one or two pivot tables.
2. On the slicer, right-click and go to Report Connections. It will be called Pivot Table connections in Excel 2010. Click on whatever pivot tables you desire and then click okay. That is all you need to do. This will allow each pivot table to respond to a slicer. You can also utilize the same approach for linking one slicer to more than one regular or pivot chart.

While slicers are fun and amazing for pivot table framework, there may be times when you want to go outside of the pivot table framework. For example, you may desire to use a slicer to add interactivity to a chart or maybe use them in a dashboard. If you want to do something like that it is vital that your slicer is able to communicate with your formula. There are two approaches to do this. The two approaches include harvester, or dummy pivot table route, and CUBE formulas route.

The easiest way to put a slicer selection into a cell is by using a dummy pivot table. For example, let’s say that you want to know which profession is being utilized by the slicer so that you can later use it in a chart or formula. You will need to create a pivot table, add a profession field to the row label area, link up the slicer with your new pivot table, click on the slicer which will allow both the dummy pivot and the original pivot to change, and access a row label the same way as you would with a regular cell in a formula.

The CUBE formula approach, on the other hand, is only relevant if your slicer is hooked up to a data model. It can be hooked up to SAS Cubes, Power Pivot, or This Work Book Model in Excel 2013 or newer. A couple of bonus functions that are now available when using the CUBE formula approach is that you will be able to count the number of selected items in a slicer. You also will be able to easily extract all of the items that are selected in a slicer by combining CUBESETCOUNT and CUBERANKEDMEMBER.

You may have additional questions such as how do I deal with too many items in a slicer? What do I do if I find that I do not want a particular slicer color? How can I get rid of a title on a slicer? What do I do if my slicer continues to show old categories or products that are no longer necessary? The good news is that all of these questions can be easily answered by going online and using a Google search engine.