Ever had an error message which stated that you “Cannot change part of a merged cell”?

Well the merge format is the culprit. The Merge and Center icon on the Home ribbon is commonly used to format headings to make them look good by centering a heading across the cells below it.

See an example of the Merge and Center format below. Cells B2 and E2 have had the format applied.

The effect on macros and on copying can be disastrous. Normally stable macros can crash if they have to copy and paste ranges that contain Merged cells.

Excel limits what you can do with merged ranges because when you merged a range you in effect remove cells from the sheet.

An alternative format that does not crash macros or display error messages is Center Across Selection which achieves the same effect as Merge and Center without the downside.

In all versions of Excel Center Across Selection is in the Format Cells dialog in the Alignment tab under the Horizontal drop down list. See image below. Ctrl + 1 will open the Format cell dialog in all versions of Excel.