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!
A picture, or in this case two pictures, are worth a thousand words. I created a pivot table from Power Pivot and then added two slicers above the pivot table. The pivot table contains a date hierarchy which can be expanded and collapsed. During this process the slicer moves around which is not optimal when you are creating a visualization in Excel such as a dashboard. Here are the screenshots which highlight the issue.
How I set it up:
What happens when the date gets expanded:
How to Fix the Slicer Position
Right click the slicer you want to keep from moving, in my case that is the second one. I first looked in the settings, but saw nothing. I stumbled onto the Size and Properties option which opened the Format Slicer slide out menu. If you expand the Properties section select “Don’t move or size with cells” option, the slicer will no longer move.
This is just one more way to use slicers to improve the user experience in your Excel dashboards and reports.