We have the below table containing a single column A in a excel spreadsheet.
Now we will merge each cell containing student name with it’s corresponding adjacent cell in column B. Like select A1 & B1 together and click ‘Merge & Center’ under ‘Alignment’ Category in Home tab.
Do the same for all below cells till A10 & B10.
Now the table will look like follows.
Now select the column header and Click ‘Sort & Filter’ available in Home tab. Then select the sorting order as required.
We have selected to sort our list in ascending order which is A to Z as follows.
Now let’s create another column named ‘State’ as follows.
Now if we want to sort our list based on any column with the same process mentioned above, we will be getting the below error since currently all cells in our table are not of same size.
Now we have to merge each cell in column C with it’s right adjacent cell in column D so that in State column the cells would be of same size of the Student’s name column. Now in both columns all cells are of same size like each cell consisting of 2 cells merged as follows.
Now you need to select the cells except header and sort. It will work now. As below we have selected A2:C10 and sorted in descending order that is Z to A.
See how perfectly it has been sorted now. Remember the thumb rule, to sort merged data in Excel, you need to ensure all cells you are sorting should be of the same size. Except that, excel will not allow you to sort.
The other option to sort the table containing some of the merged cells is to un-merge those by selecting the entire table and un-select ‘Merge & Center’ under ‘Alignment’ Category in Home tab.
This will un-merge all merged cells but this will also leave some blank cells which you need to either delete or add the desired value manually. In case of vertically unmerged cells you can also first select the entire range of cells containing the recently unmerged blank cells and click ‘Go To Special’ Under ‘Find & Select’ in ‘Home’ tab.
Then in the ‘Go To Special’ Pane select ‘Blanks’ and click OK. Now it will highlight all blank cells in your selected range and the top most blank cell is selected. It will reduce your effort to manually find the blank cells for bigger range.
Now type an equals(=) mark into the Formula Bar and press the up arrow to refer the cell which locates above the first blank cell, and then press the Ctrl + Enter key simultaneously.
It will automatically populate all of the highlighted cells with similar formulas to fill up all blank cells with the value above each of them.
Now you should be able to sort as the cell sizes are also same but with this technique you are noway sorting merged cells as you have already unmerged all cells before sorting.