How to Merge Cells in Excel
(Without Losing Data)

You can’t split a cell in Excel but you can combine or merge cells in your worksheet.

Merging combines two or more cells to create a new large cell. This is great for creating headlines or labels that span multiple columns or rows in your worksheet😀

Not only that, but you can also merge data in your cells too!

In this article, you will learn how to merge cells in excel with and without data. This will help you save a lot of work while making your worksheet organized and structured👍

Let’s start!

Make sure to download this free practice workbook we’ve prepared for you to work on as you tag along.

How to merge cells in Excel

Merging cells in Excel is easy especially when the cells have no data in them. It simply combines two or more cells to create a new large cell.

Here’s how to do it and a lot more👇

Open your practice workbook and you will see a Sales Report that needs a lot of formatting. Let’s try to merge the headline into a single cell to span the whole columns of the report.

1. Select the cells you want to merge. In our case, select cells A1 to G1.
2. Click the Home tab in the ribbon.
3. In the Alignment group, click the Merge & Center button.

Clicking the Merge and Center button not only merges the adjacent cells but also centers the cell.

Merging the cells makes the report look better already. You can bold the title text and increase its font size like this.

Now, it’s your turn! Try to merge cells D2 to G2 to make the Quarter 1 heading better.

Merge cells with data in each cell

Now, what if you want to merge multiple cells but each of the cells contains data in it? It’s possible all right but you may lose data in the process🙁

In our workbook, let’s merge cells A2 to C2 together.

1. Select the cells you want to merge.
2. Click the Home Tab in the ribbon.
3. Click the Merge & Center button.

Microsoft Excel will notify you that merging the selected cells keeps only the data or value in the upper left cell. The values in other cells will be discarded⚠️

4. Click OK.

Only the word “Sales” remains because it is the value in the upper left cell of the selected cells you want to merge.

It disregarded the values of the other cells in the process of merging cells.

That’s okay! We can always change the label to Sales Representatives.

How to merge cells in Excel without losing data

Excel has powerful operators you can use to help you perform different functions in your worksheet. The only operator that we are going to learn now is the ampersand (&) which serves as a text concatenation operator.

This will help you NOT lose data when merging cells and save you a lot of work.

The syntax of the & operator is very simple:

=string1 & string2 & string3 &…stringN

Let’s put this operator into action💪

Open your practice workbook. Under the Sales Representative Label, you’ll see the first and last names of the company’s Sales Representatives in separate cells.

We will use the Text Concatenation Operator to combine the First and Last Names of the Sales Representatives.

1. In cell C4, type the equal sign, then click cell A4.

=A4

2. Type the operator “&”

=A4&

3. Then click cell B4. Now the formula looks like this:

=A4&B4

4. Press Enter.

Wasn’t that amazing? 😀 The text concatenate operator concatenated the two cells. That’s just a fancy way of saying: you have combined data from different cells without having to lose data in the process.

But we are not quite finished. Full names don’t exactly look like that. Full names have space between the first and last names. We need to add another value to the concatenation: a space.

To do that, add a space between two quotation marks like this: ” ”

This is our new formula:

=A4&” “&B4

Press Enter.

Fill in the remaining rows by double-clicking or dragging down the fill handle.

Amazing, right? This operator can help you can combine data in almost any format that you want👍

To give you an idea, let’s combine names in this format: Last Name, First Name

Use this formula below:

=B4&”,”&” “&A4

Press Enter and fill in the rest of the rows.

The text concatenation operator can be used in a wide variety of situations to combine cells in any format you want without losing your data.

The combination possibilities are limitless! You can use it to combine multiple cells (not only limited to two) and other text.

Merge cells automatically

Did you know that in some situations Microsoft Excel will help you merge cells automatically? You read that right. Automatically😊

Let’s clear any data in the Full Name column in our practice workbook.

In cell C4, type “Abigail Aalderink”. This is the concatenation result that you want.

In cell C5, type “Steve Jones”. As soon as you start typing, Excel will fill in the cell with its best guess of what you want.

It will also show you a preview of what the rest of the entire column would look like if you used that type of concatenation.

Press Enter and it will automatically fill in other rows.

You can forget typing data one by one and make use of Excel’s auto-merge feature!

Free video on merging cells

Watch my video and learn how to merge cells in just 2½ minutes.

How to undo a merged cell

It’s so easy to unmerge cells in Excel. You can do that in two easy steps😊

Let’s unmerge the title cell in our practice workbook.

1. Select the merged cell.
2. Click the Merge & Center button in the Home tab.

That’s it! To unmerge cells, all you need to do is to click the “Merge and Center” button.

You can see that the cells are unmerged when the cell splits back into separate unmerged cells.

The value also returned to the left-most cell.

That’s it – Now what?

Great job! Not only that you learned how to merge multiple cells to format your tables in your worksheet but you also learned how to merge cells into any format without having to lose any data🎉

Plus, you learned the auto-merge concatenate feature in Excel that will help you save tons of work. You can skip having to manually type data into your worksheet.

Say no to manual work by learning essential functions in Excel like the IF and SUMIF functions. Don’t miss out to learn about the most popular Excel function too: the VLOOKUP function🚀

You will learn all of these when you sign up for my free online email course!

Other resources

There are other ways to combine text in Excel. There’s the CONCATENATE function and the TEXTJOIN function that are real-time savers for your work.

Did you know that you can also merge data from another Excel worksheet or workbook? Yes! You can do that using the Consolidate feature in Excel. Click this link to learn more.

Take care! 👋