Microsoft KB Archive/71303

= Consolidation Does Not Consolidate Text in Excel =

Article ID: 71303

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 4.0c
 * Microsoft Excel 3.0a

-



This article was previously published under Q71303



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.



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 &quot;STATE&quot; 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.

<div class="references_section">