Problem: I built a pivot table to show sales by month for one customer. For the large customers, I get all 12 months.
- All 12 months appear.
But for the smaller customers, I don't see all of the months. I was thinking of adding 12 dummy records for every customer, one per month, but with 300 customers, that would be 3600 fake records just to solve this stupid problem.
- Months without sales are missing.
Strategy: Select a cell in the data column. Click the Field Settings icon in the Options tab. This time, go to the second tab in the dialog, called Layout and Print. On that tab, click Show Items With No Data.
- Strange entries in row 4 & 17.
Gotcha: You are not done yet. The months now appear, but they are empty cells instead of zero. Also, a strange entry appears at the top and the bottom of the data set. There are no records in the data set before 1/1/2014, so this is a pure annoyance from Microsoft.
To fix the empty cells, right-click the pivot table and choose Options. Fill in the For Empty Cells Show box with a zero.
- Replace empty cells with zero.
Go to the filter dropdown for Dates. Uncheck the <1/1/2014 and the >12/29/2014 entries.
- Turn off the < and > values.
The result: a pivot table that will show all 12 months for every customer.
- Easier than adding 3,600 fake zero records to the data.