Merge and Centre: A Better Alternative
Assign a 'primary' menu

Merge and Centre: A Better Alternative

Merge and Centre: A Better Alternative

​Why Merge & Centre is a problem

Using Merge and Centre in Excel can cause many problems.

Here’s a quick tip to overcome those problems using one of those little gems hidden deep inside Excel.

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: 4 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 unmerged and you cannot remerge them.
  3. 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

  4. You cannot sort or filter a range containing a merged cell. An error is given.
  5. 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 deactivated when Format As Table is used

  6. You cannot select just one of the merged cells.
  7. Problems occur when you attempt to insert cells.
  8. 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.

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.
  6. Center Across Selection

    Figure 06: Center Across Selection

  7. 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.

Wrapping up

Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.

Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature
Jason Morrell blog signature
30 Excel Power Tips

30 EXCEL POWER TIPS

Discover the shortcuts and tricks used by the pros

Office Mastery is the digital arm and official blog of Two Rivers Software Training
Copyright © 2018 Two Rivers Software Training. All Rights Reserved Privacy Terms Contact
Like us on facebook Follow us on Twitter Watch us on YouTube Connect with Jason on LinkedIn
>