Hello Excellers, and welcome back to another #Excel blog post in my 2021 series. Today I will show you how to achieve the same effect in Excel, without actually merging cells in Excel. Confused?. Don’t be. Do you use merged cells in your Excel worksheets?. Have you come across issues when you try to write formulas such as XLOOKUPS and VLOOKUPS. Well, those merged cells are probably causing the issues. We can use Center Across Selection as a great alternative. This method has the same visual effect, but you can ‘merge’ cells without losing any data.

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips. Just click the link to get immediate access to my download. Keep Excelling.

Some while users love merging cells, some don’t. I admit I am the latter, in fact, I do not like them with a passion. They can be useful sometimes for example in a template or two, to make them look and flow better, but when it comes to functionality I just do not use them.

Problems With Merged Cells.

The risk I find with merging cells are

• Losing the ability to sort your data.
• Affecting the ability to copy and paste.
• You lose the ability to run VBA programming code as it just does not handle the merged well at all. Usually, more code is needed to deal with the issue.

Looks Like Merged Cells.

But, the can add to the look and feel of a spreadsheet solution. So, here is a great alternative. We do not need to compromise on the merged look. There is an alternative.  Same look and feel with NO effect on functionality.

Normally a merged cell looks like the screen shot below.  The cells B2 to E2 are merged.

Use Center Across Selection.

So, to get the same effect all we need to do is firstly UNMERGE the cells.

• Select the cells.
• Home Tab | Alignment Group.
• Unmerge cells.

You now have normal cell formatting.

• Select the cells that you want to appear ‘merged’.  I will select B2 to E2 again.
• Home Tab | Alignment Group | Expand Alignment  Settings.
• Select the Alignment Tab | Text Alignment.
• Horizontal | Center Across Selection.
• Hit ok.

Now, see that the text ‘appears’ to be centred.  You can still click into the individual cells, however.  This means we retain all of the worksheet functionality.  Result!.

How To Excel At Excel – Formula Friday Blog Posts.