XL98: PivotTable Appears Incorrect If Data Contains Merged Cells

ID: Q178432

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you create a PivotTable in a worksheet in Microsoft Excel 98 Macintosh Edition, the following problems may occur:

The row fields or column fields contain extra "(blank)" items.

Most of the data in the PivotTable is aligned with the "(blank)" row or column fields instead of with the correct row or column fields.

CAUSE

This problem may occur if the list of data used to create the PivotTable contains merged cells.

WORKAROUND

To work around this problem, unmerge any merged cells in the list of data used to create the PivotTable, fill the unmerged cells with field data, and update the PivotTable.

The "More Information" section of this article contains an example of the problem.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The problem described in the "Symptoms" section of this article occurs because of the way that PivotTables in Microsoft Excel 98 Macintosh Edition retrieve data from cells in a worksheet. When you create a PivotTable, the PivotTable ignores any merged cells in the list of data and retrieves information stored in the individual cells. Because the cells are blank, they appear in the PivotTable as blank fields.

Example

The following steps demonstrate the problem in Microsoft Excel 98 Macintosh Edition:

Create a new workbook. In Sheet1, enter the following data:


      A1:  Widget Type   B1:  Quarter   C1:  Sales
      A2:  Alpha         B2:  Q1        C2:  1
                         B3:  Q2        C3:  2
                         B4:  Q3        C4:  3
                         B5:  Q4        C5:  4
      A6:  Bravo         B6:  Q1        C6:  5
                         B7:  Q2        C7:  6
                         B8:  Q3        C8:  7
                         B9:  Q4        C9:  8
      A10: Charlie       B10: Q1        C10: 3
                         B11: Q2        C11: 4
                         B12: Q3        C12: 5
                         B13: Q4        C13: 6

Note that cells A3:A5, A7:A9, and A11:A13 are blank.

Select cells A2:A5. On the Formatting toolbar, click Merge And Center. Repeat this step with the A6:A9 and A10:A13 cell ranges.

Select cell B5. On the Data menu, click PivotTable Report.

In the PivotTable Wizard, follow these steps:
1. In step 1 of the wizard, click Next.
2. In step 2 of the wizard, click Next.
3. In step 3 of the wizard, drag the Widget Type field to the ROW area in the diagram. Drag the Quarter field to the COLUMN area in the diagram. Drag the Sales field to the DATA area in the diagram. Click Next.
4. In step 4 of the wizard, click Existing Worksheet. Select cell A15 in the worksheet, and then click Finish.

The following PivotTable appears in the worksheet:


   Sum of Sales   Quarter
   Widget Type    Q1        Q2        Q3        Q4        Grand Total
   Alpha                1                                           1
   Bravo                5                                           5
   Charlie              3                                           3
   (blank)                       12        15        18            45
   Grand Total          9        12        15        18            54

Note that all of the items in the PivotTable are grouped in the "(blank)" Widget Type, except items in rows two, six, and ten in the worksheet. These items are next to the original data in the merged cells so that they are aligned with the correct Widget Types.

The items grouped into the "(blank)" Widget Type are in the same rows as the blank cells in step 1.

Correcting the Example:

To correct the problem in the example, follow these steps:

In the worksheet, click cell A2. On the Format menu, click Cells. Click the Alignment tab. Clear (uncheck) the Merge Cells check box, and then click OK.

Repeat step 1 for cells A6 and A10.

Select cell A2 and fill the value "Alpha" down column A to cell A5. Then, fill the value "Bravo" in cell A6 down column A to cell A9 and fill the value "Charlie" in cell A10 down column A to cell A13.

Click any cell in the PivotTable.

On the Data menu, click Refresh Data.

The following PivotTable appears on the worksheet:


   Sum of Sales   Quarter
   Widget Type    Q1        Q2        Q3        Q4        Grand Total
   Alpha                1         2         3         4            10
   Bravo                5         6         7         8            26
   Charlie              3         4         5         6            18
   Grand Total          9        12        15        18            54

This PivotTable contains the correct information.

Additional query words: XL98 pivot table wrong order

Keywords : xlformat xlpivot
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbprb