XL: Label Missing from Upper-Left Corner of Consolidation Table

Last reviewed: February 5, 1998
Article ID: Q175851
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, if you use the Consolidate command to summarize data from two or more worksheets, the label may be missing from the upper-left corner of the consolidation table. For example, the table may appear as follows

             Number
   Alpha     5
   Bravo     7
   Charlie   9

instead of:

   Name      Number
   Alpha     5
   Bravo     7
   Charlie   9

CAUSE

This problem occurs when you create the consolidation table and you select both the Top Row and Left Column check boxes under "Use labels in" in the Consolidate dialog box.

WORKAROUND

To work around this problem, follow these steps:

  1. Select the consolidation table.

  2. On the Data menu, click Consolidate.

  3. Click to clear both the Top Row check box and the Left Column check box. Then, click OK.

  4. On the Data menu, click Consolidate. Click to select the Top Row check box, and then click OK.

  5. On the Data menu, click Consolidate. Click to select the Left Column check box, and then click OK.

The label should appear correctly in the upper-left corner of the consolidation table.

To prevent this problem from occurring when you create a new consolidation table, select only ONE of the two check boxes (Top Row or Left Column) when you create the table. Then, click Consolidate on the Data menu, select the other check box, and then click OK.

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 Consolidate feature in Microsoft Excel allows you summarize data from multiple, identically arranged tables. To see an example that uses the Consolidate feature and demonstrates problem described in the "Symptoms" section in this article, follow these steps:

  1. In Microsoft Excel, create a new workbook. Enter the following values in the specified worksheets:

          Sheet1                     Sheet2
          ------------------------   ------------------------
    
          A1: Name      B1: Number   A1: Name      B1: Number
          A2: Alpha     B2: 1        A2: Alpha     B2: 4
          A3: Bravo     B3: 2        A3: Bravo     B3: 5
          A4: Charlie   B4: 3        A4: Charlie   B4: 6
    
    

  2. Click Sheet3, and then select cell A1.

  3. On the Data menu, click Consolidate. In the Consolidate dialog box, follow these steps:

        a. Click Sheet1. Select the cell range A1:B4. Click Add.
    

        b. Click Sheet2. Select the cell range A1:B4. Click Add.
    

        c. Click to select both the Top Row and the Left Column check boxes in
           the dialog box.
    

        d. Click OK.
    

The following consolidation table is created in Sheet3:

   A1:           B1: Number
   A2: Alpha     B2: 5
   A3: Bravo     B3: 7
   A4: Charlie   B4: 9

Note that the values in the table (5, 7, 9) are the sums of the respective values in cells B2, B3, and B4 in the tables in Sheet1 (1, 2, 3) and Sheet2 (4, 5, 6).

Note also that the label "Name" is missing from cell A1. If you follow the steps in the "Workaround" section, the missing label appears correctly, for example:

   A1: Name      B1: Number
   A2: Alpha     B2: 5
   A3: Bravo     B3: 7
   A4: Charlie   B4: 9


Additional query words: XL5 XL7 XL97 5.00a 5.00c 7.00a
Keywords : xllist
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbprb
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 5, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.