Pivot Table Data Crunching for Microsoft Office Excel 2007

Problem

If you are plotting trends over time with a pivot chart, and there is no occurrence of a particular month, the pivot chart excludes that month all together. However, in most cases, you will want to show your audience a placeholder for that month, illustrating that there is no data there.

For example, the chart in Figure A.20 shows a trend from January through December, but you will notice that May through June is not shown.

Solution

The solution to this problem is to set the pivot field to show all items with no data.

In this example, you would double-click on In Balance Date and then place a check in the Show Items with No Data check box, as shown in Figure A.21.

After your pivot field is set to show all items, your pivot table will show all 12 months, regardless of whether there is data in those months. The net effect is that you see May, June, and July, even though there is no data in those months, as shown in Figure A.22.