How to Combine Rows in Excel (6 Methods)

We need Excel for our day-to-day work. We process raw data and get information as per our needs. While processing data in Excel, sometimes we need to combine rows or columns. In this article, we will discuss how to combine rows in Excel.

We are going to use the following dataset consisting of 2 students’ names, IDs, and grades in a particular subject.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Methods to Combine Rows in Excel

We will discuss 6 methods to combine rows in Excel. These are below:

1. Combine Excel Rows Using Ampersand Symbol

We can easily combine rows using the Ampersand (&) symbol.

Step 1:

• Double click on Cell B8.
• Now, input the 1st cell reference, then ampersand then 2 nd cell reference, and continue. So, the input formula will look like the following:

Step 2:

• Then press Enter.

As we can see, a delimiter, like a space (" "), would make the output more meaningful. Hence, we will see how to use a delimiter in the output result.

Step 3:

• Modify the formula to add space. So, the formula becomes:

Step 4:

• Then press Enter.

Step 5:

• If we use a delimiter like Comma(,), the formula will be:

Step 6:

• Then press Enter.

Here the result is!

2. CONCAT Function to Combine Rows in Excel

We can combine rows by different functions. In this section, we will discuss the CONCAT function to combine rows.

CONCAT function combines the text from multiple ranges and/or strings. It doesn’t provide any delimiter.

CONCAT is a replacement for the CONCATENATE function. However, the CONCATENATE function is still available for compatibility issues with earlier versions of Excel.

Syntax

CONCAT(text1, [text2],…)

Argument

text1 – The text that will be joined. It may be a string, or an array of strings, such as a range of cells.
text2,… – This is optional. Those are the additional text items to be joined. There can be a maximum of 253 text arguments for the text items.

Step 1:

• Type the following formula in Cell B8.

Step 2:

• Then press Enter.

Step 3:

• When we have more than two rows of data, we can select the whole range instead of separating the text using a comma repeatedly.

The formula will be:

Step 4:

• After entering the formula in Cell B8, press Enter.

Step 5:

• Pull the Fill Handle icon to the right and get the result for the rest of the cells.

3. CONCATENATE Function to Combine Rows in Excel

The CONCATENATE function is one of the text functions to join two or more text strings into one string.

Syntax

CONCATENATE(text1, [text2], …)

Argument

text1 – This is the first item to join. The item can be a text value, number, or cell reference.
text2, … – These are optional. These are the additional text items to join. You can have up to 255 items, up to a total of 8,192 characters.

Step 1:

• Type the following formula in Cell B8.

Step 2:

• Then press Enter.

We could add a delimiter (",") too. Follow the steps below.

Step 3:

• Modify the formula to add space. So, the formula becomes:

Step 4:

• Then press Enter.

Step 5:

• We can also add space and the result would be as the following screenshot.

4. TEXTJOIN Function to Combine Rows in Excel

The TEXTJOIN function integrates text from numerous ranges strings. It also contains a delimiter. This delimiter stays between the texts and then joins them.

Syntax

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Argument

delimiter – It is a textbook string. This may be either blank or one or more characters confined by double quotes. It may refer to a valid textbook string. If any numeric value is provided, that will be considered as text.
ignore_empty – If this argument is TRUE, it skips the blank cells.
text1 – This is the 1st text object that will join. Maybe a textbook string, or any cell reference.

Now, follow the steps below to see how to use the TEXTJOIN function to combine rows.

Step 1:

• Go to cell B8.
• Type the TEXTJOIN function.
• Complete formula and the formula will be like:

• In the 1st argument use comma(,), as we want to use this as a delimiter.
• We want to ignore the empty cells so select TRUE in the 2nd argument
• In the last argument select the range we want to combine.

Step 2:

• Then press Enter.

Step 3:

• Pull the Fill Handle towards the right and we will get the result for the rest of the rows.

5. Clipboard Functionality to Combine Rows

We can easily merge the rows utilizing the Clipboard tool in Excel.

Step 1:

• Go to the Home tab.
• From the Clipboard tool find the anchor.

Step 2:

• After clicking the anchor, get the Clipboard panel.

Step 3:

• Now, select the rows we want to combine.
• Copy them by Ctrl+C.

Step 4:

• Now, select any blank cell of the sheet and double-click on that.
• We will see the Paste All option on the clipboard panel.

Step 5:

• After clicking the Paste All we will get the below result.

6. Combine Rows: Use of Merge & Center Command

In this part, we will discuss Merge & Center for combining rows in Excel. This may be applied where the same data is typed in adjacent cells and we need a better presentation.

Step 1:

• Select the cells first. We selected B5 and B6.
• Then go to the Home tab.
• From the Home tab goes to Merge & Center command.
• From the dropdown select Merge Cells.

Step 2:

• After clicking the Merge Cells, we will get a warning Pop-Up.

Step 3:

• Then press OK.

Selected two rows are combined.

Step 4:

• Again, select B7 and B8, where B8 is blank.

Step 5:

• Follow the previous procedures and get the Merged result below.

Note: In this process, only the active cell value will sustain, other cell data will be lost. So, we strongly recommend not to use this technique when you have values in different cells and all of them are significant.

Conclusion

We’ve discussed how to combine rows in Excel here. Hope this satisfies your needs. Please give your feedback in the comment box. Please visit our website Exceldemy.com to explore more.

Related Articles

• How to Combine Columns into One List in Excel (4 Easy Ways)
• Merge Columns in Excel (4 Ways)