As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013. Much of the content will be a result of my daily interactions with business users and other BI devs. In order to not forget what I learn or discover, I write it down … here. I hope you too will discover something new you can use. Enjoy!
Adding a Slicer
Slicers are not new functionality in Excel as they were in Excel 2010 as well. However, they are even more highlighted in Excel 2013. You can add a slicer from the INSERT tab on the Ribbon in Excel as shown in the image below.
When you click this button, you get a data source dialog. It will show you the available connections in your workbook as well as other connections you have used or are available. Now in my case, I am using a PowerPivot data source. In order to use that you need to create your slicer from the PivotTable tools as shown below by right-clicking the field to use as a Slicer.
Once you click this, it will create a Slicer on the sheet you are working in with all of the available, unique values from the field.
Connecting Your Slicers to Data
Now you have created your first slicer. However, I would be cruel not to wrap this up by showing you how to connect your slicer to related data objects in Excel.
There are two ways to get to the Report Connections option. First, in the short cut menu (right-click) on the slicer you will get the option in that menu as noted below.
The second way is to bring focus to the Slicer and you will get a new menu option on the ribbon called Slicer Tools – Options. The Report Connections button there will lead you to the same dialog to relate object.
You can also get to the short cut menu from the target data object such as a PivotTable.
The Report Connections dialog allows you to relate the slicer to data objects from the same data source. It will display objects from all of the objects within your workbook. This means that you can have slicers filter data across multiple sheets. This is really cool.
In the next tip, I will talk about a new setting which, I believe, make slicers even more user friendly and usable to create a dashboard in Excel.
