While sorting data in Excel, if you see the “All the merged cells need to be the same size” error, this article will help you. While managing data in Excel, you may need to sort it many times. But if Excel shows this error, you cannot do so as it prevents Excel from sorting the data.
The complete error message that Excel displays is:
To do this, all the merged cells need to be the same size.
Fix All the merged cells need to be the same size Excel error
If you are unable to sort data in Excel due to the “All the merged cells need to be the same size” error, use the solutions provided in this article. It is clear from the error message that you have some merged cells in your data due to which Excel is unable to sort the data. Therefore, to fix this error, you have to find and unmerge the merged cells in Excel.
You can unmerge the merged cells by:
- Locating the merged cells manually
- Locating the merged cells by using the Find and Replace feature
If you have small data in Excel, you can locate the merged cells manually. But if you have large data in Excel, locating the merged cells manually can be frustrating and time-consuming. Hence, the easiest way to locate the merged cells in large data is by using the Find and Replace feature. Let’s see how to use the Find and Replace feature in Excel to locate the merged cells.
- Open Microsoft Excel.
- Open your spreadsheet.
- Press the Ctrl + F keys.
- The Find and Replace window will appear. Under the Find tab, click on the Format button.
- The Find Format window will open.
- Go to the Alignment tab.
- Under the Text control section, uncheck all the checkboxes and leave the Merge cells checkbox selected.
- Click OK.
- Now, click Find All.
When you click on the Find All button, Excel will show you all the merged cells in your data along with their cell addresses. Click on any of the results in the list to highlight the merged cell in your data. After highlighting the merged cell, you can unmerge it.
To unmerge the merged cell(s), follow the steps provided below:
- Select the merged cell in your data.
- Select the Home tab.
- Under the Alignment group, click on the Merge & Center drop-down arrow, and select Unmerge cells.
After unmerging the cells, you can sort your data and Excel will not show you the error. But before sorting your data, do not forget to fill the data in the empty cells. If you find the above method time-consuming, you can easily unmerge all the merged cells in a single click. To do so, press Ctrl + A to select all the cells in your data. Now, go to “Home > Merge & Center (drop-down) > Unmerge Cells.” This will unmerge all the cells in your data.
Umerging the merged cells also creates empty cells. It is important to fill the correct values in the empty cells to avoid errors in your data after sorting it. If you have small data, you can manually fill empty cells with the correct values. But if you have a large data set, mistakes may happen.
To avoid mistakes, you can highlight all the empty cells with color. There is an easy way to do that. Follow the steps provided below:
- After unmerging the cells in your data, select the cell range in your data.
- Go to the Home tab.
- Under the Editing group, select “Find & Select > Go To.”
- Click Special and select Blanks.
- Now, click OK.
After that, Excel will highlight all the blank cells. Now, click on the Fill Color icon under the Home tab and then select the color of your choice. After that, Excel will highlight all the blank cells with the selected color.
Now, you can easily enter data in the empty colored cells by using the Fill Handle. When you are done, you can sort data in Excel and you will not see the “All the merged cells need to be the same size” error message.
Read: Excel found unreadable content error.
Why does Excel say all merged cells need to be the same size?
Excel says all merged cells need to be the same size if you previously merged some cells but not all of the cells are in the sort range, or if you previously merged all of the cells in the sort range and the cells are not in the same size.
How do you merge cells in Excel?
Merging cells in Excel is easy. Select the cells, then go to “Merge & Center > Merge cells.” You will find this option under the Alignment group in the Home tab.
Read next: This action won’t work on multiple selections.
