When I’m working with an Excel pivot table*, I sometimes find that even after I’ve deleted something, like a typo, from my starting data, the item I deleted will still appear in my pivot table filters and slicers (slicers are a great feature new to 2010). Specifically, this has happened when I created a pivot table based on data that had a typo and then created a second pivot table on the same data after I corrected the typo. You can see an example in the images above. Although Occoosional does not appear in the pivot table, it does appear in both the slicer in the first image and the filter in the second.

This stumped me for a long time, until someone shared the secret with me. You can fix this within the Pivot Table Options dialogue box by telling Excel to not retain deleted items.

1. Click on your pivot table.
2. Go to the Options tab on the ribbon.
3. Click the Options button on the left side of the ribbon.
4. Click on the Data tab of the Pivot Table Options window.
5. In the Retain items deleted from the data source section, click the drop down arrow and select None.
6. Click OK.

*If you’re not familiar with Excel pivot tables, they’re a fantastic tool that help you summarize data very quickly. You can watch a video on them here: http://youtu.be/2BCAMj79jwc.