XL: Active Cell May Change When You Sort a PivotTable

ID: Q178047

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, when you sort a PivotTable, the active cell may change, depending on the structure of the PivotTable.

This behavior may cause a problem if you sort the PivotTable while running a Visual Basic for Application procedure that assumes that the active cell has not changed as a result of the sort.

NOTE: This behavior is different from earlier versions of Microsoft Excel. To see an example of this behavior, see the "More Information" section in this article.

CAUSE

This problem occurs because Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition use "structured selection" in PivotTables. This means that when you sort a PivotTable, Microsoft Excel moves the selection with the currently selected data. If the selection is not adjacent (not contiguous), Microsoft Excel selects the upper-right cell in the current selection.

This behavior is by design of Microsoft Excel.

WORKAROUND

You cannot change this behavior. However, if you are using a Visual Basic procedure to sort a PivotTable, try modifying the macro so that it does not assume that the active cell remains unchanged when you sort the PivotTable.

MORE INFORMATION

Example

To see an example of this behavior, follow these steps:

1. In Microsoft Excel, create a new workbook. Enter the following data

   in Sheet1:

      A1: Name        B1: Position   C1: Points
      A2: Dineen      B2: R          C2: 12
      A3: Kapanen     B3: R          C3: 27
      A4: Roberts     B4: L          C4: 25
      A5: Primeau     B5: C          C5: 22
      A6: O'Neill     B6: C          C6: 14
      A7: Sanderson   B7: L          C7: 13

2. Select cell A2. On the Data menu, click PivotTable or PivotTable

   Report.

3. Proceed through the PivotTable Wizard as follows:

    a. In step 1 of the wizard, click Next.

    b. In step 2 of the wizard, click Next.

    c. In the step 3 of the wizard, drag the Position field into the ROW
       area, drag the Name field into the ROW area, and drag the Points
       field into the DATA area. In the ROW area, double-click Position,
       select None, and click OK. Then, click Next.

    d. In step 4 of the wizard, select cell D10. Click Finish.

   The PivotTable lists the Position, Name, and total Points for each
   player.

4. Select cell D16. Click the Sort Descending tool on the Standard

   toolbar.

   In all versions of Microsoft Excel, the PivotTable is sorted. In
   Microsoft Excel versions 5.0 and 7.0, the active cell does not change.
   In later versions of Microsoft Excel, the active cell moves from cell
   D16 to cell F12 (the upper-rightmost cell in the selection) because the
   current selection is not an adjacent range of cells.

5. Select cell D16. On the Data menu, click PivotTable or PivotTable

   Report.

6. Drag the Position field out of the ROW area. Click Finish.

7. Select cell D16. Click the Sort Descending tool on the Standard

   toolbar.

The PivotTable is sorted. In Microsoft Excel versions 5.0 and 7.0, the active cell does not change. In later versions of Microsoft Excel, the active cell moves with the data that was previously in cell D16.

Additional query words: XL97

Keywords          : xllist xlpivot 
Version           : WINDOWS:97; MACINTOSH:98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999