I want to show nonexistent data in pivot table

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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...
1605192447624.png


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).
1605192486409.png

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?
1605192575516.png

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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,422
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,674
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top