Consolidation Does Not Consolidate Text in Excel

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

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

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

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

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.
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

Additional query words: 2.2 2.20 2.21 3.0 3.00 4.0 4.00 5.0

Keywords :
Version :
Platform :
Issue type :