How to Merge Rows Without Losing Data in Excel (5 Easy Ways)

In Excel, merging multiple rows, columns or cells is a common task. However, the fundamental problem in this task is that it only keeps data from the upper left cell. This limitation becomes critical when dealing with crucial data management tasks such as financial records or customer details.

In this Excel tutorial, you will learn to merge rows without losing data by using Excel functions, copy-paste commands, ampersand operator, notepad, and VBA code.

Consider a dataset with customer names in rows 6 to 12. Our task is to merge these rows and gather all the customer names into a single row.

overview image of merging rows without losing data

Here are 5 methods to merge rows without losing data in Excel:

Using Excel Functions

To merge rows without losing data in Excel, use Excel functions like TEXTJOIN, CONCAT, or CONCATENATE. Although it’s an indirect approach, these functions ensure the merging of row data without losing any valuable information.

1. Using TEXTJOIN Function

The TEXTJOIN function concatenates text from multiple cells, ranges, or columns in Excel. It is designed to work with range eliminating the need for manual cell input. Additionally, using the TEXTJOIN function, you can also handle blank cells and add separators when merging data from multiple rows. You will find this function in Excel 2016 and later versions.

To merge rows without losing data in Excel using the TEXTJOIN function, follow these steps:

  1. Select a blank cell.
  2. Write the following formula: =TEXTJOIN(", ",TRUE,B6:B12)
    using TEXTJOIN functionTo keep all values in just one cell, you will have to apply Wrap Text.
  3. Select the output cell and then go to the Home tab > Alignment group > Warp Text.
    using wrap text

In the output, you will notice a comma (,) and a space which act as a separator here while merging row values.

To ignore blank cells use TRUE and include blank cells use FALSE in the TEXTJOIN function expression.

2. Using CONCAT Function

The CONCAT function in Excel also merges text from multiple cells into a single cell. The drawback of the CONCAT function is it doesn’t handle empty cells and errors well. Moreover, you need to manually input the rows.

To merge rows without losing data in Excel using the CONCAT function, follow these steps:

  1. Select a blank cell.
  2. Write the following formula: =CONCAT(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B11,", ",B12)
  3. Go to the Home tab > Alignment group > Warp Text.
    CONCAT function with separator
    It is recommended to use data separators (comma and space) in the formula or else the output may become difficult to understand.
  • The CONCAT function is an alternative to the CONCATENATE function. You can also use the CONCATENATE function to merge rows without losing data in Excel. Here is the formula: =CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B11,", ",B12)

using CONCATENATE function

  • The CONCAT function is recommended for its simplicity and compatibility across all Excel versions.
  • While using the CONCAT function, specify each cell individually.

Read more: How to Merge Rows and Columns in Excel 

Using Copy and Paste Command

The copy and paste command in Excel is handy for moving information around. It helps to transfer data between different places in Excel or even between different applications.

To use the copy and paste command for merging rows in Excel without losing data, follow these steps:

  1. Select data.
  2. Go to Home tab > Clipboard group > Copy
    or you can press CTRL+C.
    select data for copy in clipboard
  3. Select the output cell using double click.
  4. Click the Clipboard icon from the Home tab. It will open the Clipboard panel at the left.
  5. Select the Paste All option.
    paste data using clipboard featureIt will return the following final output.
    final output using clipboard feature

Read more: How to Merge Rows in Excel Based on Criteria

Using Ampersand Operator

The Ampersand (&) operator is very useful for combining text, numbers, or cell references in Excel. You can also use the ampersand operator to create a dynamic formula.

To merge rows without losing data in Excel using the Ampersand operator, follow these steps:

  1. Select a blank cell.
  2. Write the following formula =B4&", "&B5&", "&B6&", "&B7&", "&B8&", "&B9&", "&B10
  3. Press Enter.

The final output will look like the following image.
using ampersand operator in the formula

Read More: How to Combine Multiple Rows into One Cell in Excel

Using Notepad

You can also merge multiple rows into a single row by using Notepad. Although it is not a conventional approach, this method can help in the case of large datasets. Notepad eliminates any formatting complexities present in Excel. Here are the steps:

  1. Select data from all the rows and press CTRL+C.
    copy data
  2. Then, paste the data into an empty Notepad.
    copy data into notepadYou can customize data in the notepad easily. For example, you can put all the names in one line, or you can insert a comma between two names.
  3. After that, copy and paste it back into an empty row of your Excel file by using double click.
    paste data into one row

Applying VBA Code

To save time and effort, in the case of a large dataset, it is very helpful to use VBA code to merge rows while preserving existing data.

Check the following steps to apply VBA code to merge rows without losing data in Excel.

  1. Go to the Developer tab > Visual Basic.
    Or you can press ATL+F11.
    open visual basic applicationIt will open the Microsoft Visual Basic Application window.
  2. Now go to the Insert tab > Module.
    how to insert module
  3. Copy and paste the following code into the module.
    Sub vba_merge_with_values()
    Dim val As String
    Dim rng As Range
    Set rng = Selection
    For Each Cell In rng
        val = val & " " & Cell.Value
    Next Cell
    With rng
        .Value = Trim(val)
        .WrapText = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    End Sub
  4. Click Save or you can press CTRL+S as the keyboard shortcut.
    copy and paste the code
  5. Next, select the rows and go to the Developer tab > Code group > Macros.
    select macros from developer tab
  6. From the Macro dialogue box, select the code and press Run.
    run the code from macros
  7. A warning message will pop up. Click OK to proceed.
    Excel warning notificationYou will have the following output after using the code.
    final output after using vba code
Be careful with the code because there’s no undo option once it’s used.

Download Practice Workbook
You can download the workbook to practice yourself.


Merging rows or columns in Excel is a basic skill. It is important to learn how to merge rows without losing any data due to the technical complexity associated with this feature. To merge data from different rows into a single row, you can make use of Excel functions or the ampersand operator within a formula. Another useful shortcut in this regard is using copy and paste command. Additionally, the VBA code can be of great assistance for large sheet. All these methods can help you to manage your data efficiently. If you have any suggestions or queries, feel free to ask in the comment section below.

Frequently Asked Questions

How Do I Convert Multiple Rows to One Column in Excel?

To convert multiple rows to one column in Excel, follow these steps:

  1. Select the rows.
  2. Copy the selected rows or use CTRL+C.
  3. Choose the output cell.
  4. Right-click on the selected cell and choose Paste Special.
  5. In the Paste Special dialog box, select Transpose and click OK.

This will convert the data from multiple rows into one column.

How to Handle Blank Cells During the Merging Process?

To handle blank cells during the merging process, use the TEXTJOIN function which provides the option to include or ignore blank or empty cells in a range.

How to Merge Rows With Different Data Types Without Losing Information?

To merge rows with different data types in Excel, use Excel functions like CONCAT, CONCATENATE, and TEXTJOIN. It allows you to combine data from various cells while preserving the integrity of different data types.

Further Readings

<< Go Back to Merge Rows in Excel  | Merge in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

1 Comment
  1. Thank You! This helps a lot!

Leave a reply

Advanced Excel Exercises with Solutions PDF