How to Merge Rows in Excel

To merge rows in Excel without losing data, combine the values into one cell with a formula such as CONCAT, TEXTJOIN, or the ”&” operator (for example, =A2&" "&A3), then paste the result back as a value. The Merge & Center button on the Home tab joins selected cells visually but keeps only the top-left value and deletes the rest, so use it for layout only — not for combining data.

If you use Excel frequently, you might have encountered situations where you need to merge data from multiple rows into a single one.

Merging rows in Excel is a useful technique for combining data in a spreadsheet, saving time and effort while also making it easier to analyze and present information.

In this blog post, we will delve into the different ways you can merge rows in Excel, both manually and automatically, using built-in features and formulas. By the end, you’ll have a solid understanding of how to combine rows in Excel and be equipped to tackle diverse data scenarios with confidence.

Introduction

Excel is one of the most widely used spreadsheet software in the world, used by professionals and students alike for different purposes. One of the common tasks users often find themselves doing is merging rows of data. When faced with large amounts of data, that are often time-consuming to process, it is faster and more efficient to merge rows together. In this blog post, you will learn how to merge rows using different methods that are built-in with Excel.

Method 1: Merge cells manually

The first method that we will explore in this post is merging cells manually. This is the most straightforward method to use when you have a small number of cells that need to be merged.

Step 1: Select the cells you want to merge

Highlight the cells you want to merge by clicking and dragging on the cell or cells of interest.

Step 2: Merge the cells

Use the Merge & Center button located in the Home tab of the Excel Ribbon. Click on the dropdown arrow next to it, then select “Merge Across” or “Merge Cells” to complete the process.

Keep in mind that this method is for layout, not for combining data. When you merge cells that all contain values, Excel keeps only the value in the upper-left cell and discards everything else. If you need to keep the contents of every cell, use a formula instead — see how to merge cells without losing data for a step-by-step walkthrough. You can learn more about the visual formatting itself in our guide to Merge & Center in Excel.

Method 2: Use CONCATENATE function

Another way of merging rows in Excel is by utilizing the CONCATENATE function. This function combines text from different cells into a single cell and separates this text value by a delimiter that you supply. (In Excel 2016 and later, CONCATENATE has been replaced by the CONCAT function, though CONCATENATE still works for backward compatibility.) You can also use the TEXTJOIN function, which lets you set one delimiter and ignore empty cells automatically, or simply join values with the ”&” operator. For more ways to stitch text together, see our guide on how to combine text in Excel.

Step 1: Select a column where you want to merge rows

Select the column which you want to merge cells under for your table.

Step 2: Input CONCATENATE formula

Input “=”, then CONCATENATE, then open parenthesis “(” then select the cells you want to combine, separated by commas. Type the delimiter within quotations. For example, =CONCATENATE(A2," – ",A3) combines the text of cells A2 and A3, separated by a hyphen. To merge several rows at once with a single, cleaner formula, try =TEXTJOIN(" – ",TRUE,A2:A5), where the second argument (TRUE) tells Excel to ignore any blank cells in the range. The ”&” operator works the same way for a quick join: =A2&" – "&A3. After the formula returns the merged text, copy the result and use Paste Special > Values if you want to keep the combined text and delete the original rows.

Method 3: Use Power Query

If you have Excel 2010 version or above, you can avail yourself of the merge feature found in the Power Query (Get & Transform) function.

Step 1: Load the data into Power Query

Select the manual option in the Load Table promt that automatically appears upon opening Power Query. After this, select Load and turn the two columns into one column of combined data.

Step 2: Merge columns

Select the “merge columns” feature and select your delimiter. This feature allows you to join two or more columns into one column.

Power Query is also the best tool when you need to consolidate many rows into one per group — for example, combining every line item that shares the same customer or order ID. Use the Group By feature on the Transform tab: choose the column to group on, then add an aggregation such as a combined text value (using the “All Rows” or “Sum” operation, or a custom column with Text.Combine). This collapses repeated rows into a single summary row without manual work. For related approaches, see how to consolidate rows in Excel and how to combine duplicate rows.

Conclusion

Merging rows in Excel is an essential skill that anyone can use to make working with spreadsheets easier and more efficient. By following the various methods outlined in this blog post, you’re now empowered to tackle Excel spreadsheets no matter what the data looks like.

Best Practices for Merging Rows in Excel

While merging rows may seem straightforward, there are some best practices to keep in mind to ensure that your data remains accurate and easy to understand.

Keep a backup copy of your data

Especially if you’re working with a large amount of data, it’s always a good idea to make a backup copy of your spreadsheet before merging any cells in case something goes awry.

Ensure data consistency

If you plan to merge cells with data, it’s essential to ensure consistency within the data. For example, if you’re combining information from two cells, make sure they hold data of the same type and format such as text or numbers.

Avoid large numbers of merged cells

While merging cells can help to make your data easier to read, it’s important to avoid merging too many cells at once. Merging many cells can make the worksheet difficult to navigate and make it more prone to errors.

Use the right tool for the job

In Excel, there are different methods for merging rows, and it’s important to use the method that best suits your needs. Consider the size of your dataset and the type of information you’re working with before deciding on a method to merge rows.

Final thoughts

Merging rows in Excel can be a great way to save time and streamline your data analysis. Hopefully, this guide has given you the tools and knowledge necessary to merge rows in Excel quickly and effortlessly. Remember to follow the best practices outlined in this guide to keep your data accurate and easy to read.

Frequently Asked Questions

Here are some common questions you might have about merging rows in Excel.

What is the difference between merging and centering cells and merging cells?

Merging and centering cells are two separate actions in Excel. Merging cells allows you to combine two or more cells into one single cell, while centering cells align data within the cell. So, merging and centering cells effectively merges data from cells and then centers the data in the newly merged cell.

How do I merge rows without losing data?

Merge & Center keeps only the upper-left value, so it is not safe for combining data. Instead, write a formula in an empty cell that joins the rows — for example =TEXTJOIN(" ",TRUE,A2:A4) or =A2&" "&A3 — then copy the result and use Paste Special > Values before deleting the original rows. This preserves every value in one cell. See merge cells without losing data for the full method.

Should I use CONCAT, TEXTJOIN, or the ”&” operator?

All three combine values, but they suit different jobs. The CONCAT function (and the older CONCATENATE) joins a list of cells but repeats the delimiter manually. The TEXTJOIN function is best for merging a whole range because it applies one delimiter automatically and can skip blank cells. The ”&” operator is fastest for joining just two or three cells, such as =A2&" "&B2.

Can I unmerge cells in Excel?

Yes, you can unmerge cells that you have previously merged in Excel. Simply select the merged cell, then click the “Merge & Center” button in the “Alignment” group of the “Home” tab. Choose “Unmerge Cells” from the dropdown option.

Why does Excel show an error message when I merge cells?

If you try to merge cells that contain different types of data, such as text and numbers, Excel may display an error message. This is because Excel cannot merge cells with different types of data. Ensure you look out for such errors before merging cells.

What is the limit to the number of cells I can merge in Excel?

The number of cells you can merge depends on the number of cells that can fit in a single worksheet. In Excel 2019, the maximum number of cells you can merge within a single worksheet is approximately 1,048,576 cells. However, we recommend that you merge cells only when necessary to avoid making your spreadsheet difficult to read and manage.

What happens to the data in the merged cells when I sort a table in Excel?

When you sort a table with merged cells, Excel sorts the first cell in the merged range, based on the order you have specified. The other cells in the merged range will remain associated with the corresponding data rows, but their position in the sorted data set may change.