Watch Video – How to Merge Cells In Google Sheets (How to Combine Columns in Google Sheets)
This Article Covers:
When you work with data that has headers, sometimes, you may feel like you need to learn how to merge cells in Google Sheets.
For example, if you have the main header as Revenue and subheaders as 2016, 2017, and 2018, you can merge the three cells that make up the revenue header.
Something like shown below:
As you can see in the example above, Revenue spans across three cells that have not been merged. We have prepared this step-by-step guide for those wondering “How can you merge cells in Google Sheets?”
What Is Merging Cells in Google Sheets?
Merging cells in Google Sheets simply means combining multiple cells into one cell. There are various reasons why you would want to merge in Google Sheets if you wish to compress your data. This is also a way to show how to combine rows in Google Sheets if you want to create one cell at the top as a title.
The advantage of using merge cell Google Sheets shortcuts is that the properties of the cells you have merged are not affected.
How to Merge Cells In Google Sheets
Here are the steps for merging cells:
- Select the cells that you want to merge. If you select a row, you will merge horizontally; if you select a column, you will merge cells vertically.
- Go to the Format tab, and within the Google Spreadsheet Merge Cells options, select Merge All.
This would instantly combine two cells. It’s also how to merge columns in Google Sheets, and how to merge rows in Google Sheets.
Note: When you merge cells, it only retains the value in the top left cell. If there is anything in cells apart from the top left cells, Google Sheets shows you the prompt as shown below.
Note: Instead of navigating to the menu you can use the shortcut in the toolbar as shown in the following screenshot:
Now also have a look at the merge cells options available in Google Sheet:
- Merge All: This would merge all the selected cells and make them one. Note that you can only have contiguous cells that can be combined. If you have non-contiguous cells, merge options will not be available to use.
- Merge Horizontally: This would merge all the horizontal cells into one. Even when you select more than one row of cells, it will only merge the horizontal cells.
- Merge Vertically: This would merge all the vertical cells into one. Even when you select more than one column of cells, it will only merge the vertical cells.
If you want to combine multiple columns, just select all the cells in the columns and use the option merge cells. You can also use formulas like the ARRAYFORMULA and the UNIQUE formulas to combine columns. These two formulas will combine your columns in a new column so that you can have two separate data values.
The array formula can also be used together with the CONCAT function to combine two or more columns horizontally.
The merge cell options work the same way in the Google Sheets app for android or iOS.
Related Reading: How to Group Columns in Google Sheets
Steps to Unmerge Cells
Here are the steps to unmerge cells in Google Sheets:
- Select the merged cells that you want to unmerge.
- Go to the Format tab and within the Merge cells options select Unmerge.
This would instantly unmerge all the cells merged.
How to Merge Data from Cells in Google Sheets (with CONCATENATE).
Concatenate is a useful function in Google Sheets that you can use to merge values to create a new value. The syntax for the CONCATENATE function is written as
=CONCATENATE(Value1, Value2, …)
Let’s say we want to combine the brand name with the name of the shoe in our spreadsheet below
The first step would be to type in our CONCATENATE formula in the cell where we want the combined names to appear.
This will give us the combined name as you below. However, you will notice that the names do not have a space between them.
To add a space between the names, you can add a space in between quotations in the formula like this:
You will get your names separated with a space between them like below. You can also add commas or other punctuation in the same way. Make sure to separate everything with commas in the formula.
You can read more on our article about the CONCATENATE function in our other articles and how to split text to columns.
What to Be Aware of Before Merging Cells in Google Sheets
There are a couple of things you need to be aware of before you combine your cells. The first is that once you combine cells, the first cell (the upper left) is the only one that retains its data. You can’t retrieve the data from the other cells unless you undo the merging.
The merge cell option also does not merge data, so if you want to do that, you must instead use the CONCATENATE function.
You can’t sort columns them even if you did not merge the entire column. If you try to use the sort function, it will return an error.
The merge-all option is only available if you select a continuous range of cells otherwise, it’s greyed out.
Frequently Asked Questions
How Do You Combine Two Columns In Google Sheets?
All you have to do to combine 2 columns into 1 in is use the merge vertically option from the merge menu. To merge rows you just have to use the merge horizontally option.
Is There a Shortcut to Merge Cells in Google Sheets?
There is no direct shortcut to merge cells. However, the most common Google Sheets merge cells shortcut is by using keyboard shortcuts to open up the merge menu which is almost as quick. You just have to press Alt+O >M on windows or Control+Option+O > M on MacOS.
How Do I Merge Cells in the Mobile Version of Google Sheets?
- Select the cells you wish to merge by tapping and dragging or pulling the edge of the blue highlight box to cover all the cells.
- Click the merge cells button
Is There a Way to Merge Cells In Google Sheets Without Losing Data?
There is no solution to merge cells without losing data with the Google Sheets merge cells native function. However, you can install an add-on that will allow you to do this.
- Download and install Merge Cells
- Highlight the cells you want to merge
- Go to Extensions>Add-ons>Merge Cells
- Select how you want your data to combine from the available options
Why Can’t I Merge Cells In Google Sheets?
One of the main reasons you can’t merge cells is because they’re locked. You’ll need administrator rights on the spreadsheet to be able to unlock them.
What if You Find Google Sheets Merge Cells Greyed Out?
This may happen if you have not selected multiple cells so make sure to check that you have selected more than one cell.
Another reason why the merge cell options may be greyed out is that you do not have access to make changes in the spreadsheet, in which case you can request access from the owner of the spreadsheet.
Can I Copy Merged Cells Without Copying Their Formatting?
No, unfortunately, you can’t. It’s a bit of a bother. You’ll need to copy the text within the cell instead of the whole cell.
Also Read: How to Merge Cells in Excel.
How Do You Merge Cells in Google Sheets and Split?
You can use the SPLIT function after merging cells to split the data. We have a full guide on the split function here.
How Do I Merge Data From Multiple Sheets in Google Sheets?
You can use IMPORTRANGE to import data from several other sheets into one. Check out our IMPORTRANGE guide to learn more.
More to Learn
In this article, we have shown you how to merge cells in Google Sheets and a sneak peek at how to merge data as well. If you’re wondering how to combine rows in Google Sheets, they work the same way as we have shown you. We hope this article was a useful guide for you.
I hope you found this tutorial useful! If you did, You May Also Like the Following Google Sheets Tutorials: