Show Zero Values In A Pivot Table

Recently a colleague was having an issue with their Pivot Table, they claimed some if their data was ‘missing’, a subscriber to my newsletter also had this issue so I thought I would cover it in today’s post.

Let’s say if you have sales data for 12 months for 3 products, but unfortunately some months of the year those products did not sell any units (as in the data table shown below), you would still want to show the data for those products for those months even if none were sold. Check out the difference in the Pivot Tables below.

The months October 10 as well as january, February and April 11 have no A Widgets sold.

Pivot A shows data without zero values.

Pivot B shows the complete data set including those months and products with no units sold.

So, if your data looks a little strange or you think some is missing, then you may likely be unknowingly hiding zero values in your data.

My colleague breathed a sigh of relief, they thought that something had gone seriously worng with their data analysis.

So, if you are creating a pivot table which might offer this type of problem during creation, you can use the “Show Items with No Data” option to make sure that all of the months will appear. This is accessed in Excel 2007 via the following

1. Right clicking in the pivot table column area and selecting Field Settings- Layout and Print- Layout -Show Items with no data
2. Or click in your pivot table, Active Field- Field Settings- Layout and Print- Layout -Show Items with no data.

Comments

My pivot table date range is 4/28/13 to 6/17/13. When I open Field Settings\Layout & Print and check the box “Show Items with no data” It includes dates from 2/1/13 to 6/17/13. Why did the change in field settings include dates outside of my date parameters and how did it decide to add February and March but not January or any other months? Hmmm…

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

Comment

Name *

Email *

Website

Follow How To Excel At Excel

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel?
Click the link below to receive more Excel tips' and my Free Ebook

Sign Up For My FREE Excel Tips Newsletter and receive your own E-book of my Top 50 Excel Tips.