We can merge cells horizontally or vertically in Google Sheets. When it comes to sorting, we can’t sort vertically merged cells in Google Sheets.

So I never recommend merging cells for formatting purposes.

I do merge cells to create formats, and it never comes into use in data manipulation.

In concise, if you wish to do data manipulation, never merge cells, in Google Sheets or other similar spreadsheet applications.

I already have a table that contains vertically merged cells. Now I wish to sort it with minimum effort.

Is there any workaround I can try?

Yes! This post contains two workarounds to sort vertically merged cells in Google Sheets.

Both the workaround follows the same logic. But one returns the output in a new range, whereas the other returns the result in the existing table range.

Here is the table. It’s in the range A1:B15.

Now to the workarounds.

Workarounds to Sort Vertically Merged Cells In Google Sheets

Please take a look at item “A” in A2:A5. It’s in cell A2 only. Other cells in the range, i.e., A3:A5, are blank.

I mean =A2 will return “A” and =A3 or =A4 or =A5 will return a blank. As you may already know, it is going to be an issue in sorting.

What’s the solution?

Using a formula we can repeat the blank cells with the non-blank cells from above.

I already have a formula for you here – Array Formula to Fill Blank Cells With the Values Above in Google Sheets. Just read it and move on as I am not following that formula in this tutorial.

I am going to use a slightly different formula that seems even cleaner. The same is also used in my related tutorial here – How to Use Sumif in Merged Cells in Google Sheets.

We can convert the merged column range D2:D15 to an unmerged column range. For that, use the following formula in cell D2.

=ArrayFormula(lookup(row(A2:A15),if(len(A2:A15),row(A2:A15)),A2:A15))

This is the key!

I am not attempting to explain this formula because you can find the same in the above SUMIF tutorial.

Regarding modifying the formula for another range, it only contains the cell ranges A2:A15. So you won’t face any issues editing it for a different column range.

Sorting Merged Cells Using Manual + Automatic Approach (Workaround 1)

You only need to follow this workaround approach in the following two scenarios.

• If you want to keep the merged range A2:A15 as is or;
• If the range A2:A15 is numeric.

Steps:-

1. Select A2:A15 and go to Format > Merge cells > Unmerge. You can unmerge selected cells using the shortcut icon from the toolbar also.

2. Select D2:D15 and right-click to “Copy”.

3. Right-click cell A2 and “Paste values”.

4. Select A2:B15 and sort the data using the Sort menu. Then, if you want, merge the cells again.

At this point, you can delete the formula in cell D2.

Full Formula Approach (Workaround 2)

It is the second method to sort vertically merged cells in Google Sheets.

This approach is fully formula-based.

So the output will be in a new range and won’t be merged. But we can bring the merged effect if you print the table.

I’ll explain that later.

Steps:-

1. Modify the D2 formula slightly to add the range B2:B15 to it. The syntax is ={D2_formula,B2:B15}.

So the formula will be;

={ArrayFormula(lookup(row(A2:A15),if(len(A2:A15),row(A2:A15)),A2:A15)),B2:B15}

2. Here we can use the function SORT to sort the table.

I am enclosing the D2 formula with the SORT function to sort column 1 (Item) in ascending order and column 2 (Qty.) in descending order.

=Sort({ArrayFormula(lookup(row(A2:A15),if(len(A2:A15),row(A2:A15)),A2:A15)),B2:B15},1,true,2,false)

Better to use it in cell A2 in a new sheet tab.

Didn’t get?

Assume the above table (A2:B15) and formula (D2) is in Sheet1. In Sheet2, copy-paste the field labels using the following formula in cell A1.

=ArrayFormula(Sheet1!A1:B1)

Now in cell A2, insert the following formula, which was in cell D2 in Sheet1 earlier.

=Sort({ArrayFormula(lookup(row(Sheet1!A2:A15),if(len(Sheet1!A2:A15),row(Sheet1!A2:A15)),Sheet1!A2:A15)),Sheet1!B2:B15},1,true,2,false)

In this formula, the only difference is the sheet name with the range.

Because the table is in Sheet1 and the formula is in Sheet2.

That means referencing a table in a different sheet.

Conditional Formatting to Bring Back the Merged Look

The workaround two that I have explained to sort vertically merged cells loses the merged feel (please refer to the image above). It is because of the repeated values.

Here you can use conditional formatting to hide those unwanted values. For that, you require a formula rule to insert in the relevant panel.

Steps:-

1. Select Sheet2!A2:A15.

2. Go to Format > Conditional formatting.

3. Under “Custom formula is”, insert the below rule (formula).

=countif($A$2:A2,A2)>1

4. Under formatting style, set font color to “White” and background color to “None”.

That’s all about how sort merged cells in Google Sheets.

Thanks for the stay. Enjoy!