Merged Cells & Paste Special (Bug)

[ccoleman]

I wasn't sure if this would be the right forum to post this but there's a slight bug in excel, if you have 2 or more columns merged and you paste special -> formula into those merged cells it will write the value to the individual cells instead of giving you an error or writing to the "single" merged cell. This can cause your sum() formulas to count the values in the merged cell twice.

I like to think the lesson here is to avoid having formulas/computational values in merged cells, but I may be slightly biased here.

 

[Norie]

I wouldn't really call that a bug and I wouldn't say you are being biased against merged cells.

I think if you search this forum you'll find that the general consensus is that merged cells are just a bad idea.

They can cause problems like the one you've described, and working with them with code can't just be a nightmare.

 

[Joe4]

The lesson here is to avoid using merged cells whenever possible! They can wreak havoc with things like VBA and sorting too. Most Excel programmers (like myself) avoid them like the plague!

Usually, you can use the "Center Across Selection" formatting to get the same effect without all the issues.

 

[ccoleman]

It was a company affiliated with ours that came up with this and their contact with Microsoft said they're working on a hot fix as I believe all paste special commands are suppose to error with merged cells. I think it's funny that my exact response was, and they're using merged cells why? I've always been very VBA heavy in my sheets and so I avoid them basically 100% of the time but I know people will use them because they understand them more but I'm still unsure what they would ever be copying a formula into a merged cell for, I just really can't see any logical reason for it.

 

[Norie]

I can't see a logical reason for using merged cells at all, not for formulas and definitely not for code.