This field has no items in a pivot table but it does for filtering - help!

Question

_{Wednesday, June 29, 2016 12:30 PM}

Hi

I have an Excel spreadsheet which has a tab with data in including numbers. One column in WIP greater than 12 months, another is Total Exposure.

They both have the same formatting - number decimal places 2.

On new tab I have created a pivot table based on these columns and selected them to appear in the values (bottom right hand corner).

In the pivot field table list I can right click on the upturned triangle on WIP greater than 12 months, and a filter list appears. I try the same thing on the total exposure, as that is the one I want to filter on and it says, "this field has no items" which is not true.

I have checked the formatting through to the pivot table and it looks ok, what am I doing wrong?

As always thank you for your help.

Best wishes

Ros

All replies (3)

_{Thursday, June 30, 2016 8:39 AM ✅Answered}

Hi Ros,

Did your PivotTable contain any empty cells?

Did the PivotTable display all data from source Table?

Did you type the wrong filter data?

First please confirm if your Table contain empty cells, if yes please go to PivotTable options- Layout & Format- For empty cells show: 0

Then I suggest you provide a screenshot or file about your problem, I will help you check the file.

You can provide the screenshot or file via OneDrive.

Any updates please let me know and I'm glad to help you.

Regards,

Emi Zhang
TechNet Community Support

Please mark the reply as an answer if you find it is helpful.

If you have feedback for TechNet Support, contact [email protected].

_{Thursday, October 13, 2016 3:59 AM}

Hi

I have a very similar issue. In my case I have an Excel spreadsheet that contains a number of pivot tables. This works fine. 6 months later, when new data has been collected, I then deleted the old pivot source data and pasted in the new data, changed the source data range (just to add more rows) refreshed all pivot tables and slicers, and expected this to work. However it did not. One filed (Forename), when selected from the Pivot Table Fields on left of screen, shows the error message "This field has no items" despite there being 3000+ Forenames in the source data. I have refreshed many times, changed the source data heading from Forename to FirstName (the heading changes in the Pivot table, but still no data) and back again, deleted all data and added it again, etc. Still no change. I have found macros online that supposedly refresh all caches etc, but this does not work.

Do you have any other suggestions I could try? I really do not want to rebuild this spreadsheet every 6 months.

Thanks

_{Thursday, October 13, 2016 4:29 AM}

Just letting you know I have found a solution to my issue above. Typically, after days of working on it, I found the solution just minutes after posting here!

The solution was to include the Forename field in the pivot table (it was not used in the pivot table, just in slicers). So by selecting the Forename field as an item to show in the pivot table, it has populated the data, and now removing that field again from the pivot table means the slicer can access the data.