How to Show Missing Items in Pivot Table

When you create a pivot table from your source data, it only shows the items that have data. For example, if you put Customers and Products in the Row area, it only lists the items that each customer has bought.

pivotmissingitems02

Show Items With No Data

In the pivot table shown above, some items have bought several different products, and some customers only bought one type of product.

If you’d like to see each product listed for each customer, you can change a setting for the product field.

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

After you’ve changed that setting, all the products are listed under each customer name, showing the number of units sold.

pivotmissingitems04

Show Zeros in Empty Cells

For the products that a customer hasn’t bought, the Units column shows a blank cell. If you’d like to see a zero there, you can change a pivot table setting.

  1. Right-click a pivot table cell, and click PivotTable Options
  2. On the Layout & Format tab, add a check mark to “For empty cells show:”
  3. In the text box for that setting, type a zero, then click OK

pivotmissingitems05

After you change the setting, the empty cells show a zero.

pivotmissingitems06

Show Missing Data

The “Show Items With No Data” setting can only show items that are included in the source data at least once. If you start selling a new product – Markers – but no customers have ordered it yet, it won’t appear in the pivot table.

If you’d like it to appear, you can add a fake record to the source data:

  1. In the source data, add a record with Markers as the product, and 0 as the quantity
  2. Refresh the pivot table, to update it with the new data
  3. Right-click a cell in the Product field, and click Field Settings.
  4. On the Layout & Print tab, add a check mark in the ‘Show items with no data’ box.
  5. Click OK

pivotmissingitems07

After you have added the fake record, refresh the pivot table, so the new data appears.

pivotmissingitems08

Video: Show Items With No Data

To see the steps for showing pivot table items with no data, please watch this short video. And please visit my Contextures website, for more information on pivot table field settings.

Or watch on YouTube: Show Missing Items in Excel Pivot Table

_____________________________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in pivot fields. Bookmark the permalink.

4 Responses to How to Show Missing Items in Pivot Table

  1. Pingback: Excel Roundup 20131125 | Contextures Blog

  2. JRauz says:

    I have a scenario for you which is similar to the example but a bit different. Let’s flip the example so the Customer is actually the Seller. And we are buying the products from them.

    What if AnyCo doesn’t sell Binders? So the source data has no instance of AnyCo with Binders associated with it. In this case, when you select “Show items with no data”, Binders will still show up under AnyCo even though it doesn’t make sense because AnyCo doesn’t sell them.

    How can you get only the associated products to show up under the Seller and not all products listed in the source data?

  3. marco chan says:

    this is perfect. just what I am looking for. thank you very much for this. big big help.

  4. SteveJ says:

    Can anyone help with this problem: calculations in created calculated fields erroring-out when I update my base data. I am using an offset formula to ensure all data in rows and columns are captured for the pivot table data. I have read that sometimes when additional columns are inserted in the base data it causes this to occur, but in the most recent update, I added no additional columns. I’d love to know if anyone has found a way to protect calculations from disappearing on updates (btw, the field remains, but blank). Thanks, Steve

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>