Merged cells alternative

When copying and pasting cells, I sometimes get an error message that says:

By Neale Blackwood

Merged cells can cause difficulties when using Excel. There is a standard tool on the toolbar called “Merge and Center”. Many people use this tool to format reports, as it “merges” two or more cells into one and then centers the text horizontally within the merged cell. This can cause a number of problems.

One of these relates to the question above, as it affects how you can copy and paste. Merging also affects how you can select ranges since you can’t select or enter data into an individual cell within a merged cell – apart from the cell to the left or top of the merged cell.
There is an alternative to using the “Merge and Center” toolbar icon but it only works horizontally. You can, however, merge cells vertically or vertically and horizontally simultaneously.

This alternative is “Centre Across Selection”. Select a range of cells in a single row. Click the Format menu, click Cells, click the Alignment tab, click the Horizontal drop down arrow and select “Centre Across Selection”. Click OK. This format works exactly the same for display purposes as “Merge and Center”, but doesn’t have drawbacks mentioned above.

Note: If the cell to the left of the range you selected was empty, you will not see a difference until you enter something in that cell. When you use “Center Across Selection” you can select and enter data into an individual cell within the “merged” area. Unfortunately, there is not a toolbar button for “Centre Across Selection”. To simplify using it you could create a style, or record a macro and then assign the macro to a toolbar button.