Merge cells without losing data Google Sheets

If you want to learn everything about merging cells in Google Sheets, this tutorial is for you.

In this detailed step-by-step article, I’ll tell you what the merge cells option does, the different ways you can use it, the things you need to be careful about while using it, and the common problems users face while merging cells in Google Sheets.

Let’s get started.

What Is Merging Cells In Google Sheets?

Merging cells is an option in Google Sheets using which you can combine two or more cells in a sheet to create a larger cell. It is frequently used to create headers in tables and to organize data into different categories.

Let me demonstrate the difference between normal and merged cells.

Here’s a table that lists the members of the Boys and Girls teams.

Merge cells without losing data Google Sheets

Let’s use the merge cells option to make this table easier to understand.

Merge cells without losing data Google Sheets

See the difference?

We’ve merged cells to create headers for the two teams.

This is a basic example of how you can use the merge cells option.

Let’s see how you can merge cells in Google Sheets

How To Merge Cells

Merging two or more cells in Google Sheets is very easy. Just follow these steps.

Step#1: Select the cells you want to merge.


Merge cells without losing data Google Sheets

Step#2: Click on Format in the top menu.
Step#3: Click on the Merge option.
Step#4: Click on Merge vertically, Merge horizontally, or Merge all depending on your need (I’ll explain the difference in these options shortly).

Merge cells without losing data Google Sheets

Here’s how the merged cells appear.

Merge cells without losing data Google Sheets

You can also merge cells using the Merge icon in Google Sheets.
Step#1: Select the cells you want to merge
Step#2: Click on the Merge icon to combine the selected cells.
Step#3: If you want to use the vertical or horizontal merge options, click on the arrow next to the Merge icon.

Merge cells without losing data Google Sheets

Using this simple process you can merge as many cells as you want. However, there are a few things you need to keep in mind.

Let me explain in the next section.

Types Of Merge Options In Google Sheets

There are three different ways you can merge cells in Google Sheets.

Merge cells without losing data Google Sheets

Merge all
“Merge all” turns all the selected cells into a single cell.

Merge cells without losing data Google Sheets

Here’s how you can apply it.

Step#1: Select all the cells you want to merge.
Step#2: Click on the arrow next to the Merge icon
Step#3: Click on Merge all to combine all the cells into a single cell.

Merge horizontally
Merge horizontally combines all the selected cells as rows.

Merge cells without losing data Google Sheets

Here’s how to apply it

Step#1: Select all the cells you want to merge.
Step#2: Click on the arrow next to the Merge icon
Step#3: Click on Merge horizontally to combine all the cells horizontally as separate rows.

Merge vertically
Merge vertically combines all the selected cells as individual columns.

Merge cells without losing data Google Sheets

Here’s how to apply it.

Step#1: Select all the cells you want to merge.
Step#2: Click on the arrow next to the Merge icon
Step#3: Click on Merge vertically to combine all the cells as columns.

How Merge Cells Works In Google Sheets

While there’s nothing complex about using the merge cells option in Google Sheets, you need to understand how it works.

Merge Cells In Equal Proportion

You can only merge cells vertically and horizontally in equal proportion across different columns. Let me explain this with a couple of screenshots.

You can merge cells when they’re equally selected like this.

Merge cells without losing data Google Sheets

But you can’t merge cells if they’re selected unequally across columns.

Merge cells without losing data Google Sheets

Keep Important Content In The Left Most Cell

If one cell has content and the rest are empty, the resultant merged cell will show the content unchanged.

However, if you’re merging multiple cells with different content, you need to be careful because the resultant merged cell will only show the content of the top-left cell in the selected range.

Let’s consider the table I showed you earlier.

Merge cells without losing data Google Sheets

If I merge all the cells under “Boys Team”, the merged cell will show the content of the top-left cell only, which is “Andrew”.

Google Sheets also shows you a warning sign when you’re merging cells with different content.

Merge cells without losing data Google Sheets

If you click OK, the merged cell shows you the value of the top-leftmost cell.

Merge cells without losing data Google Sheets

Avoid Merging Cells In Large Data Sets

Merge cells is primarily a formatting option that helps you make your data easier to understand. It’s fine as long as you’re using it for formatting only.

But avoid using it in large data sets, especially where formulas are used. This is a recommended practice because merged cells can mess up your formulas and cause problems in calculations.

How To Merge Cells Without Losing Data

As I’ve already mentioned, when you combine cells with different content, the merged cell preserves the value of the top-leftmost cell and removes the rest.

However, there is a method using which you can combine the values of different cells into a single merged cell without losing any data.

For this method to work, you’ll need to use a free Google Sheets Add-on.

Here’s how to do it.

Step#1: Open Google Sheets
Step#2: Click on Add-ons → Get Add-ons

Merge cells without losing data Google Sheets

Step#3: In the Add-ons library, search for Merge Values
Merge cells without losing data Google Sheets

Step#4: Click Install to add Merge Values to your Google Sheets account. Google Sheet will ask you to choose your Google account and allow access.
Merge cells without losing data Google Sheets

Step#5: Once the installation is complete, you can access Merge Values from Add-ons → Merge Values.

Now that you have the required add-on, here’s how you can use it to combine content from different cells into a single cell.

Step#1: Choose all the cells you want to merge.
Step#2: Click on Add-ons → Merge values → Start

Merge cells without losing data Google Sheets

Step#3: This opens the Merge Values settings pane where you can configure how you want the merged cells to appear.
Merge cells without losing data Google Sheets

Step#4: There are three ways you can choose to combine your cell data – in rows, in columns, or in one cell.

Merge cells without losing data Google Sheets

Step#5: You also have the option to separate the merged values in multiple ways (semi-colon, comma, space, line break).

Merge cells without losing data Google Sheets

Step#6: You can choose where to place the resultant merged cell: top-left, top-right, bottom-left, bottom-right.

Merge cells without losing data Google Sheets

Step#7: In the Options section you can choose additional ways to display your merged cell data.

Merge cells without losing data Google Sheets

Step#8: Once you’ve chosen your settings, click the Merge button.

Here’s how the merged data appears for the settings I chose.

Before:

Merge cells without losing data Google Sheets

Settings:

Merge cells without losing data Google Sheets

After:

Merge cells without losing data Google Sheets

The Merge Cells Keyboard Shortcut In Google Sheets

Google Sheets does not have a direct keyboard shortcut for the Merge option.

However, if you like using shortcuts, here’s how you can apply the merge option from your keyboard.

Step#1: Select the cells you want to merge.
Step#2: Press Alt + O → M to open the Merge option menu.
Step#3: Click on the Merge type you want to apply.

How To Merge Cells In The Google Sheets App

You can also use merge cells using the Google Sheets smartphone app. However, you don’t get the option to merge horizontally or vertically in the smartphone app of Google Sheets.

You can only apply the Merge all option.

Here’s how to use it.

Step#1: Open the Google Sheets app on your smartphone
Step#2: Open the relevant document.
Step#3: Tap on one of the cells you want to merge.
Step#4: Drag the blue border of the cell to select all the cells you want to merge.

Merge cells without losing data Google Sheets

Step#5: Now tap on the formatting icon
Merge cells without losing data Google Sheets

Step#6: Select “Cells”
Merge cells without losing data Google Sheets

Step#7: Scroll down and enable the Merge cells option to merge all the selected cells.
Merge cells without losing data Google Sheets

Alternatively, you can also merge all the selected cells by tapping on the Merge icon in the Google Sheets app.

How to Merge Names

Let’s say you have a data set in which you have a list of first and names that you want to merge in a single cell.

There are a couple of ways to do it in Google Sheets.

Method#1: Merge Names Using Add-Ons

I’ve already shown you how to merge multiple cell values using the Merge Values add-on.

You can use it to merge names in one cell as well.

Here’s how to do it.

Step#1: Select all the cells with the first and last names
Step#2: Click on Add-ons → Merge Values → Start
Step#3: Under “how to merge the selected values” choose “each selected row”
Step#4: Separate values with “Space”
Step#5: Place the results in the “right cell”
Step#6: In the Options, choose “Insert new column with results to the right”
Step#7: Uncheck the option “clear the contents of the selected cells”
Step#8: Click on Merge

This will result in a new column that has the full names of all the people in your selected cells.

Method#2: Combine Names Using Formula

You can also combine the first and last names in one cell using a simple formula

Here’s how to do it.

Step#1: Open the Google Sheet with the first and last names

Merge cells without losing data Google Sheets

Step#2: Click on the cell where you want to display the full name
Merge cells without losing data Google Sheets

Step#3: Now apply the following formula to display the full name

=[First Name cell]&””&[Last Name cell]

Here’s an example from my data set.

Merge cells without losing data Google Sheets

Step#4: When you press enter, your cell will now combine the first and last name in a single cell.
Merge cells without losing data Google Sheets

Step#5: Select this cell and drag its border down to the end of your data set.

Merge cells without losing data Google Sheets

Your data set now has the full name in every row.

Merge cells without losing data Google Sheets

How To Unmerge Cells

If you want to unmerge previously merged cells, select the cell and click on the Merge icon.

Merge cells without losing data Google Sheets

However, unmerging your cells will not return any data lost when you first merged them. It will simply move any content in the merged cells to the top leftmost cell of the selected range.

Merge cells without losing data Google Sheets

As I said earlier, you should ideally merge cells only to organize and format your data where needed. Because when you unmerge them, all your data is lost.

Conclusion

Merging cells in Google Sheets is a very useful option that can help you better organize your data and make it easier to understand.

As I’ve shown you in this article, it’s easy to apply to your sheets and comes with useful options such as merging horizontally and vertically.

Plus, you can always use Google Sheets add-ons if you’re looking for more advanced ways to merge the values from different cells.