Make All Merged Cells the Same Size in Excel & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to make all merged cells the same size in Excel and Google Sheets.
There are a number of operations you can perform in Excel that require all the cells contained within your data to be merged in the same way, or not to be merged at all.
Unmerge Cells
Say you have merged some cells in Row B, and the same cells need to be merged in all other rows of your data. If you attempt an operation and get an error message that “all the merged cells need to be the same size,” then you need to either find all the merged cells and make sure they are all merged to the same size, or more commonly, unmerge all cells.
In the picture below, cells in Rows 6 and 7 and Rows 9 and 10 have been merged. Using this data, if you attempt to sort or filter this data, you get an error message.
One option to fix this is to unmerge cells individually. Select the merged cell, and then in the Ribbon, go to Home > Alignment > Unmerge Cells.
However, this could become tedious if there are many merged cells in your worksheet! To unmerge all the merged cells at the same time, select the entire dataset, and then in the Ribbon, go to Home > Alignment > Unmerge Cells.
This unmerges all selected cells.
Another instance where you could get an error with merged cells is if you attempt to copy a formula down when some of the cells in the rows below the formula are merged together. Once again, you would need to unmerge your cells before copying down the formula.
Remove Blank Cells
Once you have unmerged your cells, you should be able to perform commands – like filtering, copying a formula down the entire column, or sorting – as usual. However, you could now find yourself with blank rows within your data which may hinder that operation!
Remove the blank rows with these steps:
- With the dataset selected, in the Ribbon, go to Home > Editing > Find & Replace > Go To Special.
- Then choose Blanks and click OK.
- Finally, in the Ribbon, go to Home > Editing > Delete > Delete Sheet Rows.
This removes blank rows that were left when you unmerged the cells. Now you can sort or filter the data.
Make All Merged Cells the Same Size in Google Sheets
To unmerge cells in Google Sheets, you can either select each merged cell individually, or select the range of merged cells and unmerge them all at once.
- Select the range of cells to unmerge. The first cell in the selected range must contain a merged cell.
If the top-left cell in a selection is not a merged cell, Unmerge is not available!
- In the Menu, go to Format > Merge cells > Unmerge.
