Removing entry from pivot table field list in Excel 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Suppose you have data table which looks like this:
Name Hours
James 3
Dave 4
James 5
David 2

and you form a pivot table from the data you will get three different names
(as you should). If you then realise that Dave and David are the same person
so you overwrite "Dave" with "David" in the source area, then when you
refresh the pivot table only two names appear, but if you click on the drop
down arrow in the pivot table field, the original name is still there even
though it no longer appears in the source table. The name also appears in the
pivot table if the "show data for empty rows" checkbox (or whatever it's
called) is ticked.

Is there anyway to get rid of that obsolete entry without redoing the entire
pivot table? I have tried loads of obvious tactics and none of them seem to
work.

Thanks,

Eric
 
See Debra Dalgleish's coverage of that situation at her website:
http://contextures.com/xlPivot04.html

Alternatively, you could use the Pivot Play add-in (available at her site)
to clear them with the click of a button.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Thanks.

I did try an internet search but I probably wasn't using quite the correct
words!

Interestingly I did try dragging the field to other parts of the pivot table
but not out of the pivot table altogether. When I dragged it to the "page"
part the spurious entries disappeared only to reappear when I dragged it back!
 
This is just a note for anyone who finds that the proposed methods don't work:

When I first tried these, they didn't work on my pivot table. By messing
around with it, I discovered that it was because the field was grouped. By
ungrouping it completely, I could remove the obsolete data points.

Eric
 
Thanks for the update. We'll have to keep that situation in mind when the
usual methods don't work.

***********
Regards,
Ron

XL2002, WinXP
 
I have that tip in my book, and just added it to the web page too.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question on the occupied field of the pivot table in excel spreadsheet 0
Excel Pivot Tables 1
Pivot Tables 4
Excel 2007 pivot table source table 1
Pivot Table Missing Field List 7
pivot 2
Can't open pivot table field list 1
about pivot table 1

Back
Top