Pivot Table Not Showing All Data Problem-Layout Fix

Pivot tables are great for summarizing data, but do you ever notice that there are missing items in a pivot table?

For example, you know there are product records in the source data table, but one product isn’t showing up in the pivot table. How can you troubleshoot and fix the problem to show all data?

Pivot Table Missing Items

There are several reasons why a pivot table data might not show all data, and I covered the most common missing data problems in a previous post.

For example:

Pivot table needs to be refreshed
Incorrect entries in source data table
Some rows or columns not included in pivot table source data
Pivot items collapsed and accidentally hidden

My previous post shows how to check for those problems, and fix them, so click here to see the troubleshooting details.

Not Showing All Data

Last week, someone ran into a different kind of missing data in a pivot table. She had been through all the troubleshooting steps in my previous post, and none of those solutions fixed her pivot table problem.

The pivot table was still not showing all data from the source table – one item was missing!

I’ve made a generic example, to show the problem, in the screen shot below.

Why aren’t the cookies showing, along with the other food categories?

It’s a Filter Problem

I offered to take a quick look at her file, and soon found the problem – a pivot table filter was hiding some of the data.

Pivot filter troubleshooting wasn’t included in my previous article, so I’ve put some steps below.

First, I’ll show you why the applied filter wasn’t obvious
Next, you’ll see a quick way to make the applied filters easier to see
Finally, you’ll see how to fix the filter problem by changing the pivot table layout

1) Why the Filter Wasn’t Obvious

Because of the default pivot table setup, it was difficult to see what was filtered, and that’s why my email friend hadn’t spotted the filter problem.

When you create a new pivot table in Excel, it uses the Compact Layout, by default. That layout has the following features:

all the row fields are in a single column
each field is slightly indented from the previous field
the column heading says Row Labels – no row field names are shown
there is one filter icon at the top of the column, for all the row fields

In my “Missing Cookies” example, I clicked on the heading cell, then clicked the Filter button.

The drop down menu showed there was a filter on the Customer field, and Mega Market was not showing.
There’s no mention of a filter on the Category field.

2) Add Pivot Table Slicers

For a quick way to troubleshoot, and see what filters are applied, you can add Pivot Table Slicers, for one or more of the pivot fields.

Select any cell in the pivot table
On the Excel Ribbon, go to the Insert tab
In the Filters group, click the Slicer command
In the Insert Slicers window, add a check mark for each field that should get a Slicer
Click OK

For this pivot table, I inserted Slicers for the two row fields – Customer and Category

Check the Pivot Slicers

After I clicked OK, the two Slicers appeared on the worksheet.

I adjusted the Slicers’ size and location, so I could see all the items in each Slicer.

With the Slicers on the worksheet, it’s easy to see that the Category field has a filter applied too, and Cookies are not showing.

To fix the missing Cookies problem, just click the Clear Filter icon at the top right of the Category Slicer.

3) Change Pivot Table Report Layout

If you don’t have room for Slicers on your worksheet, another way to see all the filters is to change the pivot table layout.

In addition to the Compact Form, there are two other pivot table report layout options that you can use – Outline Form or Tabular Form.

Usually, I use one of those two layouts, because both of them have the following features:

each row field is in a separate column
column headings show the individual row field names
each column has its own filter button

In the screen shot below, you can see a pivot table with Compact Layout compared to Outline Layout. The Outline layout clearly shows that there is a filter applied in each row field.

Video: Change Pivot Table Report Layout

You can manually change the layout, or use a macro, if you have lots of pivot tables to change.

The video below shows the manual steps, and there are more details and layout tips on my Contextures site.

In the video, you’ll see the 3 pivot table report layout options, and the different settings available in each layout.

The layout details are described in detail on the Pivot Table Report Layout page on my Contextures site, along with the benefits and limitations of the different layouts

More Pivot Table Layout Tips

Here are a few more articles on Pivot Table layout settings and tips, that you can find on my Contextures website.

Pivot Field Layout Changes: Add or remove fields in pivot table. Move fields to different locations in pivot table. Change pivot field headings. Show Value headings at the left, with row labels

Pivot Table Format: Apply formatting scheme from PivotTable Styles gallery. Create custom Pivot Table Style. Copy custom styles to different Excel file. Change pivot table labels.

PivotTable Field List: Use the pivot table field list to add, remove and move pivot fields to the worksheet layout. Sort the field list, so it’s easier to find fields in a long list.

Pivot Field Settings: Hide and show subtotals. Show items with no data. Repeat all item labels for pivot field. Insert blank rows for spacing. Sort pivot items. Show new items in drop down.

Pivot Table Options: Change pivot table name. Change value for all error cells. Show zero in empty cells. Show or hide Values row. Set pivot table layout and formatting options.

_______________