Excel’s merge cells feature provides an easy way to group multiple cells together. You can create multi-level headers by merging cells on the previous row or column. Merged cells looks like a higher category that contain the information in the following cells. However, the merge cells feature comes with some disadvantages which can cause issues in the long run. In this guide, we are going to show you how to use Center Across Selection in Excel as an alternative to Merge Cells.
Disadvantages of Merge Cells
Possible data loss
Merging cells is an easy action which you can directly apply with the click of a button. You can find the icon in Alignment under Home tab of the Ribbon.
This icon merges all of the selected cells and centers the text contents. However, the merging action keeps the content of the top-left cell only, and removes any other contents from the other cells. Although, Excel will give you a warning for this, this essentially means data is lost once you perfrom a merge.
No ability to sort
If you have a merged cells in your data table, Excel won’t allow you to sort your data.
Copy and paste may not work
Unfortunately, you can’t paste anything into a merged cell unless you copy the data from another merged cell which is exactly the same size as your target cell. Otherwise, you will get an error message.
You can’t select an individual cell in a merged cell
You need to un-merge the cells before you can make an inidividual selection. This may not make sense in some cases, but it can happen:
Merge cells doesn’t work with Excel Tables
Excel Tables has its own special cell structure. For example, filtering, formatting, dynamic sizing, and special references which can directly refer a specific column. Excel doesn’t allow you to use merge cells feature to avoid of breaking this structure.
Using Center Across Selection as an alternative
You can disregard all of the mentioned disadvantages by using the Center Across Selection feature. The feature makes selected cells looks like merged cells without actually grouping them.
Technically, Center Across Selection is an alignment setting. Let’s see how you can apply this formatting to your cells:
- Begin by selecting cells in a row. Unfortunately, the feature only works in horizontal direction.
- Open the Format Cells dialog by pressing Ctrl + 1 or using context (right-click) menu
- Activate the Alignment tab
- Select the Center Across Selection option for the Horizontal dropdown
- Click OK to apply the settings
Center Across Selection basically alters how cells will look instead of changing their structure. This visual trick allows user to use filtering or pasting without any issues.
On the other hand, there are some disadvantages to this method as well. First of all, you need to set the alignment in Format Cells dialog while merging cells can be done with a single click. Also, you need to select all cells to apply any other formatting like background colors or borders.