Overview

Over the past couple of months I have occasionally been losing my PowerPivot Slicers in Excel when using the PowerPivot 2012 add-in.  I have now managed to put together a repro for the issue and will outline this below.

The issue has been reported via Connect, so should receive attention soon.  Until that point, this blog post provides my own (unofficial) description of the problem and how to avoid it, in case other people are hitting it.

Symptoms

The workbook will initially work as normal, i.e. slicers appearing and functioning as normal:

At some point later (maybe hours, days or weeks), you will re-open the workbook and the slicers will appear very broken. Sometimes they disappear completely, at other times they change size.  They often get dropped from the "Slicers Vertical" and "Slicers Horizontal" sections in the PowerPivot Field List, e.g.

Unfortunately the issue is present in both the RTM and CU2 releases of the add-in.  (I have only tested the RTM x86 and CU2 x86 add-ins so cannot definitely say if the issue is present in other releases).

Detailed Reproduction Steps

  1. Double click a PowerPivot workbook in Windows Explorer to open it in Excel.
  2. DO NOT click on any of the charts / pivot tables.
  3. Click on the PowerPivot window button in the ribbon.
  4. Refresh all (in the PowerPivot window).
  5. Refresh the Pivot Chart (right mouse click on the chart, refresh).
  6. Save the workbook and close Excel.
  7. Reopen the workbook.
  8. Click the pivot chart (to load the PowerPivot data).
  9. Click off the chart.
  10. Click back on it, the slicers have now mysteriously become disconnected.

Workaround

The clue to avoiding the issue is in step 2 above...i.e.

Ensure the PowerPivot Model is loaded into Excel before opening the PowerPivot window to refresh.  Or put even more simply...

ALWAYS CLICK ON A PIVOTTABLE, PIVOTCHART OR

SLICER BEFORE OPENING THE POWERPIVOT WINDOW

And, with that, you should be able to continue to use your Slicers.

Updates

More details (and probably future updates to this issue) will be found on Connect:

https://connect.microsoft.com/SQLServer/feedback/details/755850/bug-powerpivot-2012-slicers-are-lost-from-powerpivot-field-list#details

The Connect item also includes example files plus a short video to repro the issue.