Merging cells in Microsoft Excel can be a helpful feature to organize and display your data.
But sometimes, while working with data in Excel, you may get the warning message ‘To Do this, all merged cells need to be the same size‘ as depicted below:
So how do we tackle this issue? Let’s find out.
Understanding the “Merged Cells Need to be the Same Size” Error
The error “To Do this, all merged cells need to be the same size” in Excel typically occurs when attempting to apply an operation, such as sorting, filtering, or copying a formula, to a range of cells that contain merged cells of different sizes.
This error message results from Excel’s inability to perform the desired operation on merged cells with different sizes.
To resolve the error, you must make all the merged cells the same size (which means that either all cells should be unmerged, or all merged cells should be the same size).
However, making all the merged cells the same size can be quite challenging because you need to find and adjust them to the same size without changing any data.
A better way of fixing the error is to unmerge all the cells in the target data range.
This tutorial describes four techniques for resolving the error, “To Do this, all merged cells need to be the same size.”
Method #1: Manually Locate The Merged Cells and Unmerge Them
Consider the dataset below with merged cell range D4:D6 and a formula in cell D2.
When we double-click or drag the fill handle in cell D2 to copy the formula down the column, Excel does not copy the formula down the column but returns a warning message: “To Do this, all merged cells need to be the same size.”
This error occurred because there are merged cells in the target cell range in column D.
How To Fix
We use the following steps to fix the error:
- Select the merged cell range D4:D6.
- On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
The merged cells are split into separate cells:
When we double-click or drag the fill handle in ell D2 to copy down the formula, the formula is copied to the cells down the column, and the warning message does not come up.
Note: This method is convenient if you have a small dataset with only a few merged cells in the target range that you can quickly locate. However, it is inconvenient if you are working with a large dataset with many merged cells in different rows and columns.
You can use the following method if you have a large dataset with many merged cells spread out the dataset.
Also read: Why is Merge and Center Grayed Out?
Method #2: Use the Find and Replace Feature to Locate Merged Cells, then Unmerge
If you are working with a large dataset and you get the ‘To Do this, all merged cells need to be the same size’ error, you may not be able to easily locate the merged cells that need to be unmerged easily.
You can use the Find and Replace feature to quickly identify the merged cells in the dataset before unmerging them.
Suppose we have the following dataset of invoice numbers:
Notice that the cell ranges A4:A5, A8:A10, and A14:A15 are merged.
Let’s try to sort this dataset from lowest to highest:
- Select the dataset.
- On the Home tab, click the Sort Smallest to Largest button on the Sort & Filter group.
Excel does not sort the dataset but instead displays a warning message as shown below:
How to Fix
You can use the below steps to find all the merged cells and unmerge them:
- Select the cell range A2:A15 in which you want to find the merged cells.
- On the Home tab, open the Find & Select drop-down on the Editing group and choose Replace.
Alternatively, you can press Ctrl + H.
- Click the Options button on the Find and Replace dialog box to expose more options.
- On the expanded Find and Replace dialog box, click the Format button to open the Find Format dialog box.
- On the Find Format dialog box, open the Alignment tab, select the Merged cells option under the Text Control section, and click OK.
- Click the Find All button on the Find and Replace dialog box.
- Press Ctrl + A to select all the merged cells. Notice that the references for the merged cells at the bottom of the Find and Replace dialog box are highlighted in blue:
Also, notice that the merged cells in the dataset are selected:
- On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
All the merged cells are unmerged, and you can now sort the data without the error.
You may have noticed that when you unmerge cells containing a value, the value occupies the topmost cell, and the rest of the unmerged cells remain blank, as shown below:
You can use the method below to unmerge cells having a value and fill them with the original value.
Also read: How To Make All Cells The Same Size In Excel
Method #3: Unmerge Cells and Fill them With the Initial Value
Consider the dataset below with merged cells containing values highlighted in yellow.
If we try to sort that dataset, we get the warning message” To Do this, all merged cells need to be the same size,” as shown below:
How to Fix
We must unmerge cells in column B to get rid of the error.
We want to unmerge the cells and fill them with the original value.
We use the following steps:
- Select the cell range B2:B11 containing the merged cells causing the error.
- On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
After unmerging, we are left with blank cells in column B.
To fill the blank cells with the initial value, use the steps below:
- Select the cell range B2:B11.
- On the Home tab, open the Find & Select drop-down on the Editing group and choose the Go to Special option.
Alternatively, press Ctrl + G and click Special on the Go To dialog box that appears:
- On the Go To Special dialog box that appears, select Blanks and click OK.
All the cells in the target cell range that remained blank after unmerging are selected:
- Press the equal sign (=) key to enter the equal sign symbol in the selection’s active cell:
- Press the up arrow key to enter the reference of the cell immediately above the active cell into the active cell:
- Press and hold down the Ctrl key and press Enter.
Note: The formula entered in the active cell was copied to all the blank cells so that each cell points to the cell immediately above it and copies its value.
Once the formula has copied the value from cell above, you can remove the formula by copying the cells and pasting them as values only.
Also read: Can’t Push Objects Off the Sheet in Excel – How to Fix!
Method #4: Unmerge Cells in the Entire Worksheet
If you want to unmerge all merged cells in the entire worksheet at once, use the steps below:
- Click the Select All button to the left of the column headings and at the top of the row headings.
- On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
The process splits all merged cells in the worksheet into separate cells.
How to Add the Unmerge Cells Command Button to the Quick Access Toolbar (QAT)
You can add the Unmerge Cells command button to the Quick Access Toolbar (QAT) for quick access using the steps below:
- Click the QAT’s Customize Quick Access Toolbar button (the downward-facing arrow icon).
- On the drop-down list, choose More Commands.
- On the Excel Options dialog box that appears, open the Choose commands from drop-down and select All Commands.
- Scroll down the commands list, select the Merge Cells command, and click the Add button.
The Merge Cells command is added to the box next to the Add button:
- Click OK.
The Merge Cells command button is now added to the QAT and can be quickly accessed by clicking it.
Note: We recommend merging cells sparingly, as it can cause issues when sorting or manipulating data. It’s usually better to keep cells separate and use other formatting options, such as centering or bolding, to achieve the desired appearance.
As you continue working with Excel, consider avoiding unnecessary merging of cells, as this can lead to complications when sorting or filtering data.
Instead, explore other formatting options like cell alignment, borders, and shading to achieve your desired layout. This way, you can ensure your worksheets remain compatible with Excel’s various features.
Pro Tip: A better way to merge cells in Excel without getting into all the issues that come with merged cells is by using the Center Across Selection option. It makes it look as if the cells have been merged, but it doesn’t actually merge the cells.
This tutorial explained four techniques for resolving the error, “To Do this, all merged cells need to be the same size.”
We hope you found the tutorial helpful.
Other Excel articles you may also like: