To Do this All Merged Cells Need to be the Same Size – Fix!

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:

Excel prompt to do this all the merged cells need to be the same size

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.

Example of merged cells in Excel

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.”

Error message shown when trying to copy the formula down the column

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:

  1. Select the merged cell range D4:D6.
Example of merged cells in Excel
  1. On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
Click on the unmerge sales option

The merged cells are split into separate cells:

Cells have been unmerged

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.

Formula can now be copied down

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:

Data set with merged cells

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:

  1. Select the dataset.
Select the data set
  1. On the Home tab, click the Sort Smallest to Largest button on the Sort & Filter group.
click the Sort Smallest to Largest button

Excel does not sort the dataset but instead displays a warning message as shown below:

warning message To Do this, all merged cells need to be the same size

How to Fix

You can use the below steps to find all the merged cells and unmerge them:

  1. Select the cell range A2:A15 in which you want to find the merged cells.
Select the data set
  1. On the Home tab, open the Find & Select drop-down on the Editing group and choose Replace.
Click on the replace option

Alternatively, you can press Ctrl + H.

  1. Click the Options button on the Find and Replace dialog box to expose more options.
Click on the options button
  1. On the expanded Find and Replace dialog box, click the Format button to open the Find Format dialog box.
Click on the format button for find what
  1. On the Find Format dialog box, open the Alignment tab, select the Merged cells option under the Text Control section, and click OK.
Select the alignment tab in the find format dialog box
  1. Click the Find All button on the Find and Replace dialog box.
Click on the find all button
  1. 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:
Select all the merged cells that have been found

Also, notice that the merged cells in the dataset are selected:

Merged cells in the data set are selected
  1. On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
Click on the unmerge sales option

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:

cells have been unmerged

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.

 dataset with merged cells containing value

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:

Warning message when trying to sort the data set

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:

  1. Select the cell range B2:B11 containing the merged cells causing the error.
Select the data set
  1. On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
Click on the unmerge sales option

After unmerging, we are left with blank cells in column B.

All the merged cells in the range have been unmerged

To fill the blank cells with the initial value, use the steps below:

  1. Select the cell range B2:B11.
  2. On the Home tab, open the Find & Select drop-down on the Editing group and choose the Go to Special option.
Click on the go to special option in the find and select dropdown

Alternatively, press Ctrl + G and click Special on the Go To dialog box that appears:

Click on the special button in the go to dialog box
  1. On the Go To Special dialog box that appears, select Blanks and click OK.
Click on the blank option in the go to special dialog box

All the cells in the target cell range that remained blank after unmerging are selected:

Unmerged cells remain blank
  1. Press the equal sign (=) key to enter the equal sign symbol in the selection’s active cell:
Enter the equal to sign in the active cell
  1. Press the up arrow key to enter the reference of the cell immediately above the active cell into the active cell:
Press the up arrow key to enter the reference of the cell
  1. Press and hold down the Ctrl key and press Enter.
Value from cell above is entered in the blank cell

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:

  1. Click the Select All button to the left of the column headings and at the top of the row headings.
Select all cells in the worksheet
  1. On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells.
Click on the unmerge sales option

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:

  1. Click the QAT’s Customize Quick Access Toolbar button (the downward-facing arrow icon).
Click on customize quick access toolbar icon
  1. On the drop-down list, choose More Commands.
Click on the more commands option
  1. On the Excel Options dialog box that appears, open the Choose commands from drop-down and select All Commands.
Select all commands from the drop down
  1. Scroll down the commands list, select the Merge Cells command, and click the Add button.
Scroll down and select the merge cells option

The Merge Cells command is added to the box next to the Add button:

Merge cells option has been added to the quick access toolbar
  1. Click OK.

The Merge Cells command button is now added to the QAT and can be quickly accessed by clicking it.

merge cell icon in the quick access toolbar

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment