How to Merge Cells in Excel with Data (3 Ways)

Merging cells is a common scenario when you might need to combine multiple cells of data in one single cell. But, sometimes merging cells along with data could be a little difficult. In this tutorial, we are going to show how to merge cells in Excel with data.

Download Practice Workbook

Problem with Merge Cells Using Merge Option

Let’s first look at some problems that arise when we directly use Merge & Center.

Now take a look at the same worksheet. Here we entered Hello in Cell B2 and Everyone in cell C2.

how to merge cells in excel with data: basic merging using merge option

Let’s merge this again. Select A2 to D2.

Highlighting the cells to be copied

Now, click Merge & Center. You will see this prompt.

prompt for merging the value

Click OK.

Merged data after clicking merge option

This time it merged the cells but it only shows the leftmost cell’s value. So, you can understand the merging option won’t be efficient for multiple cells with data. We have to take a different approach.

So now let’s see how we can rectify these issues.

Read more: How to Merge and Center Cells in Excel

3 Ways to Merge Cells in Excel with Data

If we want to merge cells and keep the data then it will be wise to use Formulas or Notepad. We are giving you 3 optimal approaches so that you can perform merging with data in Excel.

1. Merge Cells with Data Using CONCATENATE (in a different cell)

You can merge cells using the CONCATENATE function in Excel.

The formula we are using:

=CONCATENATE(B2,",",B3,",",B4,",",B5)

Notice that, here we are merging different cells with a comma separator (,). Data will be merged in one cell with commas.

Take a look at this dataset:

how to merge cells with data in excel: Data to be merged in a column

As you can see there are 4 values in different cells.

Now, select any cell and type the formula.

CONCATENATE function to merge data

Press Enter. 

Result after concatenation to merge cells with data

You will see our data is merged into a single cell D2 with a comma separator.

2. Merge Cells with Data Using Notepad

This approach is not the traditional one. Basically, we are copying multiple cells of data and pasting it to a notepad. Then, we will replace the extra space (Tab) with a single space.

Take a look at this dataset:

Dataset for merging using notepad

As you can see, there are 4 different values in different cells.  We will merge those data into two rows of a single column.

Now, copy all that data.

copy data for merging

Paste it on a notepad.

paste data on notepad

First, press Tab somewhere. Then Copy that with Ctrl+C. We will use it later.

Select all the values and click Edit.

select edit option in notepad

Click Replace. Now, paste that Tab space that you copied in Find What.

replace data of extra spaces

Now, replace it with a single space.

replace tab with single space

Click Replace All. All of your data will have a single space.

data without extra spaces

Now copy all that data and paste it on any cell.

merged data in a single column

As you can see our multiple columns of data are now merged in a single column.

Now our pasted values are in two different cells because we actually merged two columns in a single column. The main data had 2 rows and 2 columns. But we merged it into 2 rows but a single column.

3. Merge Cells with Data Using Ampersand (the & sign)

Another way to merge cells with data is using Ampersand (&).  You can merge multiple cells using an ampersand sign.

Take a look at this dataset:

how to merge cells with data using ampersand

Now, select any cell and write the formula:

 =B2&","& B3&","& B4& ","&B5

Ampersand formula for merging data

Then press Enter.

merged data into a single cell

Finally, all of our data is merged into one cell.


Conclusion

Using formulas to merge cells is the optimal way according to us. You can use these formulas to perform merging. Make sure to download our practice workbook and try it on your own. Be sure to check our website Exceldemy.com for various Excel-related articles.


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo