When you want to merge two cells into one in Excel, you have a few options. Merging cells is one of the best ways to do it. But it also has some drawbacks. Still, you might find it useful for working with text in Excel.
We will see how to merge cells in Excel, how to unzip them, and another method to merge two cells into one without losing the data in the cells.
Before we get into how to merge cells, let's talk about exactly what happens when you merge in Excel. In this example, we will see two columns, one for the first name and one for the 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 merged, leaving a single cell that is twice as wide and only contains one value. Excel only keeps the top left value of any merged cell . That's a drawback of merging in Excel. We'll talk about how to fix 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 will select only two. Once you have selected the cells, go to the House tab and click Merge and Center in the Alignment section:
If you have selected more than one with text, a warning will appear:
If you want to continue, just press OK . Then you will have your merged cell as below screenshot. That's all there is to it.
In some cases, you'll want to merge entire columns instead of just a few cells. Column merging 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 and Center At this point, you'll end up with a huge cell that has a single value. Instead, click the dropdown arrow next to Blend &Center and select Blend Through :
After clicking Merge Through , You will receive another warning from Excel. But instead of a single warning, you'll get a warning for each row in your selection. Which is a lot After clicking OK For each row, you will have a new combined column:
You may be wondering why you wouldn't remove the second column in this case. Well, you probably would. Merging columns doesn't do much for you.
Let's talk about a better way to merge cells in Excel.
The fact that merging cells causes you to lose data is a big disadvantage of using Excel's merge feature. However, you can get around that by using a formula. Here is the syntax for the CONCATENATE function, one of Excel's most useful text functions:
= CONCATENAR (texto 1, [texto 2],…)The function takes multiple text inputs and creates a new cell that combines all of them. This allows you to 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 enter both the first and last name.
Here is the formula we will use in the new columns:
= CONCATENADO (B2, "", C2)Note 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, you will get the first and last surname mixed up.
This is what it looks like in Excel:
When I hit Enter , This is what we get:
Much better than our first attempt, isn't it?
It also has the advantage of being able to modify the merge later, whereas you can't do that when you merge cells in the usual way.
If you decide to merge cells, you'll also want to know how to unmerge or split them.
Unfortunately, not merging doesn't return any of your lost data. When you merge cells in Excel, you will lose some information and it will be gone forever. However, merged cells can create weird gaps in your spreadsheet, and merging them solves the problem.
To split cells in Excel, simply select the merged cell, click the dropdown arrow next to Merge and Center, and click Unmerge Cells :
The cells will return to their original count and size. However, you will still only have the data for the top left corner of your selection. This is one reason why it's a good idea to work on a copy of your spreadsheet (or use the CONCATENATE function).
While you'll need to use CONCATENATE to combine 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 can help you get a better impression of your spreadsheet:
In general, though, it's best to use CONCATENATE if you're looking to merge cells without losing data.
That said, you may come across a situation where you need to merge cells in Excel. Even an Excel beginner will have to use them every day. So when you encounter one of those situations now, you'll know exactly how to do it.
And don't forget that you can also combine Excel sheets and files. How to combine Excel sheets and files. How to combine Excel sheets and files. Do you need to combine multiple data sets in a single spreadsheet? It is a tedious task if done manually. We'll show you smart ways to combine Excel files. Read more !