Here you can learn an array formula-based approach to fill merged cells down or to the right in Google Sheets.

When you merge ten cells, there will value only in the very first cell in the cell range.

By merging so, probably, you meant to convey that all the cells have the same value.

It may visually convey the meaning. But unfortunately, worksheet functions won’t understand that.

So, for the sake of data manipulation, you may require to fill merged cells in Google Sheets.

In this tutorial, you can learn how to fill merged cells vertically or horizontally in Google Sheets.

Array Formula to Fill Merged Cells Down

I have the following data in a blank Sheet.

As you can see, I have a column (A2:A11) with cells merged. Here, how can I fill the name “Tamara” in A2:A4, “Jane” in B5:B6, and so on?

Here are the steps to follow to fill merged cells down (vertically) in Google Sheets.

1. Select the cell range A2:A11 and unmerge.

2. In cell D2 insert the following Lookup-based array formula.

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

3. Select D2:D11, right-click and Copy.

4. Click cell A2, right-click, select Paste Special, and Paste Values.

This way, we can fill merged cells down using an array formula in Google Sheets.

How Does Lookup Fill the Names?

Syntax as per the above formula:

Array_Formula(
     LOOKUP(
        search_key,
        search_range,
        result_range
     )
)

search_key – row numbers from 2 to 11.

search_range – corresponding row numbers against names in A2:A11 and FALSE Boolean values in blanks (please see the image below.)

result_range – the names in the cell range A2:A11.

The Lookup requires a sorted search_range. The above formula satisfies this condition.

The next point is the logic of the formula.

As you can see, there are search_keys that won’t match in the search_range. For example, 3.

How will Lookup behave in this case? That’s the key.

If search_key does not exist, the item used in the lookup will be the value that’s immediately smaller in the range provided.

Here the search key is 3, then 2, which is an immediately smaller value, will be used for the lookup.

In concise, the Lookup function plays a vital role in filling merged cells down in Google Sheets.

Array Formula to Fill Merged Cells to the Right

In the above formula, we have used the Row function within Lookup for search_key and serach_range.

By changing that to Column, we can fill the merged cells to the right in Google Sheets. Here is how?

In the following sample data, the cells in B1:K1 merged horizontally.

Steps:

1. Unmerge B1:K1.

2. In B5, insert the following horizontal Lookup formula.

=ArrayFormula(
     lookup(
        column(B1:K1),
        if(len(B1:K1),column(B1:K1)),
        B1:K1
     )
)

3. Select B5:K5, right-click and Copy.

4. Click cell B1, right-click, select Paste Special, and Paste Values.

That’s all.

This way, we can fill merged cells to the down or right in Google Sheets.

This method is beneficial when we have a large set of data with merged cells in a column or row.

Resources:

• Copy-Paste Merged Cells Without Blank Rows/Spaces in Google Sheets.
• How to Find the Cell Addresses of the Merged Cells in Google Sheets.
• How to Use Sumif in Merged Cells in Google Sheets.
• Sort Vertically Merged Cells in Google Sheets (Workaround)