Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using Excel with a SQL data connection to generate a Pivot Chart.

The issue I'm having involves the data filters at the top of each column, allowing you to filter the visible values. Upon creation of the worksheet, the selectable values in the data filters is correct. However, if an item is deleted from the database and the Excel data connection data is refreshed, the value that has been removed is still visible as a data filter. (Obviously selecting it results in an empty table since there's no actual data remaining.)

How can I fix this behavior, so a data refresh also updates the list of possible data filter values?

share|improve this question
up vote 7 down vote accepted

In Excel 2007 and above (maybe 2003 not sure)

  1. Right-click a cell in the pivot table
  2. Click on PivotTable options
  3. Select "None" in "Retain items deleted from the datasource".

enter image description here

share|improve this answer
Good answer, good use of graphics – Dave Oct 12 '12 at 10:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .