How To Merge & Unmerge Cells in Google Sheets?

I struggled with structuring and organizing data in Google Sheets for a long time until I discovered the handiest feature, i.e., merging and unmerging cells.

It impacts your workflow by efficiently managing and organizing data. With the help of the merge/unmerge feature, you can easily structure your spreadsheets, format headers, or make a table.

You can do so in just a few clicks. Let me show you four different methods. I shall also discuss its limitations. So, read along.

Ways To Merge Cells in Google Sheets

Method 1: Using Format Option

1. Click and select the cells to merge.
2. Go to “Format” from the menu bar.
3. Choose from “Merge Vertically, Horizontally, or All”.

Remember, once merged, the data will present only the top-most value of your cell. Therefore, choose wisely.

Method 2: Using Merge Button

1. Click and select the cells you want to merge.
2. Go to the “Merge Cells” icon next to the Borders icon.
3. Select your desired option from merging Vertically, Horizontally, or All.

The icon will only activate after you select the desired cells to merge. So, don’t panic if you find it greyed out when you open Google Sheets.

Method 3: Using a Formula

There are more advanced ways in which you can use formulas to merge cells. These add a layer of customization and offer more control over data. So, there are various formulas you can explore in Google Sheets.

Let me touch upon each.

Ampersand(&) Operator

This formula enables you to merge cells with texts and values. To merge data from two cells to appear into a single cell:

1. Select an empty cell where you want the merged data to appear.
2. Enter the formula =cell range & “ ”& cell range.

For instance, if cell A1 contains the value December and cell B1 contains 2023. After entering the formula =A1 & “ ” & B1, you will get the result December 2023 in the cell that contains the formula.

Remember to enter the spaces and commas correctly when entering the formulas.

CONCATENATE Function

For a simpler and more structured approach, use the CONCTENATE formula. This formula will help you merge multiple values or strings into a single cell.

1. Select an empty cell to enter the formula.
2. Enter “=CONCATENATE(A1, “ ”, B1)“.

Although this formula helps you achieve the same result as the ampersand operator formula, it is simpler to remember.

Join Function

Essentially, all these formulas generate the same results, but you might find one simpler than the others to remember. All these functions enhance flexibility, and you don’t have to hover to find menu options.

Simply enter the formulas, and you’ll get your desired results. So, here’s how you can use the JOIN formula:

1. Select an empty cell where you want the merged data to appear.
2. Enter the command “=JOIN(“ ”, cell range)“.

Method 4: Using Add-Ons

Add-ons are third-party tools that will enable you to merge and enhance the functionality of your Google Sheets. These add-ons merge cells without data loss and elevate collaboration.

You can also add auto-merge functions to your Google Sheets and prevent data loss. Follow the instructions to specify the columns or rows you want to merge. This add-on will immediately merge data without any data loss.

Steps To Unmerge Cells in Google Sheets

Umerging a cell is as simple as merging it. Often, you may want to retrieve the structure of the sheet to the original, and this is where unmerging cells help you.

Follow the below simple steps:

1. Select a merged cell that you want to unmerge.
2. Select Format > Merge Cells > Unmerge.

That’s it. There is also a keyboard shortcut for unmerging cells. Press Alt + O, then M on Windows, or Command + Option + O, then M on Mac.

Unmerging All Cells At Once

The unmerge-all option separates merged cells into their original form. The unmerging cells all-at-once option is helpful when you want to create a standard format for the entire sheet or when you accidentally merge the cells across the sheet.

1. Select a merged cell.
2. Press Ctrl + A (Command + A in Mac) to select the entire data set.
3. Hold the Ctrl key and press A to select the entire sheet again.
4. Go to Format from the menu bar, followed by Merge > Unmerge.

Limitations of Merged Cells

While merged cells seem convenient, there are certain shortcomings to them. The biggest pitfall is data loss; this option can also affect data analysis and transform the behavior of cell navigation.

Therefore, consider all your requirements and pitfalls before using the merge cells feature.

Here are some negative impacts that the feature may have:

• Data Loss: When you merge cells, only the top-most value in the top-left cell is retained.
• Data Analysis: Merging cells makes it challenging to analyze data and leads to inaccurate results or complicated application of formulas.
• Cell Navigation: When you merge cells, navigating may be confusing because of the disturbed formatting of the sheet

Frequently Asked Questions

Wrap Up!

Efficiently organizing data can positively impact your workflow. While it is common to merge cells to organize and structure data, it has its shortcomings.

So, if you think merging cells isn’t working for you, try different options like text formatting and alignment.