Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 983435 - Last Review: October 9, 2011 - Revision: 3.0
The result of a calculation that uses the data in a merged cell does not match the result that is expected based on the visible data in the merged cell in Excel 2000, in Excel 2002, in Excel 2003, in Office Excel 2007, or in Excel 2010
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.
This problem occurs because only the data that appears in the upper-left cell of the copied cells is visible in the merged cell.
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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.
APPLIES TO
- Microsoft Office Excel 2007
- Microsoft Excel 2000 Standard Edition
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2010
| kbtshoot kbsurveynew kbprb KB983435 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate