Merge and Centre: A Better Alternative

Merge and Centre:
A Far Better Alternative

AUTHOR:  JASON MORRELL 

Jason Morrell
Merge and Centre: A Better Alternative

Using Merge and Centre in Excel can cause a lot of problems down the track.

This post discusses a hidden tool that the experts use to avoid these problems entirely.

30 Excel Power Tips

FEATURE DOWNLOAD
30 Excel Power Tips

1.  Why Merge & Centre is a problem

Perhaps you use the Merge and Centre feature, available in Excel on the Home ribbon. It merges a range of pre-selected cells and horizontally centres the text. If more than one of the cells contains text, only the text in the first cell is retained and the rest are discarded.

The Merge & Center icon

Figure 01: The Merge & Center icon

4 cells merged and centred

Figure 02: Four cells merged and centred

There are a number of problems that occur when using Merge and Centre.:

1.       You cannot merge cells within a formatted Excel table.

2.      When you select a cell range containing merged cells and ‘Format As Table’, the merged cells become un-merged and you cannot re-merge them.

You get an error when you attempt to sort or filter a table that contains merged cells

Figure 03: You get an error when you attempt to sort or filter a table that contains merged cells

3.      You cannot sort or filter a range containing a merged cell. An error is given.

Merged cells become unmerged when you Format As Table

Figure 04: Merged cells become unmerged when you Format As Table

Merge & Center is deactivated when Format As Table is used

Figure 05: Merge & Center is inactive when Format As Table is used

4.      You cannot select just one of the merged cells.

5.      Problems occur when you attempt to insert cells.

6.      In older versions of Excel, if, say, 5 cells were merged vertically, you could not then select an individual row as every row belonging to the merged cells is selected.

2. The alternative to Merge & Centre

To get around the problems with Merge and Centre, you should use ‘Center Across Selection’ instead. Here’s how:

1.  Select the cells you want to ‘merge’ (they won’t actually be merged but the end visual result is the same).

2.  Press CTRL 1 (or right-click and choose Format Cells).

3.  Select the Alignment tab.

4.  Open the Horizontal drop-down list.

5.  Choose Center Across Selection.

Center Across Selection

Figure 06: Center Across Selection

5.  Click OK to close the dialog.

The text becomes centred but the individual cells remain intact so Excel can continue using the rows and columns that it so loves. All of the aforementioned problems disappear.

So stop using Merge and Centre and start using Center Across Selection.

3. What next?

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Suggested Posts

  • This… This just saved me! It would be great if Microsoft just re-purposed the Merge and Center button to use this instead! Never gonna happen, but it would be great it if they did!

    • If you have 10 cells and you don’t want the text centred across the 10 cells, then enter your text into the first cell, leave it left-justified and leave all the following cells empty.

    • Microsoft have well and truly hidden this essential feature and have not provided an easy way to shortcut it. Even Bill Jelen (Mr Excel himself) does not have an answer.

      There is no command in the commands list so you can’t add it to the Quick Access Toolbar.

      You could create a quick macro, but that has other implications.

      Or you can create a cell style and use the ribbon shortcuts to access it, like this:

      1. Click the Cell Styles icon (Home ribbon) and choose New Cell Style.
      2. Name it Center Across Selection and clear all the check boxes except Alignment.
      3. Click the Format button, go to the Alignment tab and choose Center Across Selection.
      4. Click OK twice to close the dialogs.
      5. To use it, press Alt+H then J then up arrow then Enter.

      It’s ugly and certainly not sleek, but it’s the only real option available.

      (If anyone has a better solution, please share the love!)

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Office Mastery
    >