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
_____________________________
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?
this is perfect. just what I am looking for. thank you very much for this. big big help.
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
THANK YOU! This will save us hours of re-formatting in other documents. 🙂