Merge and Center – Killer or Saver –Alternative to it

Merge and Center the most commonly used feature in excel. This feature is used to combine multiple cells and create a cell that spans across multiple rows/ columns. Though mostly used by many analysts, this feature is not the favorite of many analysts/ advanced excel users.

Interesting right. The most commonly used feature not being favored by many. Let’s look into the reasons behind this.

Merge and Center

Despite of the usage, Merge and Center has few limitations to it.

  1. Copy and Pasting Cells: Merge and Center restricts copy pasting of data from other cells. When you try to paste data over merged cells an error message pops up restricting the operation (see the below screen shot which shows the most common errors).

    merge and center error1

  2. Hurdle for Macros: All the VBA coders agree that merged cells are macro breakers. If a macro code comes across a merged cell the code breaks out unless you has an error handling code for it.

How to overcome this problem?

Excel has an option for this which is “Center across Selection”.

The option serves the similar functionality as Merge and Center. However offers more flexibility compared to Merge and Center option. The look is similar to the merge and center but without the problems of copy paste/ using macros.

This feature is available in the Format Cells dialog box. Let us see how to use the Center across Selection feature in Excel.

  1. Select the range of cells on which you want to use this option. Please remember that this would be applied only on one row though multiple rows are selected unlike merging cells.

     center across option in excel

  2. Right click on the selected cells and click on “Format Cells” option.

     center across option in excel

  3. In the format cells dialog box, go to “Alignment” tab.

     center across option in excel

  4. From the Text Alignment section, in the horizontal section, choose the “Center across Selection” and click OK.

     center across option in excel

  5. You can observe that the data is centered as we see in merge and center.

center across option in excel

Alternatively you can use the Key Board Shortcut “CONTROL + 1” for opening the Format Cells dialog box.

But the option is not available in the ribbon. However, this can be added to the Quick Access Tool Bar. (In case if you are not sure of adding features to Quick Access Tool Bar, Please read about the Quick Access Toolbar here)

I personally feel that this should be a must have option in the ribbon. Many advanced excel users also feel that this is one of the features which needs to be included in the excel ribbon. Already a proposal was made by Chris Newman on the Excel User Voice site to squeeze in a button on the Home Tab that would provide the Center across Selection functionality. You can see it and vote for it Here.

Do you use the Center across Functionality. Do share your thoughts on it in the comments.

Please do share this post with your friends if you find it useful.

Leave a Reply