Setting default values for slicers in PowerPivot

I’ve been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:

  • Year
  • Month
  • Day

But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.

So I seemed to be fresh out of luck. However, one of my Spanish colleagues José Quinto Zamora came to the rescue. All you need to do is to select the slicer filter value that you want as the default, before you save the workbook, and every time you open the workbook, the slicer value will be already selected. That’s as good as a default value for me. Thanks José!

Hope this helps someone else.

2011-08-11