Pivot Table - "Show Items With No Data" Creates New Unwated Column Headings

[wahbob]

Hello,

Wondered if anyone could help me with the following.

I have multple pivot tables all looking at the same source data.
All the pivot tables are set up in Tabular Format with one field as a filter, one field as both a Column Label and a Values (as a count).

The Column Label colum is looking at a column containing a formula in the data source which, based all inputs, will return one of 5 outputs.

When I select different filter settings, some of the column headings have no lines of data, and therefore the columns headers with no data disappear from the pivot table.

When I click on a Column Label field, go to Field Settings, and select "Show Items With No Data" the columns with no data return as desired.

However, additional columns also appear that do not exist anywhere in the original data, so no matter what filter I had on, this data does not exist.

Has anyone experienced this, and how can I resolve this to only show blank data columns labels for the data that actually exists in the data source?

Regards

Rob

[xlnitwit]

Hi,

Did the phantom columns ever appear in the data? If they did, in the pivot table options (right-click the table and choose Pivot Table options), select the Data tab and change the option that says "Number of items to retain per field" to None.

[wahbob]

xlnitwit,

I don't believe they ever did, however that approach has worked!!

Learned something new! Cheers for your help!

Rob