How to Merge Cells in Excel:
3 Methods Explained

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In most cases, you want your Excel spreadsheet data highly segmented.

But what if you need the contents of multiple cells combined into a single cell?

You might need to print your document – or use the data in a different fashion.

Luckily, there’s a simple solution:

That’s merging the cells!

There are a couple different ways to merge cells in Excel—and they produce very different results.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s go over the 2 different merging methods to see what they do 🙂

(There’s also a rather cool bonus method we’ll cover at the end.)

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

An introduction to Excel merging

Merging, as you might expect, is simply combining multiple cells.

However, there are two different ways you can accomplish this: with a menu button and with a formula operator. As I mentioned, they have very different results.

Kasper Langmann, Co-founder of Spreadsheeto

Using the menu button combines multiple cells—but only keeps the data from one of them. We’ll see in just a moment why this might be useful.

The formula, on the other hand, keeps the contents of all the cells you select. This is likely to be more useful in a wider variety of cases. But, as you’ll see, both have their places.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Merging cells with the Merge & Center button

On the first sheet of the example workbook, you’ll see a number of companies listed near the cities and countries where they’re located.

It seems a bit redundant to use both the “City” and “Country” labels, so we’ll merge them into a single cell that spans both columns.

First, select the cells you want to merge (in this case, B1 and C1).

Then, press the Merge & Center button in the Ribbon.

Excel will warn you that only one value will be kept. Click OK.

Now, you’ll see that there’s a single cell spanning the width of two columns. “City” is no longer a good label for this column, so feel free to re-title it “Location.”

What if we decide this wasn’t such a good idea?

Just highlight the merged cell, and click the Merge & Center button again (you’ll see that it’s a slightly different color after a cell has been merged).

The cell splits back into two, and the value is moved into the left-most new cell.

You’ll notice that you don’t get the original labels back. That’s important to keep in mind if you’re going to be merging and unmerging multiple cells.

Kasper Langmann, Co-founder of Spreadsheeto

“Great,” you might be saying, “but what if I want to merge the cities and countries into single location cells? How can I keep both values?”

That’s exactly what we’re going to check out next.

How to merge cells with the & operator

Excel has a number of operators that perform different functions—the only operator we’ll be discussing here, however, is the ampersand (&), which serves as a text concatenation operator.

(Note: this is the simpler version of the TEXTJOIN function we wrote about a while back.)

Put simply, it takes two cells that contain text and puts them together without losing any of your data.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s see how that works in practice. On the second sheet of the workbook, you’ll see a First Name column and a Last Name column. We’ll use this operator to combine the two of them without losing the data.

The syntax of the operator is very simple:

The syntax of the & operator

=string1 & string2 & string3 &…stringN

You need at least two different strings to concatenate with the & operator, and you can add as many as you want.

Each string can be referenced by a cell value (like “A4”) or a specific string (like “business practice”).

Let’s try it on the first and last name of one of the employees in our sheet.

First, click into cell C2. Then, type the equals sign, and click on cell A2. The resulting formula should look like this:

Next, type “&” and click on cell B2. The resulting formula is “=A2&B2”.

Pretty simple. After hitting Enter, though, you may find that the result isn’t quite what you expected:

The operator concatenated the two cells, but didn’t put a space in between the two. We’ll have to add another value to the concatenation.

To add a space, you simply add a space between two quotation marks, like this: ” ”

Adding that to our previous formula, we get this: =A2&” “&B2

Now, plug that into cell C2, and we get:

Hit Enter, and you’ll see that this gets the result we were looking for.

You can add anything you want with quotation marks. For example, we could use a comma and select the cells in the opposite order to get a “Last name, first name”-style cell.

Kasper Langmann, Co-founder of Spreadsheeto

Here’s the formula we’d use for that:

As you can see, B2 is selected first, because it will be displayed first in the resulting cell. Next, we’ve added a comma and a space (both held within the same set of quotation marks). Then, cell A2.

Here’s what happens:

If you’d like to merge two whole columns, just use the concatenation operator and then drag the cell down, using the fill handle, to fill the rest of your blank column.

Here’s what that looks like:

In this way, you can combine two entire columns into a new one that contains all of the data, in almost any format you want.

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

You can use it to combine multiple cells (you’re certainly not limited to two) or cells and other text.

Bonus: merging cells automatically

In some situations, Excel will help you merge cells automatically. This can save a lot of time if you need to merge the same types of data repeatedly.

Let’s take a look.

We’ll use the Employees sheet (the second sheet in the workbook) again. Delete any values from the Full Name column before you start.

Click into cell C2, and type “Noella Bridgen.” This is the concatenation result that you’re looking for.

Next, click into cell C3, and start typing “Rosana Ellin.” As soon as you start typing, Excel will fill in the cell with its best guess at what you want, and will show you a preview of what the rest of the entire column would look like if you used that type of concatenation.

In our case, everything looks good, so we’ll just hit enter to accept it.

Now, the entire column has been filled with merged cells!

You can even change the format of your concatenation, and Excel will figure out what you did and apply it to the other cells.

Kasper Langmann, Co-founder of Spreadsheeto

For example, if you type “Bridgen, Noella” into cell C2, and then start typing “Ellin, Rosana,” Excel will fill in the rest of the cells with the same type of concatenation, giving you everyone’s name in “Last name, first name”-format.

Pretty cool, huh?

It gets even better.

Click over into cell E2, and type “Bridgen, Noella: Product Management.” Then click down into the next cell in the column, and start typing “Ellin, Rosana: Marketing.”

Excel already knows what you’re doing, and will help you autofill the entire column with this format.

The possibilities for this auto-merge type of concatenation are endless.

And once you get the hang of it, it can save you a lot of formula typing!

If you have a huge spreadsheet and you want to merge a lot of different cells, this is a very efficient way to do it.

Wrapping things up…

Once you’ve seen the things you can do with the concatenation operator and Excel’s automatic concatenation feature, it becomes clear that merging cells is not only easy, but very powerful. It just takes a bit of practice to get used to.

Try doing some merging in your own spreadsheets. See if you can figure out how to get Excel’s automatic concatenation to save you some time!

2019-10-08T12:28:20+00:00