Pivot Tables "break" when changing column headings

[laxster]

I am having an odd problem with pivot tables. Basically, when I try to modify the column heading titles in a data sheet which feeds to a pivot table, the pivot tables "break" when I try to refresh the data, and a significant amount of data in them doesn't appear anymore.

Why does this happen, and how can I fix it?

 

[noboffinme]

Hi Laxster

The answer is the Pivot Table no longer recognises what fields to use as the names have been changed, so it will either drop that field out of the table on the next refresh or just not work.

You have 2 options;

Delete it & start again or you can 'deconstruct' it temporarily & rebuild it.

If it's a lot of work to completely redo, do the below;

1. Right click inside the Pivot & select Pivot Table Wizard >> Layout,

2. Drag any field(s) that have been affected by the changes of spelling

out of the pivot design, leave everything else there.

3. Select 'OK' & 'Finish'

4. Now correct the spelling of the heading to suit what you need

5. Go back to the Layout of the Pivot Table & drag that field back in the position it originally was.

hth