Items that may have cached data found

The Document Inspector found one or more BI features that have a cache that's saved in the workbook, possibly containing data that's not visible.

Here are the types of cached data the Document Inspector checks for:

• PivotCache This cache is saved for PivotTables and PivotCharts.

• SlicerCache This cache is saved for slicers (with or without PivotTables) and timelines.

• Cube formula cache This cache is saved for Cube formulas.

Issue

The Document Inspector can't remove the cached data for you, because that might cause your workbook to stop working correctly.

Recommended solution

The only way to be sure your workbook doesn't contain cached data for PivotTables, PivotCharts, slicers, timelines (Office 2013 or newer), or Cube formulas is to remove all of those items.

• PivotTables

  1. To remove a PivotTable but keep its data, select the entire PivotTable and press Ctrl+C.

  2. Select a worksheet cell outside the PivotTable, press Ctrl+Alt+V, and pick Values.

     

     Note: Make sure the data you're pasting doesn't overlap the PivotTable.

  3. Select the entire PivotTable and press Delete.

  Tip: Repeat these steps for each PivotTable in your workbook.

• PivotCharts

  1. To remove a PivotChart but keep it as a picture, select the PivotChart area and press Ctrl+C.

  2. Select a worksheet cell outside the PivotChart, press Ctrl+Alt+V, and pick a Picture format.

     

  3. Select the PivotChart and press Delete.

  Tip: Repeat these steps for each PivotChart in your workbook.

• Slicers and timelines

  • Select a slicer or timeline and press Delete.

  Tip: Do this for each slicer or timeline in your workbook.

• Cube formulas

  1. To remove a Cube formula but keep its resulting value, select the Cube formula and press Ctrl+C.

     Note: If you don't know where the Cube formulas are, try locating them by finding cells that contain formulas first.

  2. Keeping the cell that has the Cube formula selected, press Ctrl+Alt+V, and pick Values.

     

     Tip: Do this for each Cube formula in your workbook.

Clear the cached data of PivotTables or PivotCharts

To keep any PivotTables or PivotCharts in your workbook, there are steps you can take to reduce or clear the cached data in your workbook.

Note: After clearing the cached data, the Document Inspector will continue reporting about these items because it won't be able to determine whether you successfully cleared the cached data.

1. Close the Document Inspector.

2. Select a field in the PivotTable or PivotChart, and then click Analyze > Options.

3. On the Data tab, in the Number of items to retain per field box, pick None and click OK.

4. Click Analyze > Refresh.

If you run the Document Inspector again, it'll find the PivotTables and PivotCharts, whether they have cached data or not. Only when you remove PivotTables and PivotCharts, you can be absolutely sure the cached data is gone.