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 standard merging procedure only keeps data from the upper left cell. In this Excel tutorial, we will demonstrate how to merge rows without losing data by using Excel functions, the Copy-Paste command, the Ampersand operator, Notepad, and VBA code.

Consider the following dataset with customer names in rows 6 to 12. We’ll 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

Although using Excel functions like TEXTJOIN, CONCAT, or CONCATENATE is an indirect approach, it does ensure the merging of row data without losing any valuable information.

Method 1 – Using TEXTJOIN Function

The TEXTJOIN function concatenates text from multiple cells, ranges, or columns in Excel. It is designed to work with a range, eliminating the need for manual cell input. The TEXTJOIN function can also handle blank cells and add separators when merging data from multiple rows. The function is available from Excel 2016 onwards.

Steps:

  • Select a blank cell.
  • Enter the following formula: =TEXTJOIN(", ",TRUE,B6:B12)
    using TEXTJOIN function

To keep all values in just one cell, we will apply Wrap Text:

  • Select the output cell.
  • Go to the Home tab > Alignment group > Wrap Text.
    using wrap text

In the output, a comma (,) and a space act as separators between the merged row values.

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

Method 2 – Using CONCAT Function

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

Steps:

  • Select a blank cell.
  • Enter the following formula: =CONCAT(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B11,", ",B12)
  • Go to the Home tab > Alignment group > Wrap 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, which can also be used to merge rows without losing data in Excel. Here is the formula: =CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B11,", ",B12)

using CONCATENATE function

Note

The CONCAT function is recommended for its simplicity and compatibility across all Excel versions.

When using the CONCAT function, specify each cell individually.

Read more: How to Merge Rows and Columns in Excel 


Using Copy and Paste Command

Steps:

  • Select the data to be merged.
  • Go to Home tab > Clipboard group > Copy (or press CTRL+C).
    select data for copy in clipboard
  • Select the output cell by double clicking it.
  • Click the Clipboard icon from the Home tab, which will open the Clipboard panel on the left.
  • Select the Paste All option.
    paste data using clipboard feature

This will return the following final output:

final output using clipboard feature

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


Using the Ampersand Operator

The Ampersand (&) operator is very useful for combining text, numbers, or cell references in Excel, and can be used to create a dynamic formula.

Steps:

  • Select a blank cell.
  • Enter the following formula: =B4&", "&B5&", "&B6&", "&B7&", "&B8&", "&B9&", "&B10
  • 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

We can also merge multiple rows into a single row by using the Notepad application. Although this is not a conventional approach, this method can help in the case of large datasets, because Notepad eliminates any formatting complexities present in Excel.

Steps:

  • Select data from all the rows and press CTRL+C.
    copy data

Paste the data into an empty Notepad file.
copy data into notepad

  • Customize the data as required, for example putting all the names in one line, or inserting a comma between two names.
  • Copy and paste the data back into an empty row in your Excel file by using double click.
    paste data into one row

Applying VBA Code

In the case of a large dataset, it is very helpful to use VBA code to merge rows while preserving existing data.

Steps:

Go to the Developer tab > Visual Basic (or press ATL+F11).
open visual basic application

This will open the Microsoft Visual Basic Application window.

  • Go to the Insert tab > Module.

how to insert module

  • 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
    .Merge
    .Value = Trim(val)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
 
End Sub
  • Click Save (or press CTRL+S on the keyboard) to save the file.

copy and paste the code

  • Select the rows to be merged.
  • Go to the Developer tab > Code group > Macros.

select macros from developer tab

  • From the Macro dialog box, select the code and press Run.

run the code from macros

A warning message will pop up.

  • Click OK to proceed.

Excel warning notification

The following output is returned after using the code:

final output after using vba code

Note
Be careful with the code because there’s no undo option once it’s used.

Download Practice Workbook


Frequently Asked Questions

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

Follow these steps:

  • Select the rows.
  • Copy the selected rows by pressing CTRL+C.
  • Choose the output cell.
  • Right-click on it and choose Paste Special.
  • 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?

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?

Use Excel functions like CONCAT, CONCATENATE, and TEXTJOIN, which allow combining data from various cells while preserving the integrity of different data types.


Further Reading


<< 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

 

 

ExcelDemy
Logo