Show Missing Items in Excel Pivot Table

A pivot table shows a summary of the source data, and in the screen shot below you can see all the products that were sold, and the quantities for each colour.

showallpivotitems01

When you filter the pivot table, some of that data might disappear. For example, in the pivot table shown below, the Customer field has been filtered to show XYZ Inc. orders.

They didn’t order all the colours for each product, so the pivot table is smaller. It’s only showing the products and colours that XYZ Inc. ordered.

showallpivotitems02

Instead of having the pivot table change size, so it shows only the applicable items, you can change a field setting, to keep it consistent.

Show All Items in Excel 2010

To make all the items appear, even if the pivot table is filtered, you can change a Layout setting in the pivot table. This setting applies to a single field, so you’ll have to make the following change to each field in which you want to see all the items.

To show all items for a pivot field in Excel 2010 or 2007:

  1. Right-click an item in the pivot table field, and click Field Settings
  2. In the Field Settings dialog box, click the Layout & Print tab.
  3. Check the ‘Show items with no data’ check box.
  4. Click OK

showallpivotitems03

After the setting is changed, all the colours are listed for each product, even if they weren’t sold for the selected customer.

showallpivotitems04

To see the instructions for changing the setting in Excel 2003, please go to my Contextures website: Pivot Table Field Settings

Watch the Video

Watch this video to see the steps for changing the layout setting in Excel 2010 or 2007.

_____________________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.