Data sorting in Microsoft Excel is one of the most important yet tedious processes to make your data sheet look presentable and comprehensible. Everything is fine until an error message appears that says, “To do all this, the merged cells need to be the same size.” If you have ever encountered this message, you know how annoying it is to get rid of it.
Here are a few methods that will help you fix this error and make working with Microsoft Excel easier.
There are two easy ways to fix the error:
1. The first method involves using the unmerge cells and sort command.
2. The second method involves using a VBA code.
What Is the “To Do This All, the Merged Cells Need to Be the Same Size” Error?
The “To do this all, the merged cells need to be the same size” error is common and every person who works with Microsoft Excel comes across it. This error message suggests that you can only choose merged cells that are the same size. To sort or filter them, you must have combined cells that were created using the same number of rows and columns.
Why Do You Get This Error?
To understand how we can solve the error, we first need to understand why this error appears. Microsoft Excel worksheet does not sort the range when you try to sort your data. This condition opens a dialogue box that says, “ To do all this, the merged cell needs to be the same size.” This issue occurs if you have previously merged some cells but not all in the sort range or you merged all the cells in the range and they are not the same size.
How to Fix This Error?
There are two ways to fix this problem. Let’s look at them one by one:
Using Unmerge Cell and Sorts Command – Windows
To fix this error using this method, you need to either break all the range’s merged cells or merge them all so that they are all the same size. The amount of rows and columns that each merged cell in the range takes up must match the other merged cells in the range.
Here is the step-by-step process that will help you get rid of the error when you are working on Windows.
Step 1: Search for Merged Cells
Simply select the entire spreadsheet by clicking once on the tiny triangle in the top left corner of your spreadsheet, or press Ctrl+A to select all cells.
Step 2: Click on the Home Tab
You will find this tab in the upper right corner of your worksheet.
Step 3: Click on the Find and Select Icon
Doing so will open a list of options where you need to click on “Find and Select.”
Step 4: Click on the Find Icon
Clicking on the “Find” icon will show you a pop-up with a list of functions.
Step 5: Click on the Options Button
The pop will have an “Options” button on the button right corner.
Step 6: Click on the Format Button
You will find the “Format” button next to the “Find What” search bar. Doing so will show a new pop-up and give you some more options.
Step 7: Select Alignment and Click on the Merged Cell
Check if the check box contains a checkmark rather than a line. Then click OK to confirm the format settings.
Step 8: Click on Find All
A new pop-up will appear in which you have to select the “Find All” button that finds all the merged cells in your worksheet.
Step 9: All Merged Cells Will Be Listed
Doing so will show all the merged cells at the bottom of the window. If you click on a particular result, it will take you to that cell.
Step 10: Unmerge the Cells
Now that you have found all the merged cells, it is time to unmerge them. Select all the merge cells shown in the result by pressing shift and that particular merged cell.
Step 11: Click on Merge and Center
Go to the Home tab and click on the drop-down next to the merge and center tool. This will open a list of options where you have to select unmerge cells. Doing so will unmerge all the merged cells, and your error is now fixed!
This is how you can fix this error on Windows. Next, we will see how to get rid of this error if you are working on a Mac.
There isn’t a quick way to locate merged cells in Excel if you’re using a Mac. As the Format option is absent when using the Find and Replace tool, you cannot search for cells depending on how they have been combined.
The only solution is to remove all combined cells from your sheet because it is difficult to look for merged cells.
You can follow these steps to unmerge cells in Excel for Mac.
Method 1b: Fixing the Error in Excel for Mac
Step 1: By clicking in the blank space at the top left of your spreadsheet or pressing Cmd+A, you may select the range of cells you want to delete from the merged cells or all the cells.
Step 2: Click the drop-down next to the Merge & Center icon on the Home tab.
Step 3: Select all the unmerged cells, and all of the cells in your selected cells will be unmerged.
Once you have unmerged all your cells, you will have blank cells which need to be filled with a value. Follow these steps to write proper values in the blank cells.
Step 4: Click on Ctrl+G while the cell range is still selected, and then click the “Special” button visible on the pop-up to fill them with their appropriate pre-unmerging values.
Step 5: Choose the Blanks option. Type = and choose the value of the cell above it after all the blank cells have been selected.
Step 6: Now press Ctrl + Enter instead of enter to input the proper values into the empty cells.
Doing so makes it easier for you to sort your data and shows no error.
Method 2: Using VBA Code to Sort and Merge Cells of Different Sizes
To unmerge every cell in a worksheet, you can also use a VBA code. The cells can then be sorted and merged back together. You can sort merged cells of various sizes using the following steps.
Step 1: Press Alt + F11 to open the VBA editor
Step 2: Now, go to Insert >> Module
Insert this suggested code in the VBA editor
Sub UnmergeCells ()
Range (“B4:G11”). Select
Selection. UnMerge
Range (“B4”). Select
End Sub
Step 4: Return to your worksheet and press Alt + F8. This will open a Macro dialogue box. Now press the Run button. The combined cell will all be quickly undone.
Step 5: Place your mouse cursor in the bottom-right corner of each cell containing a value, and then double-click the Fill Handle symbol to enable AutoFiling. Choose the Whole data table after that.
Step 6:Now, select Data >> Sort & Filter >> Sort. It will display the Sort dialogue box.
Step 7: By the Sort, choose the column you need, then press the OK button. The entire data table will now be sorted according to your desired column. Now you can manually merge each of the value-containing cells.
This is how using a VBA code will help you unmerge and sort your cells and properly place desired value in them.
Wrapping Up
Now that you know of not one but two methods to fix the most annoying error in Microsoft Excel, data sorting will no longer be a challenge. All you need is to keep practicing until you master the unmerge and sort commands. This will not only save you time but also prevent you from losing your data.
As a bonus, this will make your worksheets look more systematic.
