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.
Merging cells in Google Sheets can make your data look organized and presentable. Whether you're looking to combine text from multiple cells or simply format your spreadsheet better, here's everything you need to know about merging cells in Google Sheets, from standard tools to an enhanced extension.
Standard Merge Cells in Google Sheets: pros & cons
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:
- Merge all to combine all selected cells into one:
- Merge vertically to join selected cells in each column:
- Merge horizontally to join selected cells in each row:
- Merge all to combine all selected cells into one:
So what's the catch?
Well, the most significant one is data loss.
As you can see, 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.
If you'd rather preserve all values, let's explore other options of merging cells in Google Sheets without losing any data.
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 &mdash first and last names &mdash 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 one more function 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 ;)
Combine cells in Google Sheets using JOIN function
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 it still requires some formula knowledge. Empty cells that may appear in the range will also be combined and appear in the result as an extra space. 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.
7 reasons why Merge Values is your best choice to merge cells in Google Sheets without losing data
- 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:
Tip. You will also find this add-on in Power Tools which includes 40+ various tools for Google Sheets (combine, split, sum by color, etc.).
- 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!
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.
Practice spreadsheet
Merge cells in Google Sheets without losing data (make yourself a copy)
