MS Excel Pivot Table Deleted Items Remain – Fix

 

 

 

Microsoft Pivot Tables & Pivot Charts should be in every data visualization toolbox as they are one of the most powerful reporting and analysis features in Microsoft Excel today.

New to Pivot Tables & Pivot Charts, we can help 877-392-3539.

..

The Problem: Your Excel Pivot Table Has Deleted Items Remaining.

How do you fix the problem of having duplicate items remain in your Pivot Tables, read on.

Pivot Tables are one of the most powerful data visualization tools in Microsoft Excel, quickly followed by Pivot Charts and Power BI Dashboards as the most useful tools for the senior management team. These three tools quickly allow you to make sense of what your data is telling you, interactively, with a point-n-click graphical user interface for ease of use. KPIs have met their match with Excel Pivot Tables and BI Dashboards.

Yes, Pivot Tables are a wonderful thing, but they have their downsides. One of them is the fact that they will retain deleted data from the data source, which shows up in filter drop downs and slicers. This can be slightly (up to extremely) annoying and hard to find where to not show pesky deleted items. Knowing how to fix this problem will get you back in love with Excel Pivot Tables once again.  Really, there is no better analysis tool built into Excel than Pivot Tables and Pivot Charts, accessible by all. Power BI Dashboards are not as easily accessible by the masses.

There are two areas where the Pivot Table is most affected – Excel filters and Slicers. You can take care of this in one fell swoop with a Pivot Table setting change, or you can fix this for slicers only. Both are detailed below. I’ve utilized a Microsoft template using Excel 2013 for this example and screenshots.

..

Pivot Table’s Template Example with “Deleted Items Remaining“

The template I started with has a Pivot Table already, which just made it easy to use for this example. Here is what it looks like:

 

There’s some Excel dashboard data up top, then an overall chart, and below that is our Pivot Table with Slicers to its right. Before we get to the fixes, we need to show the problem, and before we do that, well, we need to create it.

..

Adding a New Record to the Excel Pivot Table to Create Problem

To create this particular problem, I’ll just add a new row of data to the data source. Before we get there, you should get an idea of what values are currently in the Pivot Table. Here is the filter drop-down for the AREA field:

..

Notice there are only three unique items listed. We’ll revisit this in a moment. First, let’s create this behavior we’re talking about.

For this particular template used, data input is on the COST INPUT worksheet.

TIP

If the totals row is showing for a table, and you can’t just start typing a new row, select the bottom-right-most cell in the table data body range and press Tab.

 

Now we add a row of data:

So I added a cabinet to the upstairs bathroom. Ooh, and it only cost me $250. Yay! Ok, data added.

If you now look at the pivot table, you won’t automatically see your data.

 

 

 

Microsoft Excel MVP Trainers – One-on-one Microsoft Excel Training

 

 

---

 

TIP

To refresh the Pivot Table, either right-click any cell in it and select Refresh (as seen in the picture below), or go to the PIVOT TABLE TOOLS

ANALYZE ribbon and select Refresh there.

Once you refresh the Pivot Table you should now see the newly added data. We will check the filter drop-down, which should show us a list of unique values from that field.

As you can see, there is now an “Upstairs” item listed for that field.

Delete the record

Down to the nitty gritty. We are going to delete this newly added record of data. So go to the data source table, make sure the new row (or any cell in it) is selected and either right-click -> Delete -> Table Rows, or from the HOME tab, select Delete (in the Cells group) -> Delete Table Rows:

If you now refresh your Pivot Table and look at your filter drop-down menu, you should notice you will still see data for the deleted row from the source menu! In addition, you will see it on any related slicers as well.

Now for the fixes.

---

 

Slicer fix

This is a very simple, yet hidden and somewhat cryptic fix. Quite simply if you select your slicer (any slicer you want this to work on, which, alas, must be done individually) you can get to the slicer settings by either the right-click menu, or the ribbon (SLICER TOOLS

OPTIONS -> Slicer Settings):

This will bring up the slicer settings dialog box. What you’re looking for is in the bottom-right corner, a little check box that says “Show items deleted from the data source”. Uncheck that, click OK and your slicer is now no longer showing phantom data.

---

 

 

Pivot Table fix

Fixing the slicers are nice, but that doesn’t clear these deleted items from the Pivot Table filter drop-downs. To do this we need to go into the Pivot Table Options and look at the Data tab. Here you will see an item that says “Retain items deleted from the data source” with a combobox for you to set the number of items to retain per field:

 

By default it’s in automatic. Just change this to None and click OK. Voila! That’s it, you’re done. No more phantom deleted items showing up in your Pivot Table. The best part of this fix is it takes care of the slicers too. Think of it as a cascade of deleted data. Fix the Pivot Table and it will fix the slicers – all of them (using that data source).

Summary

While Pivot Table’s are a tremendously powerful tool, sometimes the default settings aren’t so helpful. This is one of those ‘Pivot Table’s 101’ things, but is hard to find if you don’t know where it’s at.

 

 

 

 

---