Pivot table hanging on to deleted values

Hi, I'm trying to set up a template that contains a formatted pivot table
(e.g. presents sums that are formatted as dates). It looks fine, but the
problem I'm getting is that the field across the top is "holding on" to
values that no longer exist in the source table. I tried removing the field,
saving the file, and then putting it back in, but it seems to make no
difference. I don't want to recreate the table from scratch because I'll
lose all the formatting, but I don't want to confuse users with options on
the field list that aren't there.

Any suggestions on how to reset this?

Thanks,
Geoff.

Did you refresh the table? Pivot tables (and charts) are the exception in
Excel: they do NOT automatically reflect changes in your underlying data.
Right-click in the pivot table and Refresh Data (the big exclamation mark).
Does that clear up the issue?

"Geoff C" wrote:

> Hi, I'm trying to set up a template that contains a formatted pivot table
> (e.g. presents sums that are formatted as dates). It looks fine, but the
> problem I'm getting is that the field across the top is "holding on" to
> values that no longer exist in the source table. I tried removing the field,
> saving the file, and then putting it back in, but it seems to make no
> difference. I don't want to recreate the table from scratch because I'll
> lose all the formatting, but I don't want to confuse users with options on
> the field list that aren't there.
>
> Any suggestions on how to reset this?
>
> Thanks,
> Geoff.

Sorry, not making my question clear. Refreshing makes no difference. The old
values don't appear in the actual table, but they do appear in the drop down
list that is attached to the field name (the one where you can click certain
values "off").

"bpeltzer" wrote:

> Did you refresh the table? Pivot tables (and charts) are the exception in
> Excel: they do NOT automatically reflect changes in your underlying data.
> Right-click in the pivot table and Refresh Data (the big exclamation mark).
> Does that clear up the issue?
>
> "Geoff C" wrote:
>
> > Hi, I'm trying to set up a template that contains a formatted pivot table
> > (e.g. presents sums that are formatted as dates). It looks fine, but the
> > problem I'm getting is that the field across the top is "holding on" to
> > values that no longer exist in the source table. I tried removing the field,
> > saving the file, and then putting it back in, but it seems to make no
> > difference. I don't want to recreate the table from scratch because I'll
> > lose all the formatting, but I don't want to confuse users with options on
> > the field list that aren't there.
> >
> > Any suggestions on how to reset this?
> >
> > Thanks,
> > Geoff.

Hi Geoff

Drag the offending Field out of the PT.
Refresh the PT.
Drag the Field back to where you wanted it.

Regards

Roger Govier


Geoff C wrote:
> Sorry, not making my question clear. Refreshing makes no difference. The old
> values don't appear in the actual table, but they do appear in the drop down
> list that is attached to the field name (the one where you can click certain
> values "off").
>
> "bpeltzer" wrote:
>
>
>>Did you refresh the table? Pivot tables (and charts) are the exception in
>>Excel: they do NOT automatically reflect changes in your underlying data.
>>Right-click in the pivot table and Refresh Data (the big exclamation mark).
>>Does that clear up the issue?
>>
>>"Geoff C" wrote:
>>
>>
>>>Hi, I'm trying to set up a template that contains a formatted pivot table
>>>(e.g. presents sums that are formatted as dates). It looks fine, but the
>>>problem I'm getting is that the field across the top is "holding on" to
>>>values that no longer exist in the source table. I tried removing the field,
>>>saving the file, and then putting it back in, but it seems to make no
>>>difference. I don't want to recreate the table from scratch because I'll
>>>lose all the formatting, but I don't want to confuse users with options on
>>>the field list that aren't there.
>>>
>>>Any suggestions on how to reset this?
>>>
>>>Thanks,
>>>Geoff.

I'm facing the same problem and for some reason only in some cases it seems
to work. Is there a more powerful way to reset a pivot table without loosing
any formatting that you already have applied. A "Drop Down List refresh" or
"Items with no data refresh"?

Andy

"Roger Govier" wrote:

> Hi Geoff
>
> Drag the offending Field out of the PT.
> Refresh the PT.
> Drag the Field back to where you wanted it.
>
> Regards
>
> Roger Govier
>
>
> Geoff C wrote:
> > Sorry, not making my question clear. Refreshing makes no difference. The old
> > values don't appear in the actual table, but they do appear in the drop down
> > list that is attached to the field name (the one where you can click certain
> > values "off").
> >
> > "bpeltzer" wrote:
> >
> >
> >>Did you refresh the table? Pivot tables (and charts) are the exception in
> >>Excel: they do NOT automatically reflect changes in your underlying data.
> >>Right-click in the pivot table and Refresh Data (the big exclamation mark).
> >>Does that clear up the issue?
> >>
> >>"Geoff C" wrote:
> >>
> >>
> >>>Hi, I'm trying to set up a template that contains a formatted pivot table
> >>>(e.g. presents sums that are formatted as dates). It looks fine, but the
> >>>problem I'm getting is that the field across the top is "holding on" to
> >>>values that no longer exist in the source table. I tried removing the field,
> >>>saving the file, and then putting it back in, but it seems to make no
> >>>difference. I don't want to recreate the table from scratch because I'll
> >>>lose all the formatting, but I don't want to confuse users with options on
> >>>the field list that aren't there.
> >>>
> >>>Any suggestions on how to reset this?
> >>>
> >>>Thanks,
> >>>Geoff.
>

Hi Andy

Debra Dalgleish has some program methods for clearing old items from the list at
http://www.contextures.com/xlPivot04.html

Regards

Roger Govier


MishkinAB wrote:
> I'm facing the same problem and for some reason only in some cases it seems
> to work. Is there a more powerful way to reset a pivot table without loosing
> any formatting that you already have applied. A "Drop Down List refresh" or
> "Items with no data refresh"?
>
> Andy
>
> "Roger Govier" wrote:
>
>
>>Hi Geoff
>>
>>Drag the offending Field out of the PT.
>>Refresh the PT.
>>Drag the Field back to where you wanted it.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Geoff C wrote:
>>
>>>Sorry, not making my question clear. Refreshing makes no difference. The old
>>>values don't appear in the actual table, but they do appear in the drop down
>>>list that is attached to the field name (the one where you can click certain
>>>values "off").
>>>
>>>"bpeltzer" wrote:
>>>
>>>
>>>
>>>>Did you refresh the table? Pivot tables (and charts) are the exception in
>>>>Excel: they do NOT automatically reflect changes in your underlying data.
>>>>Right-click in the pivot table and Refresh Data (the big exclamation mark).
>>>>Does that clear up the issue?
>>>>
>>>>"Geoff C" wrote:
>>>>
>>>>
>>>>
>>>>>Hi, I'm trying to set up a template that contains a formatted pivot table
>>>>>(e.g. presents sums that are formatted as dates). It looks fine, but the
>>>>>problem I'm getting is that the field across the top is "holding on" to
>>>>>values that no longer exist in the source table. I tried removing the field,
>>>>>saving the file, and then putting it back in, but it seems to make no
>>>>>difference. I don't want to recreate the table from scratch because I'll
>>>>>lose all the formatting, but I don't want to confuse users with options on
>>>>>the field list that aren't there.
>>>>>
>>>>>Any suggestions on how to reset this?
>>>>>
>>>>>Thanks,
>>>>>Geoff.
>>

Hi Roger,

ive come across the same problem but for some reason the solutions suggested
by Debra dont work.

Any idea

Regards

"Roger Govier" wrote:

> Hi Andy
>
> Debra Dalgleish has some program methods for clearing old items from the list at
> http://www.contextures.com/xlPivot04.html
>
> Regards
>
> Roger Govier
>
>
> MishkinAB wrote:
> > I'm facing the same problem and for some reason only in some cases it seems
> > to work. Is there a more powerful way to reset a pivot table without loosing
> > any formatting that you already have applied. A "Drop Down List refresh" or
> > "Items with no data refresh"?
> >
> > Andy
> >
> > "Roger Govier" wrote:
> >
> >
> >>Hi Geoff
> >>
> >>Drag the offending Field out of the PT.
> >>Refresh the PT.
> >>Drag the Field back to where you wanted it.
> >>
> >>Regards
> >>
> >>Roger Govier
> >>
> >>
> >>Geoff C wrote:
> >>
> >>>Sorry, not making my question clear. Refreshing makes no difference. The old
> >>>values don't appear in the actual table, but they do appear in the drop down
> >>>list that is attached to the field name (the one where you can click certain
> >>>values "off").
> >>>
> >>>"bpeltzer" wrote:
> >>>
> >>>
> >>>
> >>>>Did you refresh the table? Pivot tables (and charts) are the exception in
> >>>>Excel: they do NOT automatically reflect changes in your underlying data.
> >>>>Right-click in the pivot table and Refresh Data (the big exclamation mark).
> >>>>Does that clear up the issue?
> >>>>
> >>>>"Geoff C" wrote:
> >>>>
> >>>>
> >>>>
> >>>>>Hi, I'm trying to set up a template that contains a formatted pivot table
> >>>>>(e.g. presents sums that are formatted as dates). It looks fine, but the
> >>>>>problem I'm getting is that the field across the top is "holding on" to
> >>>>>values that no longer exist in the source table. I tried removing the field,
> >>>>>saving the file, and then putting it back in, but it seems to make no
> >>>>>difference. I don't want to recreate the table from scratch because I'll
> >>>>>lose all the formatting, but I don't want to confuse users with options on
> >>>>>the field list that aren't there.
> >>>>>
> >>>>>Any suggestions on how to reset this?
> >>>>>
> >>>>>Thanks,
> >>>>>Geoff.
> >>
>