Whenever you try to sort or filter a cell range containing merged cells, you can encounter an error message such as “To do this, all the merged cells need to be the same size.”
As the error message suggests, you can only select merged cells of the same size. Meaning, you must have merged cells formed using the same number of rows and columns to sort/filter them.
To resolve such error messages, you need to configure all the merged cells to be of the same size. However, it’s quite taxing as you need to find all the merged cells first and adjust them to the same size without altering any data.
So, a better alternative is to simply unmerge such cells to any merged cell issue whatsoever.
- Select the cell range of the column where you want to sort/filter cells but receive the error message.
- Now, click Merge & Center under the Home tab. Or, click the down arrow icon and select the Unmerge cells option.
- Click OK.
After the cells are unmerged, you will be left with blank cells. To fill them with the same respective values that existed before unmerging,
- Press Ctrl + G while the cell range is still selected and click Special on the next prompt.
- Next, choose the Blanks option.
- Once all the blank cells are selected, type
=and select the cell value above it.
- Now, instead of pressing Enter, press Ctrl + Enter to fill in the blank cells with appropriate values.
How Do the Merged Cells Work?
Surprisingly, the merged cells work much differently than the typical Excel cells.
Once you merge cells, you can no longer select individual cells inside the merged cell. The whole merged cell takes up the cell reference of the top-left cell. This is true irrespective of how you have merged them; merged across or center.
So, if you try to refer to other cells inside the merged cell, Excel will either return the value 0 or an error while using them in a formula.
For instance,
Here, we have different types of merged cells. However, you can notice that all of them return the value of their respective top-left cell only.