How to refresh dropdown lists in PivotTables?

[Guest]

The dropdown lists in our PivotTables are not refreshing -- they still show
items that no longer exist in the original data.

How do I refresh the Pivot Table so these items disappear? Just clicking on
Refresh doesn't clear the dropdown lists.

Thanks.

 

[Debra Dalgleish]

There are instructions here for clearing old items from a PivotTable
dropdown list:

http://www.contextures.com/xlPivot04.html

 

[Guest]

This is terrific! Thanks very much!

The manual method didn't work for me. But the macro did.

Thanks again! I will definitely explore your site some more.


Seraphim

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know which method worked for you.

 

[Guest]

I have a similar problem with my pivot table.

Whenever I click on refresh data an error message displays that PivotTable
field name is not valid. I followed your website contexture on defining the
database word for word and it worked but now I try to refresh the data I get
the error message.

Please advise.

Best Regards,
EMoorhead

 

[Debra Dalgleish]

Perhaps you included a blank column when you created the database range.

 

[Guest]

Thanks it was the header of the columns wadn't included. BTW how do you sort
without rearranging the header in the sort? It gets sorted with all the data
and that confuses the pivot table refresh.

 

[Debra Dalgleish]

If you format the heading row differently than the rest of the table,
e.g. Bold font, it should automatically be detected. Then it will remain
at the top after the sort.

Or, choose Data>Sort, and specify that a header row is included.

 

[Guest]

Thanks I will try that. BTW what webpage editing software do you use. Your
pages are clearly illustrated.

Best Regards,
EMoorhead

 

[Debra Dalgleish]

I use Dreamweaver, and SnagIt for the screenshots.

 

[Guest]

The bold font was already done and it still sorted. Confusing issue.

Best Regards,
EMoorhead
http://www.estandardtime.com