If you’ve used Excel’s Merge And Center tool for a long time (like I had), you could be forgiven for thinking there’s no better alternative (like I did). Well, let me introduce you to Center Across Selection, a tool that does exactly the same job without disrupting your spreadsheet’s structure.
Why You Should Avoid Merge And Center
This might come as a bit of a shock, as many believe that merging cells is the best way to spread content across many cells or whole rows when the same data applies to each cell.
However, because Excel largely relies on you having a consistent grid of individual cells placed in rows and columns, merging cells causes issues when you come to make changes to your data.
First, if you add filter buttons across the top row of some data and then try to sort that data using those buttons, Excel will return an error message. The same will happen if you try to use Excel’s Sort button in the Sort And Filter group of the Data tab on the ribbon.
Similarly, you’ll hit a wall if you try to paste data from an unmerged row to a merged row.
In short, Excel just can’t deal with the altered spreadsheet formatting caused by merged cells. There’s a reason why when you format your data into a table, Excel grays out the Merge And Center option—because it messes around too much with how tables and their filters work.
Why Center Across Selection Is a Better Option
If you already have lots of merged cells in your sheet, and you’re worried about the structural consequences, don’t fret—let me talk you through the best way to fix this issue while still having your spreadsheet laid out in exactly the same way.
First, select one of the affected cells, and click “Merge And Center” to unmerge them. Then, select the cells you had merged previously, and click the icon in the bottom-right corner of the Alignment group in the Home tab.
Open the “Horizontal” drop-down menu in the Text Alignment section of the dialog box that opens, click “Center Across Selection,” and click “OK.”
And hey presto! The data appears as it did when you used the Merge And Center option, but the cells’ structures and integrity have been retained. This means you can now use filters and copy and paste data between cells—regardless of whether you use a formatted table—without Excel coughing up frustrating error messages for you to deal with!
Admittedly, the Center Across Selection tool only works across rows, and not down columns. However, this shouldn’t present too many issues, as Merge And Center and Center Across Selection were designed primarily for horizontal use, rather than vertical use.
Using Center Across Selection isn’t the only way you can format your cells for clear and readable spreadsheets. For example, freezing rows and columns, not overusing cell borders, being consistent, and using nifty charts and tables can all make for a better-looking workbook.
