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.
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.
- Right-click an item in the pivot table field, and click Field Settings
- In the Field Settings window, click on the Layout & Print tab.
- Add a check mark in the ‘Show items with no data’ box.
- Click OK
After you’ve changed that setting, all the products are listed under each customer name, showing the number of units sold.
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.
- Right-click a pivot table cell, and click PivotTable Options
- On the Layout & Format tab, add a check mark to “For empty cells show:”
- In the text box for that setting, type a zero, then click OK
After you change the setting, the empty cells show a zero.
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:
- In the source data, add a record with Markers as the product, and 0 as the quantity
- Refresh the pivot table, to update it with the new data
- Right-click a cell in the Product field, and click Field Settings.
- On the Layout & Print tab, add a check mark in the ‘Show items with no data’ box.
- Click OK
After you have added the fake record, refresh the pivot table, so the new data appears.
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