To remove deleted values from a PivotTable slicer in Excel for Microsoft 365 on Windows, set the PivotTable cache to retain no deleted items, then refresh the PivotTable. Right-click the PivotTable, choose PivotTable Options, open Data, set Number of items to retain per field to None, select OK, and refresh. If you only want to undo the current slicer selection, use the slicer’s Clear Filter button instead; that does not remove old values from its list.
Why old values remain in an Excel slicer
A slicer is a visual filter, not a separate list that independently controls what is in the workbook. When a slicer is connected to a PivotTable, its available items are influenced by the PivotTable’s source and cache. The cache can remember field items that appeared in earlier source data, even after those records have been deleted. As a result, a slicer may still list a former employee, discontinued product, old region, or outdated status.
This behavior is useful when a source temporarily changes or when someone wants to keep historical filter choices available. But it can be confusing when the removed value looks like it is still present in the report. An item remaining in the slicer does not, by itself, prove that the underlying source still contains records for it. It may be a remembered item with no current matching data.
There are two different actions people mean by remove old values. Clearing a filter restores the slicer to its unfiltered state. Removing stale items changes what the PivotTable cache retains. Choose the second approach when a value has been deleted from the source and should no longer appear as a slicer choice.
Remove deleted items from a PivotTable slicer
- Confirm that the value is actually gone from the data source. Check the relevant field for extra spaces, alternate spellings, or a remaining row containing the value.
- Click a cell inside the PivotTable connected to the slicer. If you have several PivotTables, make sure you are working with one that uses the same cache as the slicer.
- Right-click inside the PivotTable and choose PivotTable Options. You can also select the PivotTable and look for the options command on the PivotTable Analyze tab.
- In the PivotTable Options dialog, open the Data tab. Find Number of items to retain per field and choose None.
- Select OK, then refresh the PivotTable. Right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh. If the source is a query or external connection, refresh that source as well.
- Check the slicer again. If the old item remains, refresh any other PivotTables that share the cache and confirm that each one is using the intended source data.
The setting controls whether deleted source items are kept in the cache; it does not delete records from a worksheet, table, database, or query. It also does not remove a value that still exists in the source. After removing an item from the source, refresh the PivotTable so Excel can rebuild its current view using the updated data.
On Excel for Mac, select the PivotTable and look for PivotTable Analyze > Options or use the right-click PivotTable Options command. The dialog layout and labels can differ slightly by release, so locate the Data tab and the retain-items setting. If the setting is unavailable in Excel for the web, open the workbook in the desktop app to change the cache option.
Clear a slicer filter without changing its item list
If the value is not stale and you simply want to show all categories again, select the slicer and click its Clear Filter button, usually shown as a funnel with a red cross near the slicer header. This clears the active selection; it does not change the PivotTable cache or delete any slicer items.
For example, suppose a Region slicer currently filters a report to West. Clearing the filter restores the report to all regions available to that PivotTable. It will not remove East or West from the slicer, because those are valid field values. Similarly, selecting a different button in the slicer changes the report filter, not the source data.
If the clear-filter icon is not visible, check whether the slicer header is displayed. Select the slicer, open the Slicer tab, and use the available header or slicer settings. The exact ribbon label can vary with the selected object and Excel version. You can also use the slicer’s multi-select control to adjust selected items, but that still only changes the filter.
PivotTable slicers and Excel Table slicers are different
The retain-items setting applies to PivotTable caches. A slicer inserted for a regular Excel Table works differently: it filters the table’s current rows rather than using a PivotTable cache. If an item is still visible in a Table slicer, first check whether any table row still contains that value and whether the table includes more rows than expected. The PivotTable Options cache setting will not solve a problem with a regular Table slicer.
To identify the object, click it and look at the contextual ribbon tabs. A PivotTable slicer is associated with a PivotTable and typically shows a Slicer tab alongside PivotTable tools. A Table slicer is connected to an Excel Table and shows table-related tools. You can also click the report itself: if it has PivotTable fields and PivotTable Analyze tools, it is a PivotTable report.
There is another important distinction: a slicer can connect to more than one PivotTable. If those PivotTables share a cache, a change to the retained-items setting can affect the other reports using that cache too. Use Slicer > Report Connections, or PivotTable Analyze > Filter Connections depending on the ribbon, to review which reports a slicer controls. Do not assume that changing one report changes every workbook report unless they are connected appropriately.
Refresh the right source and cache
A normal PivotTable refresh updates the report from its source, but the order matters when the source itself is refreshed from elsewhere. For a Power Query table, for example, refresh the query or use Data > Refresh All, then refresh or verify the PivotTable. If you refresh the PivotTable before the query has updated, Excel may still be working from the previous rows.
For a workbook with several PivotTables, identify whether the old value appears in one slicer or in several reports. PivotTables built from the same range or table often share a cache, while reports made separately may have different caches. Changing the retention option on one cache does not necessarily clear stale items in another. Refresh each relevant report and inspect its source and cache settings.
External connections and the Excel Data Model can add another layer. A refresh must successfully update the connection or model before the slicer can reflect the new members. If a value is still in the model, or the source query returns it again, clearing a PivotTable cache will not make it disappear permanently. Check the source query, relationship or model field, and refresh status rather than repeatedly clearing the slicer.
Common problems and how to diagnose them
- The value is still in the source. Search the source field, including hidden rows and rows outside the visible part of a table. A slightly different spelling or a trailing space can make two entries look alike.
- The source refresh failed or did not run. Check the query or connection refresh status, then refresh the report after the source has updated.
- You changed the wrong PivotTable. A slicer may control several reports, and those reports may not all share one cache. Check Report Connections and repeat the setting for the relevant cache if needed.
- The slicer item is present but has no current data. A remembered cache item can remain visible without matching current records. Set deleted-item retention to None and refresh; do not mistake a visible button for proof that the source has rows for it.
- The slicer is filtering the report unexpectedly. Use Clear Filter to remove the current selection. Changing cache retention is not a substitute for clearing an active filter.
- The field is from a Data Model or external source. Confirm that the underlying model or connection no longer supplies the item. Cache settings cannot remove a member that is still returned by the source.
If the old value disappears and later returns, investigate the refresh process. A query may append historical data, a linked source may still contain the row, or another user may have reintroduced it. The lasting fix is to correct the source or transformation and then refresh, rather than repeatedly editing the slicer.
What to do if you need to preserve history
Choosing None is appropriate when deleted values should no longer be offered as filter choices. If users need to compare historical categories, however, keeping older items may be intentional. Consider whether the item is truly obsolete or whether the report needs a history field that remains in the source. For instance, an employee name can disappear from current transactions while still being needed for archived results.
A practical alternative is to keep historical records in the source and add a separate active-status or reporting-period field. Users can then filter current items without erasing the ability to analyze past data. Another option is to use a separate reporting table with only the categories that should appear in a particular slicer. These approaches make the data design explicit; they are preferable to relying on a cached item as a substitute for historical records.
Before changing cache retention in a shared workbook, consider the effect on other reports that use the same PivotTable cache. Save a copy if the workbook is important, then refresh and verify the affected PivotTables, charts, and slicers. The setting is reversible, but a refresh with no retention can remove deleted labels from the available cache list.
Frequently Asked Questions
Why does my Excel slicer show items that I deleted?
A PivotTable cache may retain items that were once in the source. Set Number of items to retain per field to None in PivotTable Options > Data, then refresh the PivotTable. Also check that the source or query does not still contain the value.
How do I remove one item from a slicer?
If the item is still a valid source value, Excel does not treat the slicer as an editable list where you can delete just that button. Remove or change the value in the source if it should no longer exist, then refresh. If it is only selected, use Clear Filter or change the selection.
Will clearing a slicer filter delete old values?
No. Clear Filter removes the current filtering selection and shows all applicable items again. To remove deleted PivotTable items, change the cache retention setting and refresh.
Why did an old slicer value come back after I removed it?
The value may still be present in the source, a query may have returned it again, or another PivotTable may use a separate cache that was not refreshed. Check the source and refresh the relevant reports after it updates.
Does the None setting work for slicers on Excel Tables?
No. It is a PivotTable cache setting. Table slicers filter the rows in their Excel Table, so check the table data and its boundaries instead.
Can I remove old slicer values in Excel for the web?
You can work with slicers and refresh supported reports, but the PivotTable cache-retention setting may not be available there. Open the workbook in desktop Excel for Windows or Mac and use PivotTable Options to change it.
Related
- Excel Protected Sheet Unlock Online Free: Safe Methods
- Excel Protected Sheet Unlock Online: What Works Safely
- Excel Protected Sheets: How to Allow Sorting Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Watermark in Word on Windows 11To add a watermark in Word on Windows 11, open the document in the Word desktop app and…
- Google Sheets Budget Template: Build a Monthly PlanA Google Sheets budget template is a spreadsheet for listing income, planned expenses, and actual spending in one…
- Excel Text Overflow: How It Works and How to Control ItYes. In Excel, text can visually overflow into the cell to its right when that neighboring cell is…
- How to Convert an Excel Table to Word Without Losing LayoutTo convert an Excel table to Word, select and copy the cells in Excel, then paste them into…
- How to Indent a New Line in an Excel Cell: Easy StepsTo indent just one new line in an Excel cell, edit the cell, insert a line break with…
- Compare Documents in Word 365: A Practical GuideTo compare two files in Word 365 on Windows, open Word and choose Review > Compare > Compare,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.