How to Merge and Unmerge Cells in Excel: Tips and Tricks to Know

When you want to combine two cells into one in Excel, you have a few options. Merging cells is one of the best ways to do it. But it has a few drawbacks, too. Even so, you might find that it’s useful for working with text in Excel.

We’ll take a look at how to merge cells in Excel, how to unmerge them, and another method for combining two cells into one without losing the data in the cells.

How to Merge Cells in Excel

Before we get into how to merge cells, let’s talk about exactly what happens when you merge in Excel. In this example, we’ll look at two columns, one for first name and one for last name:

When we merge two of these cells, we end up with the contents of those cells in a single cell, like this:

The two cells have been combined, leaving a single cell that’s twice as wide and only contains one value. Excel only keeps the upper-left value of any merged cells. That’s one drawback to merging in Excel. We’ll talk about how to get around that in a moment.

To merge cells in Excel, select the cells you want to merge. You can select any number of cells; in our example, we’ll select just two. Once you’ve selected the cells, head to the Home tab and click Merge & Center in the Alignment section:

If you’ve selected more than one with text in it, you’ll get a warning:

If you want to continue, just hit OK. You’ll then have your merged cell as in the screenshot below. That’s all there is to it.

Merging Columns in Excel

In some cases, you’ll want to merge entire columns instead of just a few cells. Merging columns in Excel is easy; just select both columns and use the same steps.

First, highlight the two columns you want to merge:

If you hit the Merge & Center button right now, you’ll end up with one huge cell that has a single value in it. Instead, click the dropdown arrow next to Merge & Center and select Merge Across:

After clicking Merge Across, you’ll receive another warning from Excel. But instead of a single warning, you’ll get one warning for each row in your selection. Which is a lot. After clicking OK for each row, you’ll have a newly combined column:

You might be wondering why you wouldn’t just delete the second column in this case. Well, you probably would. Merging columns doesn’t do a whole lot for you.

Let’s talk about a better way to merge cells in Excel.

Combining Cells With CONCATENATE

The fact that combining cells makes you lose data is a big disadvantage of using Excel’s merging function. However, you can get around that by using a formula. Here’s the syntax of the CONCATENATE function, one of Excel’s most useful text functions:

=CONCATENATE(text 1, [text 2],...)

The function takes multiple text inputs and creates a new cell that combines all of them. This lets you merge cells without losing any data. Let’s use the same spreadsheet as before, with the list of first and last names. Instead of merging the cells and losing the last names, we’ll create a new column and use CONCATENATE to bring in both the first and last names.

Here’s the formula we’ll use in the new columns:

=CONCATENATE(B2, " ", C2)

Notice that between B2 and C2 we have a space between two quotes. This is important because it inserts a space between the contents of the cells being merged. If you forget it, you’ll get the first and last names mashed together.

Here’s what it looks like in Excel:

When I hit Enter, here’s what we get:

Much better than our first attempt, isn’t it?

You also have the advantage of being able to modify the combination later, whereas you cannot do that when you merge cells the regular way.

How to Unmerge Cells in Excel

If you decide to merge cells, you’ll also want to know how to unmerge them.

Unfortunately, unmerging doesn’t bring any of your lost data back. When you merge cells in Excel, you’re going to lose some information, and it’s gone forever. Merged cells can create weird spaces in your spreadsheet, though, and unmerging them solves the problem.

To unmerge cells, just select the merged cell, click the dropdown arrow next to Merge & Center, and click on Unmerge Cells:

The cells will go back to their original count and size. You’ll still only have the data from the upper-left corner of your selection, though. This is one reason why it’s a good idea to work on a copy of your spreadsheet (or use the CONCATENATE function).

Merging Cells Can Still Be Useful

While you’ll need to use CONCATENATE to merge cells without losing data, there are still some uses of merging in Excel. It’s often used for formatting to clean up a table, for example, which may help you get a better print of your spreadsheet:

In general, though, it’s better to use CONCATENATE if you’re looking to merge cells without losing data.

That being said, you may find that you come across a situation where you need to merge cells in Excel. Even an Excel beginner will need to use them every day. So, when you find one of those situations now, you’ll know exactly how to do it.

And don’t forget that you can merge Excel files and sheets too!

from MakeUseOf https://ift.tt/2r5aXDW
via IFTTT