Why does my pivot chart exclude months for certain data items?


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.

Figure A.20. There is no data for May through June, so this pivot chart excludes these months. However, it would be nice if this pivot chart would show all 12 months regardless.


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.

Figure A.21. Clicking on Show Items with No Data will ensure that all months are shown, whether data exists for that month or not.


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.

Figure A.22. It is now clear that the months of May, June, and July have no data.




    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net