Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.