Stop! Do Not Merge Cells in Excel — Heres why with fixes — The Excel Club

Over the years I have seen many things in Excel that has made me go “wow, I won’t do that again in a hurry”. Thankfully as the years have progressed, so has Excel and many of these problem items have been resolved with updates. However, there are still a few things that you should avoid doing in Excel or stop doing altogether. One of these is the use of the popular Merge cells in Excel formatting option. I beg of you, do not merge cells in Excel

Merging cells in Excel cause problems with sorting and moving data. In this article we are going to look at the types of problems that you will encounter when you use merged cells and of course, a solution too 😊

Why you should not Merge cells in Excel

Pretty and all as you would like your spreadsheets to be, if you merge cells in Excel it can cause many different problems. First, if you are ensuring your workbook is accessible for people of all abilities, merging cells may skew layout to a screen-reader and users may be given the information in the wrong context. Best practice on spreadsheet accessibility advises against the use of Merged Cells.

You might also find when you merge cells in Excel, some of your formulae might not give you the value you expected. This is because merging cells lose the integrity of columns and rows.

Consider the following. We have two columns of data. We have totaled these columns using the SUM function. You can see from the image the SUM formula takes row 1 to row 6.

Let’s say we now merge the column A and B for row 1, Excel will see the value in the left-most column. In this case, column A. The result is that column A now includes the 1990 which should be in column B.

Consider the following table of data. This table contains 3 columns merged for 1 row in the table.

If we were to try and sort this table by any of the Products, we would get an error as there are merged columns that breaks the traditional column structure.

Alternative Solution for Merged Cells in Excel.

Do not despair. We can easily apply a different formatting that will keep the appearance of merged cells, yet the cells won’t be merged. That way your workbook and spreadsheet remain pretty. After-all you don’t want to lose that look right! And we can continue to work with our data without the error we discussed above.

To format cells so there appear as merged, select the cells. Right Click and select Format Cells.

This will open the format cells window. Select the Alignment tab.

From the Horizontal drop-down, select Center Across Selection. The contents of the cell will then appear as if it was merged.

This only works from Left to Right and Excel will assume the value to be contained within the left most column.

Now if we try and sort the table, we do not face the same problem and the data sorts with ease.

Locating Merged Cells in Excel

Now you have read the article you are convinced merged cells are a big no in Excel. That was easy, Job done. But wait. The next problem is locating these cells so we can fix them. To do this, we are going to use Excels Find option.

The keyboard shortcut CTRL + F will open the Find and Replace window.

Once we open Find, we can select Options. Among other things this will allow us select between different formats. Select Format to open the Find Format window.

In the Find Format window, select the Alignment tab and select Merge cells. We do not need to select any other options as we are only searching for merged cells. Pressing OK will close the Find Format box and return us to the Find and Replace window.

By selecting Find All will list all the merged cells. Selecting any of these from the list will make it the active cell in the workbook. This allows you to quickly navigate to merged cells.

Originally published at https://theexcelclub.com on October 20, 2019.