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. In this post let us know about the center across selection in excel. Use and advantage of center across selection over merge and center.
Though many analysts use merge and center, 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.
Despite of the usage, Merge and Center has few limitations to it.
Limitations of with Merge and Center?
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).
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 over come this limitations of merge and center ? Do we have an alternative to it.
The answer is yes. Excel has an alternative option for this which is “Center across Selection”.
What is Center Across Selection in Excel
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.
How to use Center Across selection in Excel
This feature is available in the Format Cells dialog box. Let us see how to use the Center across Selection feature in Excel.
Select the range of cells on which you want to use this option. Please remember that this will be applicable only on one row though you select multiple rows unlike merging cells.
Right click on the selected cells and click on “Format Cells” option.
In the format cells dialog box, go to “Alignment” tab.
From the Text Alignment section, in the horizontal section, choose the “Center across Selection” and click OK.
You can observe that the data is reflecting at the center similar to merge and center.
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, you can add this to the Quick Access Tool Bar.
Suggested Reading : Know about Quick Access Tool bar and how to add options to Quick Access Tool bar
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 there in the excel ribbon.
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.