Avoid Merged cells in Excel

This can be a surprise to you, but it’s true! You should avoid using merged cells in a Excel worksheet because it limits it’s future use. Here’s an example. This is how my table originally looks:


My table title is not centered across the table. I can simple select cells B2:C2 and click on the Merge & Center on my Home tab. I can also align “Missed both tests” across C2:D2. My table would look like this:


Definitely better, in terms of look, but if, for instance, you want to sort the test results by the results of Test 1 column, by selecting the range B3:D7 and going to Data-Sort and try to sort by Test 1 column, you will get an error message, like this:


Let’s get back to our original table, without merged cells. To get the same look result, you should consider using “Center Across Selection” instead. Please follow this steps:

1 - Select the cells that you want to “merge”, in this case, cells C2:D2;
2 – Open the Format Cells dialog box (Home Ribbon, Font group, little button in bottom right or right click and select Format Cells from the list) 
3 – On the Format Cells dialog box, go to the Alignment tab and on the “Text alignment”-Horizontal, select the “Center Across Selection” option and click OK, like this:


4 – Select cells C6:D6 and repeat the process.

Your table will look like the one with merged cells but now, let’s try to do the sort again by the results of Test 1 column, like you tried before. You don’t get any error now, do you?

This is to show to you that you can get the same look on your worksheets using merged cells and Center Across Selection methods but if you need to use your data to further operations, like sorting, using merged cells, you will be restricted. With Center Across Selection you will not have this kind of problems.

3 comentários:

Clem said...

this does not work as expected. Merged cells allow you to align left across the range. This centers the text within the cells. not the same thing.

Anonymous said...

If you want left alignment why do you want to merge cells ?

The6uest said...

Because if you hide the first column in the "Center Across Selection" it will also hide the text. Merging across keeps the text visible which is important if you need to reference it.