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


"EricK" wrote:

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

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!

"Ron Coderre" wrote:

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


"EricK" wrote:

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

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

"Ron Coderre" wrote:

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


"EricK" wrote:

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

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

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

XL2002, WinXP


"EricK" wrote:

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

"Ron Coderre" wrote:

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


"EricK" wrote:

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

I have that tip in my book, and just added it to the web page too.

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

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

XL2002, WinXP


"EricK" wrote:


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

"Ron Coderre" wrote:


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


"EricK" wrote:


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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html