by Tired of losing data when merging cells in Google Sheets? Explore all-time favorite methods, from built-in tools to a game-changing add-on.
In this guide I'll walk you through every method, from the built-in tool to powerful formulas like CONCATENATE and JOIN that combine your data without loss. You'll also learn useful shortcuts and find answers to frequently asked questions on common merging problems. Finally, I'll reveal a powerful add-on that merges cells perfectly without sacrificing a single value. Read on to achieve both flawless formatting and intact data 💪.
Merge cells and columns in Google Sheets: Quick summary
To merge data in Google Sheets, you can use a built-in tool, special formulas, or dedicated add-on:
- The built-in tool: Select cells > Click the down-arrow next to Merge Cells > Specify a merge type.
Note: This method deletes all data except for the value in the top-left cell.
Keyboard shortcuts:
- Merge all: Alt + O + M + A (Windows, Chrome OS) / Ctrl + Option + O + M + Enter (Mac)
- Merge vertically: Alt + O + M + V (Windows, Chrome OS) / Ctrl + Option + O + M + V (Mac)
- Merge horizontally: Alt + O + M + H (Windows, Chrome OS) / Ctrl + Option + O + M + H (Mac)
- Formulas:
- Join cells with the ampersand:
=A1&" "&B1 - Merge columns:
=ARRAYFORMULA(A2:A10&" "&B2:B10) - Combine cells using CONCATENATE:
=CONCATENATE(A2," ",C2) - Combine data using JOIN:
=JOIN(" ",A2:C2) - Merge text with TEXTJOIN:
=TEXTJOIN(" ", TRUE, A2:C2)
- Join cells with the ampersand:
- The easiest way — Merge Values add-on: Select cells > Set up the merge settings > click Merge.
How to merge cells in Google Sheets (built-in feature)
When you need to combine cells in Google Sheets, the standard Merge Cells tool is often the first choice. Yet, while it's quick & may suffice for basic merging needs, it has one notable limitation that will make you think twice depending on your use case. Let me show what I mean.
To merge cells in Google Sheets using this tool, you follow these steps:
- Select the cells you want to join.
- Either click the Merge Cells icon on the toolbar to turn everything into one cell, or click the down-arrow and specify the merge type from there:
- Choose Merge all to combine all selected cells into one, Merge vertically to join selected cells in each column, and Merge horizontally to join selected cells in each row:
Merge cells in Google Sheets using shortcuts
Unfortunately, there is no default shortcut to merge cells in Google Sheets, but you can use a sequence of key presses to navigate the menu:
- Select the cells you want to merge.
- Press Alt + O + M (Windows, Chrome OS)/ Ctrl + Option + O + M (Mac) to open the Format menu and select Merge cells:
- Press one of the following keys for your desired merge type:
- A for Merge all (merges all selected cells into one).
- V for Merge vertically (merges cells in each column).
- H for Merge horizontally (merges cells in each row).
The full sequence to Merge all would be Alt + O + M + A (Windows, Chrome OS) / Ctrl + Option + O + M + A (Mac), to Merge vertically: Alt + O + M + V (Windows, Chrome OS) / Ctrl + Option + O + M + V (Mac), and for Merge horizontally: Alt + O + M + H (Windows, Chrome OS) / Ctrl + Option + O + M + H (Mac).
So this method looks pretty easy, what's the catch?
Well, the most significant one is data loss 😥.
The standard Google Sheets Merge Cells tool keeps the content of the upper-left cell only. All other values get lost. Hence, its only ability is to merge cells without keeping all data which makes it less ideal for more complex needs, and suitable only for formatting.
If you'd rather preserve all values, let's explore other options of merging cells in Google Sheets without losing any data.
Merge cells without losing data
The quickest and most direct way to join cell content is with the ampersand (&) operator. It's designed to function as a simple connector, joining text from two or more cells into a single string.
Let's break it down with an example. I will use the ampersand operator to combine first and last names in my Google Sheets table:
- Select the cell for your combined result.
- Enter a formula like this:
=A2 & " " & C2 - Copy the formula down the column:
As you can see, the formula seamlessly joins the text from cells A2 and C2, inserting a space (" ") between them as a separator. You can choose any other separator like comma or colon, if needed.
Merge columns in Google Sheets
You can use the ampersand operator not only for joining cells, but for columns as well. To do this efficiently without dragging formulas, you'll need to wrap it in an ARRAYFORMULA function:
This method generates a new column with the merged text. Let's apply it to combine first and last names from two separate columns into a full name column.
- Select the cell where you want the combined column to begin.
- Enter the array formula:
=ARRAYFORMULA(A2:A10&" "&C2:C10)
The formula seamlessly joins the range A2:A10 (first names) with C2:C10 (last names), inserting a space (" ") between them for readability.
Combine cells in Google Sheets using CONCATENATE
The next standard option spreadsheets offer is the CONCATENATE function. It's designed specifically to combine text from two or more cells into one:
And since it merges all text strings you tell it to, no data will be lost. Let's see how it works.
I will use CONCATENATE to combine first and last names in my Google Sheets table. Each full name will go into its own cell with spaces in between:
- Select the cell where you want the combined text to appear.
- Enter this formula:
=CONCATENATE(A2," ",C2)and copy it down the column:
This formula combines the whole strings from A2 and C2 — first and last names — and adds a space " " between them.
Obviously, unlike the standard merge cells, CONCATENATE doesn't lose any data. And you can add custom delimiters between combined values.
But it's still a function. Not only does it require a learning curve, but its combined output takes up space elsewhere in your spreadsheet. If you're new to it, it may be challenging. Check our extensive guide on CONCATENATE: all the basics and lots of sample formulas are included.
Let's take a look at two more functions before we discuss an enhanced merging tool that combines the best features of both combining text strings and merging cells without all the respective drawbacks ;)
Merge cells in Google Sheets using JOIN and TEXTJOIN functions
Following the CONCATENATE function, another option provided by Google Sheets to merge cell content is the JOIN function:
Unlike CONCATENATE, JOIN combines all text strings from a range of cells using a delimiter you specify only once:
=JOIN(" ",A2:C2)
With CONCATENATE, to merge multiple values with the same separator after each record, you have to alternate each value with the delimiter over again:
=CONCATENATE(A2," ",B2," ",C2)
This difference makes JOIN formulas easier to write and manage than CONCATENATE, especially for multiple values from big data ranges.
But be aware that empty cells that may appear in the range will also be combined and appear in the result as an extra space. If your data include empty cells, it’s better to use a slightly different formula:
TEXTJOIN's main advantage is the ignore_empty argument. When set to TRUE, it automatically skips any blank cells in your range, which solves the problem of getting extra spaces or separators that you encounter with the JOIN function.
Let's use it on the same range as before:
=TEXTJOIN(" ", TRUE, A2:C2)
This formula combines the text from the range A2:C2 with a space. If any cell in that range is empty, the TRUE argument ensures it's skipped.
While these functions are a good way to merge your data, they still require some formula knowledge. If you're looking for an easy way to handle that, you should try this enhanced merging tool in the next section.
Enhanced Merge Cells: why it's better
So far, you know that the standard Google Sheets Merge Cells tool and functions like CONCATENATE and JOIN offer basic ways to combine text, but they come with limitations.
This is where the Merge Values add-on for Google Workspace steps in. It provides some extra features that standard ways simply can't match.
Install Merge Values to your Google Sheets by clicking the button below.
7 reasons why Merge Values is your best choice to merge cells in Google Sheets
- Flexible merging. Combine text from each row, column, or the entire selected range.
- Pick a place for the result. Decide where the merged results will appear: left or right of each row, top or bottom of each column, or any corner of the selected range. Or even a new row or column adjacent to your original data.
- Actual cell merging on-demand. It's up to you to merge cells where the text comes from into one cell or combine only text from these cells.
- Accepts custom delimiters & combos of delimiters.
- Ignores blanks to avoid extra spaces.
- Optional text wrapping for large merged values.
- Supports merging from multiple non-adjacent ranges.
How Merge Values combines cells in Google Sheets without losing the data
- Install Merge Values from the Google Sheets store:
- Go to Extensions > Merge Values > Start to open the add-on:
- Select all cells you want to combine text from (even if they're multiple non-adjacent ranges within the same Google Sheets tab):
- Set up the merge with drop-downs and checkboxes. Choose how to combine selected cells: in each selected row, in each selected column, or into one cell:
- Pick a standard separator, create a combo, or enter a custom one:
- Decide where the result &mdash merged text &mdash will go. Based on the selected way to combine (row, column, one cell), it can be left or right of each row, top or bottom of each column, or any corner of the selected range:
- Tick off the boxes for any additional options you need, e.g. skip empty cells so they don't get merged as extra spaces. Or insert the result into a new column/row without overwriting the original:
- Once you're all set, click the Merge button at the bottom of the tool.
As you can see, all cells are combined within each row separated by line breaks. All text is safely preserved from selected cells:
This intuitive interface is easy to navigate for both non-tech users and tech savvies, isn't it? ;)
Yes, Merge Values is not free, which might be a consideration for some. However, it offers a 30-day fully-functional trial giving you plenty of time to decide if it fits your needs. And if it doesn't, please do share your feedback in the comments section!
Click the button below to install Merge Values from the Google Sheets store:
Video: How to use Merge Values to combine cells in Google Sheets
When choosing the right tool to merge cells in Google Sheets, consider the complexity of your task and how comfortable you are with formulas. Each approach has its strength, but only Merge Values offers an unparalleled, user-friendly solution for both quick daily use and the most comprehensive tasks 😉.
FAQ: Common problems when merging cells in Google Sheets
Why does Google Sheets delete values when merging?
Merging cells combines their format, not their content. Google Sheets saves only the top-left value and removes all others.
How to merge cells in Google Sheets but keep each value in a new line?
Add CHAR(10) to your function, as it represents a line break in Google Sheets. For example, =JOIN(CHAR(10) ,A2:C2).
How do I quickly unmerge cells in Google Sheets?
Select the cells you want to unmerge and go to the toolbar: a drop-down arrow next to Merge cells > Unmerge. For more advanced capabilities, like unmerging cells and splitting the value inside by character or position, use a third-party add-on like Power Tools by Ablebits.
How do Sort and Filter work with merged cells in Google Sheets?
They don't work well and can be quite misleading. Try to avoid merging cells within your data table, or if needed, unmerge cells before sorting or filtering.
Why do merged cells disappear when exporting to Excel?
Google Sheets and Excel are different apps and they calculate row and column sizes differently. That’s why your merged cells' content can “disappear" when you move between them.
Why does text alignment look wrong after merging cells?
Your text may look off because after merging, text alignment applies to the entire new merged cell instead of the original small ones.
What value does a formula return if it refers to a merged cell in Google Sheets?
Any reference to a merged range returns the content of the top-left cell only. The other cells in the merged block are treated as empty.
Practice spreadsheet
Merge cells in Google Sheets without losing data (make yourself a copy)