ExcelInsider Team
To make our data look organized and clear, we sometimes need to merge rows in Google Sheets, especially in the case of grouped data. To do it, we can use the default merge option, Merge Vertically, of Google Sheets. Also, if we need to keep all the data in the merged rows without losing any, we can use the TEXTJOIN or CONCATENATE function. In this article, I will explain both of these methods to merge rows of a Google Sheets dataset.
➤ First, select cell D1 and write Merged Rows.
➤ Now, select cell D2 and insert the following formula:
=TEXTJOIN(CHAR(10), TRUE, A2:A4)
➤ Then, press Enter and drag the cursor of your mouse horizontally to merge all the other cells of the intended rows.
Merge Rows Using the Menu Bar Option in Google Sheets
Google Sheets offers a few merging options, and among them, Merge Vertically can help us to merge two or more rows. It is particularly useful to organize a dataset in case of repetitive group names.
We will use the dataset below to show how you can merge rows in Google Sheets using the Merge Vertically option.
In this dataset, we have exam data of some students on two subjects, Math and Physics. We need to merge all the Math entries as one and the Physics entries as one to organize the data.
Steps:
➤ First, select all the Math entries.
➤ Then, on the top menu bar, look for the merge icon. It is like inside a rectangle, two arrows are facing each other. Select the drop-down icon beside this icon.
➤ Now, a list of options will pop up. Select Merge vertically from the list.
➤ Finally, you will see that all the selected cells containing Math have been merged into one.
➤ Now, to make the data visually more appealing, we will change the alignment of the text to the middle.
➤ Click on the alignment changing icon from the top menu bar and select the option for middle alignment.
➤ Now, the text alignment of Math will be changed to middle.
➤ Similarly, by selecting the cells containing Physics and following the above steps, you can merge the rows for Physics too. It will make your dataset look cleaner and neater.
TEXTJOIN Function to Merge Rows in Google Sheets
If you need to merge some rows while preserving all the entries, the default Merge vertically option cannot do it. In that case, you have to use a formula using the TEXTJOIN function in Google Sheets.
We will use the dataset below to show how you can merge two or more rows in a Google Sheets dataset with the TEXTJOIN function.
This dataset contains some personal information about two people. So, we will merge the rows to make sure the information about one person stays in one cell.
Steps:
➤ First, select cell D1 and write a name.
➤ Then, click on cell D2 and insert the following formula:
=TEXTJOIN(CHAR(10), TRUE, A2:A4)
➤ Now, press Enter, and you will see that cells A2, A3, and A4 are merged into one cell yet retain all the values.
➤ Finally, drag the cursor to fill the rows for all the persons.
Use the CONCATENATE Function to Merge Rows in Google Sheets
The CONCATENATE function works exactly like the TEXTJOIN function. However, it does not support range values. Thus, you have to input the cells one by one. However, for small datasets, the CONCATENATE function can be used.
Steps:
➤ First, select cell D1 and give it a name.
➤ Then, click on cell D2 and insert this following formula:
=CONCATENATE(A2, CHAR(10),A3, CHAR(10), A4, CHAR(10)).
➤ Finally, press Enter and drag the cursor horizontally to merge all the cells of your intended rows.
Frequently Asked Questions
Is There Any Shortcut to Merge Rows in Google Sheets?
Though there is no one-step shortcut to merge rows in Google Sheets, there is a shortcut way to do it. If you are using Windows, first, select the cells you want to merge and then press Alt + O , and then M , and select the merge type you want. In case of Mac, press Ctrl + Option + O and then M . Then, from the options, select the merge option you need.
How Can I Merge Rows in the Google Sheets Mobile App
In the mobile app of Google Sheets, you can only use the Merge All option. It does not support other merging options like Merge vertically, or Merge horizontally. Still, you can merge rows in case you need to retain only the top entry. Select and hold the cell that you will start with, and then drag to select all the other cells you wish to merge. You will see a floating toolbar icon at the bottom. Then, look for an icon like two cells combining and tap it to merge the selected cells. It will merge all the cells and return the data from the top-left cell.
Can I Merge Entire Rows in a Single Click in Google Sheets While Preserving All Data?
No, there is no such option. You can merge a few rows or cells at once using the Merge all option, but it will only keep the value in the top-left cell. To preserve all of the data in every cell of your selected cells, you have to use the TEXTJOIN or CONCATENATE function and drag the cursor to get the result for all the cells of your selected rows.
Wrapping Up
In this article, we have learned to use the default Merge vertically option along with the TEXTJOIN and CONCATENATE functions to merge rows in Google Sheets. We also discussed when to use each method and in which situations they are most appropriate. Give these methods a try and share your queries with us.
