Forum Discussion
Format Painter error when copying from merged cell into cells that have content
This error was detected when pasting format from merged cells to cells that already have content.
The initial merged cell keeps only the upper left value, but the destination cells will keep both values leading to errors in calculations.
See the following example for better understanding.
The user wants to merge columns C and D in the following table because the content is duplicated:
The user follows these steps:
- Merge the totals in C2 and D2 using "Merge and Center".
- Copy format from C2/D2 into C3/D3 using "Format Painter".
- Merge C4 and D4 use "AutoSum" to get the total.
The result will be the following:
As you can see, the sum is 3.00 instead of 2.00, which is wrong.
Unmerging the cells will show the reason behind:
The content of both merged cells in row 3 is still there and is added individually. In other words, copy format didn't delete the content in the right cell as it would have happened if the cells were merged using "Merge and Center".
This may lead to incorrect calculations in any worksheet, specially when different users have access to the same file.
You can report it to Microsoft from within the desktop version of Excel: File > Feedback > Send a Frown.
3 Replies
This is one of the many reasons to avoid merging cells.
But it is easy to avoid this error. Instead of merging C2 and D2, then using the format painter, select C2:D4, then click the drop down arrow on the right hand side of the Merge and Center button, and select Merge Across from the drop down menu.
- Thanks for your answer! I agree with you. I don't like merging cells and if you really need to merge, there are many better ways than using the format painter.
The problem in this case is that this is a shared workbook. Another user did this and I suffered the consequences.
In any case, I believe this is an error in Excel.You can report it to Microsoft from within the desktop version of Excel: File > Feedback > Send a Frown.
