Clever Clogs tip # 711
11 August 2022

Do not show deleted data in PivotTable slicers (Microsoft Excel) *

This week's tip is inspired by a course participant and tip subscriber (thanks, Jayde!) who suggested to write a tip about the fact that slicers in PivotTables keep the details of old data, greyed out.

Like Jayde, it always annoys me, but I had assumed it wouldn't effect too many people, so I never wrote a tip about it. But then I got exactly the same question this week. (Thanks, Raquel!) So here's what you can do. You have two options ...

First of all, you can right-click the Slicer and untick Show items deleted from the data source in the Slicer Settings.

But you can also tackle it at the source, within the PivotTable itself.

Here's how:

  1. Right-click the PivotTable and select PivotTable Options.
  2. Select the Data tab.
  3. Change Number of items to retain per field from Automatic to None.
  4. Press ENTER or click OK.

Oh, and don't panic when nothing changes ... you'll need to refresh your PivotTable. (I typically right-click.) Speaking of which, you might as well tick the Refresh data when opening the file check box after step 3. Which makes you wonder why Microsoft doesn't change these two settings to be the default.

Related tips

  1. Update PivotTables and PivotCharts when you open the workbook: tip_349.php
  2. Exclude data from your PivotTable report using Slicers - tip_588.php
  3. Filter data in a PivotTable and PivotChart using Slicers - tip_474.php

This week's CleverClogsTipTime on LinkedIn

Do any of you use ALT + SHIFT + down arrow in Excel?

* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013 and 2016.