Symptoms
Assume that you paste a range of cells as formulas into a merged cell in Microsoft Excel 2000, in Excel 2002, in Excel 2003, in Excel 2007, or in Excel 2010. In this scenario, the paste may apply the formula to each underlying cell of the merged cell, if the source range is not also a merged cell of the same size. Excel then calculates the result by using all the formulas in the underlying cells of the merged cell. This is expected. However, because you only see one of the component cells of the merged cell, the calculation result might differ from the result that you expect. Additionally, the results of certain aggregation functions, such as the Sum aggregation, as displayed in the status bar do not match the expected results based on the visible data.
Cause
This problem occurs because only the data that appears in the upper-left cell of the copied cells is visible in the merged cell.
Workaround
To work around this problem, use one of the following methods:
-
Use the Paste feature to paste the data.
-
Unmerge the cell before you paste the formulas of the copied data. Then, merge the cells.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
More Information
Steps to reproduce the problem
-
Create a new workbook in Office Excel 2007.
-
Right-click the status bar, and then select the Sum aggregation.
-
Enter 5 in the A1 cell and in the D2 cell.
-
In the A3 cell, type =SUM(A2,B2,C2,D2).
-
Select the A2, B2 and C2 cells.
-
On the Home tab, click Merge & Center.
-
Right-click the A1 cell, and then click Copy.
-
Right-click the merged cell, and then click Paste Special.
-
In the Paste Special dialog box, click Formulas, and then click OK.
20 is displayed in the A3 cell. However, the value that is expected based on the visible data in the merged cell is 10. Additionally, if you select the merged cell (A2), you can see that the value of the Sum aggregation in the status bar is 15. This occurs because the merged cell is an aggregate of the cells A2, B2 and C2, and these cells contain the same formula value of 5.
