Hate Merged Cells? Here’s an Easy Alternative!

Merged cells are great for presentation purposes but not so ideal when you’re creating a model or a complex spreadsheet. It can make copying cells or formulas painful as you can get an error or unintended results when doing so. However, there’s a way that you can achieve the same result as a merged cell without actually having to merge anything.

Align cells rather than merge them

When merging cells, you would select the cells you want to merge, and then click on the following button:

Merge and center button in Excel.

What happens in that case is that the cells you selected now become one. In the below example, if I click on Merge & Center, the text would get spread across multiple cells:

Selecting multiple cells in a range.

And become this:

Cells after applying merge & center.

By doing so, I can no longer enter a value individually in cells B1:F1 as the merged cell effectively takes over. If you never plan to copy over these cells then that’s fine but if you do, then you could end up with some annoying errors along the way.

However, there’s an easier, less intrusive way of accomplishing the same result. I would still select cells A1:F1 but instead of using the Merge & Center button I’ll go into the cell formatting menu (CTRL+1) which will give me the following options:

Format cells alignment options.

From here, I’ll adjust the Horizontal Alignment and select Center Across Selection:

Format cells menu selecting center across selection.

Now, my text is spread across those cells but it isn’t merged, it only looks that way. For example, I can still click individually on cell B1 and enter data there:

Selecting a cell when text is aligned across multiple cells.

And if I do that, it takes over the center alignment and pushes back the other text into cell A1:

Entering data in a cell that was already center aligned across multiple cells.

The advantage of using an alignment rather than merging cell is this avoids the errors that can come when you’re modifying or copying data and you get notifications that the cells are not the same size.

By using the horizontal alignment, you have the flexibility of making your text appear as if you’ve merged it across multiple cells without you needing to unmerge data later on if you want to change the text or add data.

If you liked this post on an alternative to merged cells please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter.

Add a Comment

You must be logged in to post a comment