If You Use Merge & Centre, You Must Read This!

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

Merge and Centre: A Better Alternative

Merge and Centre.

It's easy to do. Just select a range of cells and click Merge and Centre. Done.

Problem is, it can cause a lots of headaches down the track.

In this short post you'll see why using Merge and Centre is a bad idea and then discover a hidden setting that's so tucked away that hardly anybody knows it exists.

Experts have been using this obscure setting for years to eliminate all the problems that Merge and Centre creates.

ALSO READ

How to handle duplicates in Excel

1.  Six reasons why Merge & Centre is a problem

Perhaps you already use the Merge and Centre feature, found 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.

Merge & Center is deactivated when Format As Table is used

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

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

Merged cells become unmerged when you Format As Table

Figure 04: Merged cells become un-merged when you Format As Table

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

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

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

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

5.      Problems occur when you attempt to insert cells, columns or rows.

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.

ALSO READ

How to show dates like 1st, 2nd, 3rd

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.

ALSO READ

How to calculate a percentage, properly

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

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
Creating New Custom Excel Templates

JOIN THE DISCUSSION

  • If you move a block of cells around the spreadsheet and you have used the merge and center utility, you will get a message from excel that those cells are no longer centered. This does not happen with the text alignment formatting. Finally resolved that annoyance … BIG THANKS!!

  • 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"}
    >