How to fix all merged cells need to be of the same size error in Excel

Let’s look at the dataset for example, where you have a table with data of customers arranged with respect to city names as shown above. The customers with the same cities are listed together and to avoid repetition of city names, merged cells are used.

The Error due to Merged Cells Size

The data looks very neatly arranged in the image shown above, however, when we try to sort the data, we get an error as shown below.

This error means that the data can’t be sorted as long as we have merged cells in our data. To fix this error either all cells in the dataset should be merged and should have the same size i.e., 3 rows by 3 columns as shown in the above image. However, this is not feasible to follow because that would take up a lot of extra space if we have a large dataset. So, we’ll use a different approach by following the steps below.

Excel users use merged cells in the worksheet very frequently to combine two or more cells into a single cell. This can be useful for creating uniform formatting or for creating a cell that spans multiple columns or rows. This can be a very useful feature for data representation, however, sometimes problems are faced while using some other features of Excel along with merged cells.

Step 1 – Select all Merged Cells

– Select all merged cells in the dataset by clicking and then dragging down along the merged cells range as shown above.

Step 2 – Unmerge the merged cells

– On the Home tab, go to the Alignment group and click on the Merge and Center dropdown.
– Now from the dropdown menu choose Unmerge Cells. This will unmerge all merged cells as shown above.

Step 3 – Copy the data of unmerged cells to required cells

– As the cells have been unmerged and the new data reside only in single cell, instead of 3 rows and 3 columns, now we have to copy the data from unmerged cells to other cells as shown above.

Step 4 – Copy the data of unmerged cells to required cells

– Now on the Home tab and in the Editing group, click on the Sort and Filter dropdown.
– Choose Sort A to Z from the dropdown menu and this time the data will be sorted without any problems as shown above.