How to Combine Rows in Excel

There are a few different ways you can combine rows in Excel. All of them are fairly easy, even for beginners. This guide will show you how to do each of them step-by-step. Read on to learn more.

4 Ways to Combine Rows in Excel

Here are the ways you can combine rows in Excel:

• Consolidate Rows
• Use the & Operator
• Merge Rows
• Use the CONCATENATE Function

Most of these also work in Google Sheets, so before we take a look at them, here is an in-depth look at Google Sheets vs. Microsoft Excel and which spreadsheet software may be best for you to use.

How to Consolidate Rows in Excel

Consolidate allows you to combine data from multiple cell ranges into a summarized table. This feature is extremely useful when you have scattered data across multiple spreadsheets that you wish to combine into one table.

Here is how to combine duplicate rows into one:

1. To consolidate data in Excel from multiple rows, open the sheets you wish to merge cells in. Here, click on the cell, which will be the top left cell of the table.

2. Now, click on Data in the top bar and then click on the Consolidate button in the Data Tools section. This will open a new window.

3. The first thing to do here is to select the Function you want to perform. In this case, we want to combine two rows in Excel, so we will choose the Sum function.
4. Now, we will add the cell range containing the data you wish to combine. Click the Up icon in the Reference textbox to add a cell range. Click and drag the cursor to select the cell range. Click on the Add button to add the cell range to consolidate rows in Excel.

5. You can also choose the Top row and Left column options to define whether the dataset has labels.
6. After configuring the consolidate function, click on the OK button.

Excel will then add the rows defined in the consolidate function and add the values to the selected cells. This function is great when dealing with a large dataset you want to summarize from different sources into a single table. This is also how you merge matching rows from 2 Excel tables without copying / pasting.

How to Combine Rows in Excel Using the Ampersand Formula

You can quickly and easily develop a simple formula to merge data from many cells. Consider, for example, a situation in which there are two columns: one contains first names and the other has last names. You may combine the data to create the whole names by using a simple formula.

Here is a formula you may use in Excel to join two rows:

1. Open Excel and head to the spreadsheet where you want to combine rows or columns using a formula.
2. Click on the cell where you want to add the merged data. Here, start typing the formula you want to use to merge data.
   1. Merge the data without spaces:

      =A1&B1&C1&D1&...N

   2. Merge the data with spaces:

      =A1&” ”&B1&” ”&C1&” “&D1&“ ”&...N

3. Press the Enter key to execute the formula after you’ve added it.
4. If the formula needs to be applied to more than one cell, click on the cell where the formula was previously placed and drag the fill handle to apply the formula to the other cells.

Once you have applied the formula, you may not want to keep the actual formula in the cell. To remove the formula and only keep the output values, follow these steps:

1. Click to select the cell you want to remove the formula from. To select multiple cells, click and drag your cursor across the cells.
2. Right-click on any selected cell and click the Copy button in the dropdown menu. You can also use Ctrl + C to copy it.

3. With the content in the cell copied to the clipboard, click on the cell where you want to paste it and right-click, opening a dropdown menu. Here, click on Values under the Paste Options.

You can use the & operator several times in one formula to repeatedly merge blocks of rows into one.

Although the war between Sheets and Excel rages on, many people prefer to use both spreadsheet software. If you’re one of those people, here is a useful guide on how to convert Excel to Sheets and vice versa.

How to Merge Rows in Excel

Sometimes, multiple cells may have the same value. Using Excel combine rows into one cell function can help you reduce the visual clutter in your spreadsheet. This method will combine the data from cells into one, meaning you may lose your data. Here is how to merge two rows in Excel:

1. Open the Excel spreadsheet and go to the table where you want to use the Excel combine rows feature.

2. The data in your spreadsheet may not be organized alphabetically. To do so, click on Data in the main toolbar. Here, click on the Sort A to Z or Sort Z to A button in the Sort & Filter section. This will sort the values according to your needs.
3. Now, click and select the cells you wish to merge together. Make sure these cells are joined by at least one side, and the selected cells create a rectangular or square shape.

4. With the cells selected, go to the Home tab in the main bar and head to the Alignment section. Click on the arrow beside the Merge & Center button and choose your desired merging option.
   1. Merge Cells: This is the basic merge option, and it merges all the selected cells and keeps the value of the top left cell. All the values in the other cells are deleted.
   2. Merge & Center: This option is very similar to the previous option, and it merges the cells and keeps the cell’s value towards the top left. Alongside that, this option also centers the value in the merged cell. This option is great for creating header labels.
   3. Merge Across: This option allows you to merge the cells horizontally. For example, if you select cells from A1 to C3, then cells A1 to C1, A2 to C2, and A3 to C3 will merge.

If you wish to unmerge the cells, follow the same steps and click the Unmerge option. If you use Google Sheets, here is a guide on How to Merge Cells in Sheets.

How to Combine Rows in Excel Using CONCATENATE

An alternative method to merge rows in Excel involves utilizing the CONCATENATE function. This function makes it possible to combine many text strings into one string. In this case, we want the first name to come first, with a comma as the separator, and the last name to come after. Here is a tutorial for utilizing Excel’s CONCATENATE function:

1. Open the Excel workbook and open the sheet where you want to use the function.
2. Click on the cell where you wish to enter the initial part of the formula, which is =CONCATENATE(.
3. The parameters in the function define the text or the cell’s address containing the text. For the first parameter, add the cell’s address for the last name value. In this case, we write it as B2.
4. Add a Comma ( , ) to separate the parameters.
5. As we want the names to be separated using a comma, we will define the comma for the second parameter. Write this as “, ” including the quotation marks. Add another comma to separate the parameters.
6. Now define the next parameter, which is the cell’s address for the first name. In this case, we write it as A2.
7. Add a Bracket “ ) ” to complete the formula and press Enter to execute it.

It is possible, if necessary, to include additional factors in the equation. Once the equation is carried out, it can conveniently be applied to adjacent cells by selecting the starting cell where the equation was inputted and dragging it using the fill handle.

Despite its capabilities as a robust spreadsheet software, Excel does come at a cost, making it an unsuitable choice for certain users. Here are the 8 best Excel Alternatives for Macs in 2024.

Wrapping Up

Those are all the ways you can combine rows in Excel. Learning a few different ways will have you making accurate and slimmed-down spreadsheets is less time.

If you found this guide useful and would like to support our site, consider checking out our premium templates. You’ll definitely find something useful. Best of all, you can use the code SSP to save 50%.

Get Premium Templates

Related:

• How to Use HLOOKUP in Microsoft Excel
• An Easy Guide to VLOOKUP in Excel
• How to Remove Rows in Microsoft Excel
• The Ultimate Guide to Excel Version History
• How to Track Changes in Excel
• Master the Excel Consolidate Features
• How to Remove Read Only from Excel Files
• Find Circular Reference Errors in Google Sheets
• How To Collapse Rows in Excel in 2 Minutes [Easy Guide]
• A Guide to Excel Dashboard + Dashboard Templates