Consolidation Does Not Consolidate Text in Excel

 Last reviewed: August 20, 1995
Article ID: Q71303
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SUMMARY

The Consolidation feature of Microsoft Excel will not consolidate text. The only text that Consolidation will bring across is Category Titles. Titles are brought into the destination sheet when you select Top Row or Left Column from the Consolidate dialog box. Any other text in the source areas is not brought across. If the source areas contain text other than Category Titles, blanks are brought into the destination area.

MORE INFORMATION

Steps to Reproduce Problem

  1. Insert the following information into a spreadsheet. This information is your first source area. 

            A1:  NAME       B1:  STATE      C1:  AMOUNT
        A2:  KEN        B2:  NC         C2:  5.00
        A3:  DALE       B3:  NC         C3:  5.00
        A4:  WILSON     B4:  NC         C4:  5.00

  2. Insert the following information into a second spreadsheet. This information is your second source area. 

            A1:  NAME       B1:  AMOUNT
        A2:  KEN        B2:  5.00
        A3:  DALE       B3:  5.00
        A4:  WILSON     B4:  5.00

  3. Save these sheets as SHEET1.XLS and SHEET2.XLS, respectively.

  4. In a third sheet (the destination area), do the following:

    a. Select cell A1. b. From the Data menu, choose Consolidate. c. Select SUM from the Function box. d. Select Top Row. e. Select Left Column. f. In the Reference box, type "sheet1.xls!$A$1:$C$4" (without the 

          quotation marks).
    g. Choose Add. h. In the Reference box, type "sheet2.xls!$A$1:$B$4". i. Choose Add. j. Choose OK.

The destination area should now contain the values listed below: 

  A1:  NAME       B1:  STATE      C1:  AMOUNT
  A2:  KEN        B2:             C2:  10.00
  A3:  DALE       B3:             C3:  10.00
  A4:  WILSON     B4:             C4:  10.00
The category title "STATE" is listed, but the cells below it are empty. This is not a problem with Excel. The function that Excel consolidates with, by default, is the SUM function. Because the SUM function cannot be applied to text values, no values are brought into the destination area.

REFERENCES

"Online Help," version 5.0 "User's Guide 1," version 4.0, pages 374-383 "User's Guide," version 3.0, pages 328-334

KBCategory: kbusage
KBSubcategory:

Additional reference words: 7.00 2.2 2.20 2.21 3.0 3.00 4.0 4.00 5.0 5.00

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: August 20, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.