I want to show nonexistent data in pivot table

[BloodyBill]

Hypothetical situation: You're selling fruit, and outputting sales data from a database as a CSV, and then fetching that data into a data sheet in Excel.

You have a pivot table in which you display date and sales for apples, oranges, peaches and pears. Thus...

One day, you fetch your data from the daily CSV export, but the pivot shows no row for oranges (because there's no oranges sales in the data set).

How can I make the pivot table show an row of zeros to show a lack of sales, if the pivot doesn't have any oranges sales data to work with?

I know I could add something to the end of the data set, but that data set gets completely replaced with every "refresh data." So, I'd have to manually add something every day. I'm looking for an automatic solution.

Also, how can I make sure that the oranges row of zeroes falls in the same place every time (under apples)? Alphabetical is fine, I just don't want it to be added to the bottom row in the pivot table.

Thanks, Bill

 

[denzo36]

The only way for this to work would be if the data the pivot table was built from contained all possible fruits that could have sales - if that was the case you could right click field in the pivot table and go to field settings -> layout and print -> check the 'shows items with no data' box

If it's the case that when the data refreshes and say there were no sales for oranges and your data hence has no rows for oranges, then the pivot table can't show it as it's not in the raw data.

Hopefully this makes sense

 

[RoryA]

In the field settings, on the 'Layout and Print' tab, check the 'Show items with no data' option. As long as your pivot table is set to remember old data (which is the default) and all the fruits have been there at some point, you should get them all kept in the pivot table.