Why are a couple of pivot items missing from this pivot table? The source data has these employees marked as “Yes”, but that information isn’t showing up in the pivot table summary. How can we troubleshoot pivot items missing from our pivot table?
Last week, a friend asked me for help with her pivot table — why did some pivot items disappear? We were troubleshooting the problem over the phone, so I couldn’t figure it out at first.
Here is a screen shot of a sample pivot table that shows the same problem, with fake data.
Sometimes data isn’t visible because the pivot table hasn’t been refreshed recently. So, I asked my friend to right-click on the pivot table, and click Refresh, just in case that was the problem.
Unfortunately, that didn’t solve the problem. The pivot items didn’t magically appear after the refresh. What else can we do to troubleshoot pivot items missing from a pivot table?
The next thing I asked my friend to do was to check the source data, to see what was entered in the table for those people.
That wasn’t the problem – the Attending column had a “Yes” for both Ida Gray and Ken Gray.
But, just to be cautious, she copied a Yes from another row, and pasted it in the problem rows. Then, she refreshed the pivot table again, and still no luck.
So the data was in the source table, but not in the pivot table. Strange.
Next, I asked my friend to confirm that the pivot table was connected to the correct table – there were a couple of other tables in the workbook.
She clicked the Analyze tab on the Excel Ribbon, then clicked the Change Data Source command.
The Change PivotTable Data Source dialog box opened, and the correct table was highlighted. So, she clicked Cancel, to close the dialog box.
Finally, it dawned on me — you can expand or collapse the pivot fields and pivot items. Maybe something had been accidentally collapsed.
I asked my friend to try these steps:
And that worked! The “Yes” pivot items finally appeared for Ida Gray and Ken Gray. My friend was very happen, and got back to work on her project.
I decided to dig a bit deeper though, into the secrets of how to expand and collapse pivot fields and pivot items.
The only remaining mystery was how those pivot items disappeared in the first place. My friend hadn’t intentionally hidden them.
I remembered that she had asked how to hide the pivot table’s plus and minus signs, earlier in our phone call. Those are the Expand/Collapse buttons, and here is a screen shot of the table with those buttons showing:
If you click one of the minus signs, everything in the fields below that is hidden. For example, if you click the minus sign at the left of East, it collapses — all the names for East are hidden.
Later, you can click the plus sign at the left of East to expand it again, and show all the hidden information.
The first name (NameF) and last name (NameL) fields have plus and minus signs too. If you click the minus sign for Ida, the “Gray” and “Yes” disappear. Later, click the plus sign to show them again.
Hmmm…now we’re getting closer to solving the mystery. What happens if you click the minus sign for the last name in the Ida Gray row?
It doesn’t just hide the Attending information in the Ida row – it hides the Attending information for anyone with “Gray” in the NameL field.
So that’s why both Ida Gray and Ken Gray would have missing information. The “Gray” last name was accidentally collapsed, before the Expand/Collapse buttons were hidden.
If you want to show or hide the Expand/Collapse buttons, follow these steps:
If the Expand/Collapse buttons have been hidden, you can still expand or collapse the pivot items. It’s easy (maybe too easy!) – just double-click a cell in one of the outer pivot fields, to collapse or expand that item.
That double-click trick also works if the plus/minus signs are showing, so be careful when you double-clicking in a pivot table!
NOTE: You can’t collapse the innermost field if you double-click on it. Instead,
To find more tips the pivot table expand and collapse feature, go to the Expand and Collapse a Pivot Table page on my Contextures website. There is a free workbook that you can download, to follow along with the tips to troubleshoot pivot items missing.
