How to Troubleshoot Pivot Items Missing From Excel Pivot Table

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?

Why Did Pivot Items Disappear?

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.

• The Attending column is empty for Ida Gray and Ken Gray.
• The correct information appears for other employees – you can see “No”, “Yes” and “(blank)” responses.

Troubleshoot Pivot Items Missing

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?

What’s In the Source Data

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.

• Perhaps there was a space character in the Attending column, and that was showing in the pivot table.
• Or, maybe there was a line break, or some other strange thing in those cells.

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.

Confirm the Data Source

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.

Expand the Pivot Fields

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:

• Select one of the pivot items in the outermost pivot field (Region).
• On the Excel Ribbon, click the Analyze tab
• Click the Expand Field command (if the Excel window is narrow, you might not see the words, just the icon)

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.

How Did the Pivot Items Accidentally Collapse?

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.

Collapse A First Name

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.

Collapse a Last Name

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.

How to Hide the Expand/Collapse Buttons

If you want to show or hide the Expand/Collapse buttons, follow these steps:

• Right-click a pivot table cell, and click PivotTable Options
• Click the Display tab
• In the Display section, add or remove the check mark for “Show expand/collapse buttons”
• Click OK to close the dialog box

Expand or Collapse Without the Buttons

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,

• If there are Value fields, the Show Detail dialog box will appear.
• If there are no Value fields, you’ll hear a warning sound, and nothing happens in the pivot table

More Expand/Collapse Tips

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.

_____________

Save