XL5: Pivot Table Displays Duplicate Months After Grouping

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you create a pivot table, and you group items in the table by month, not all of the months are displayed, and some months are duplicated.

CAUSE

This behavior occurs if the date used in the Starting At box in the Grouping dialog box is the 31st day of a month. The problem occurs because not all months have a 31st day. In this case, the 31st day wraps to the 1st day of the next month, creating a duplicate month label, as well as leaving out the label for the month that doesn't have a 31st day.

For example, if the dates in your pivot table include Jan, Feb, and Mar of 1994, and you group your data by months, starting at 1/31/94, the resulting table has the month labels Jan, Mar, and Mar, where the first Mar label actually represents the data for February.

WORKAROUND

To avoid this behavior when you use the Grouping dialog box to group data in your pivot table by months, make sure that the Starting At box contains a date other than the 31st of a month. If the 31st of a month is displayed in this box, and the Auto check box next to this box is selected, clear the Auto check box, and enter a starting date that is not the 31st of a month.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.

REFERENCES

For An Overview Of Grouping Items In A Pivot Table, choose the Search button in Help and type:

   pivot table items, grouping and ungrouping