IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд

Excel merge cells

Merge and unmerge cells

In this course:

You can’t split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it.

Merge cells

Select the cells to merge.

Select Merge & Center.

Important: When you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

Unmerge cells

Select the Merge & Center down arrow.

Select Unmerge Cells.

You cannot split an unmerged cell. If you are looking for information about how to split the contents of an unmerged cell across multiple cells, see Distribute the contents of a cell into adjacent columns.

After merging cells, you can split a merged cell into separate cells again. If you don’t remember where you have merged cells, you can use the Find command to quickly locate merged cells.

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns.

In the example here, cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information in rows 2 through 7.

Merge cells

Merge two or more cells by following these steps:

Select two or more adjacent cells you want to merge.

Important: Ensure that the data you want to retain is in the upper-left cell, and keep in mind that all data in the other merged cells will be deleted. To retain any data from those other cells, simply copy it to another place in the worksheet—before you merge.

On the Home tab, select Merge & Center.

If Merge & Center is disabled, ensure that you’re not editing a cell—and the cells you want to merge aren’t formatted as an Excel table. Cells formatted as a table typically display alternating shaded rows, and perhaps filter arrows on the column headings.

To merge cells without centering, click the arrow next to Merge and Center, and then click Merge Across or Merge Cells.

Unmerge cells

If you need to reverse a cell merge, click onto the merged cell and then choose Unmerge Cells item in the Merge & Center menu (see the figure above).

Split text from one cell into multiple cells

You can take the text in one or more cells, and distribute it to multiple cells. This is the opposite of concatenation, in which you combine text from two or more cells into one cell.

For example, you can split a column containing full names into separate First Name and Last Name columns:

Follow the steps below to split text into multiple columns:

Select the cell or column that contains the text you want to split.

Note: Select as many rows as you want, but no more than one column. Also, ensure that are sufficient empty columns to the right—so that none of your data is deleted. Simply add empty columns, if necessary.

Click Data > Text to Columns, which displays the Convert Text to Columns Wizard.

Click Delimited > Next.

Check the Space box, and clear the rest of the boxes. Or, check both the Comma and Space boxes if that is how your text is split (such as «Reyes, Javiers», with a comma and space between the names). A preview of the data appears in the panel at the bottom of the popup window.

Click Next and then choose the format for your new columns. If you don’t want the default format, choose a format such as Text, then click the second column of data in the Data preview window, and click the same format again. Repeat this for all of the columns in the preview window.

Click the button to the right of the Destination box to collapse the popup window.

Anywhere in your workbook, select the cells that you want to contain the split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.

Click the button to expand the popup window again, and then click the Finish button.

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns. For example, here cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information in rows 2 through 7.

Merge cells

Click the first cell and press Shift while you click the last cell in the range you want to merge.

Important: Make sure only one of the cells in the range has data.

Click Home > Merge & Center.

If Merge & Center is dimmed, make sure you’re not editing a cell or the cells you want to merge aren’t inside a table.

Tip: To merge cells without centering the data, click the merged cell and then click the left, center or right alignment options next to Merge & Center.

If you change your mind, you can always undo the merge by clicking the merged cell and clicking Merge & Center .

Unmerge cells

To unmerge cells immediately after merging them, press Ctrl + Z. Otherwise do this:

Click the merged cell and click Home > Merge & Center.

The data in the merged cell moves to the left cell when the cells split.

Merging combines two or more cells to create a single, larger cell. This is a great way to create a label that spans several columns. To merge cells, follow these steps:

Drag the selection handle to select the cells that you want to merge. For example, drag the handle from cell A1 to cell C1.

On your iPad, tap Merge .

On your iPhone, tap the Edit icon , tap Home if it isn’t already selected, and then tap Merge & Center.

Important: Only the data in the upper-left cell from a range of selected cells remains in the merged cell. Excel deletes data in the other cells of the selected range.

If Merge or Merge & Center is not available on the Home tab, make sure that the cells you want to merge aren’t inside a table.

If you change your mind, you can always unmerge the cells.

Select a merged cell.

On the Home tab, tap Merge or Merge & Center again.

If Merge or Merge & Center is not available on the Home tab, then the selected cell might not be a merged cell.

Читать еще:  Dax формулы excel

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Merge Cells in Excel

Merge Cells in Excel (Table of Contents)

Merge Cells in Excel

Merge cells in excel is used for merging more than one cells. This is quite useful when we need to assign one value of the cell to 2 or more cells. To merge the cells in excel, select those cells and from home menu tab select Merge & Center from the alignment section. This will combine both of those selected cells but it will keep only first value to be seen and others will be eliminated.

Excel functions, formula, charts, formatting creating excel dashboard & others

Merge Cells in Excel Using Merge & Center Option

Merging essentially means having the combined values of multiple cells in one. Perhaps the most used and one of the simplest ways to achieve this using the “Merge & Center” option available in the Home ribbon.

There is however a drawback with this method of merging. If we have some data in cells A1 and B1, using this form of merging would result in the retention of the left-hand value and the overwriting of the right-hand value.

Let us look at an example to get a better understanding of this drawback.

We have a list where the first column (A) contains the first names and the second column (B) has the last names. If we wish to merge the contents of “First Name” and “Last Name”, then this method fails as it takes only the left-hand value, completely overwriting the right-hand values.

Step 1: We will need to select the cells that we wish to merge together. In this scenario, we shall select cells A2 and B2.

Step 2: We will click on the “Merge & Center” option on the Home ribbon next.

Step 3: The moment we click on “Merge & Center”, we get a dialogue box pop up showing a warning, mentioning that only the left-hand value would be considered, and the right-hand value would be discarded.

Step 4: Click on “OK”

Step 5: Once we click on “OK”, we shall get the following result

So, what happened here is that Excel merged the two cells together instead of the cell values. Due to this, we did get a merged cell, but we lost the “Last Name” in the process. This is the main drawback of using the “Merge & Center” option.

Now we shall discuss the two best ways to merge cells in Excel.

Best ways to Merge cells in Excel

So you might have the question – What is the correct and most effective way to merge cells in Excel? Here’s the answer to that.

  • Concatenation formula
  • Ampersand (&) Operator

Let us see what each of these options are in detail.

Merge cells Using CONCATENATE

As we have seen by now that Excel has a vast array of formulas for various needs. The CONCATENATE formula can merge the contents of multiple cells into one cell. By using this method, we can successfully merge the “First Name” and the “Last Name” without losing any data in the process. However, the result would be available in a new cell.

Step 1: First, we shall select Cell C2.

Step 2: Next, we shall be applying the CONCATENATE formula as shown below.

On applying this formula, we shall now get the correct results:

Similarly, We will Get Other Result.

Now if we look at the breakup of the formula:

A2 = The first cell that we wish to merge.

“ “ = The space between the First Name and the Last Name.

B2 = The last cell that we wish to merge.

Using this method, we can merge multiple cells into one, but it will be in a different cell.

Merge Cells Using Ampersand (&) Operator

Similar to the way in which we merged two cells using the CONCATENATE function, we shall use the ampersand (&) operator. The only difference will be – instead of the CONCATENATE function, we shall use the “&” operator.

Step 1: First, we shall select the cell C2:

Step 2: Next we shall apply the formula as shown below.

This would give us the following result:

Similarly, We will get another Result.

Merge Option

Now let us discuss the different options in the “Merge” option that we had mentioned briefly earlier.

  • Merge & Center: This option will merge the selected cells by keeping only the left-hand value and making the text alignment as center.

  • Merger Across: “Merge Across” would merge the selected cells but would not change the alignment.

  • Merge Cells: This is perhaps the simplest option – It would just merge the selected cells.

  • Unmerge Cells: “Unmerge Cells” is the exact opposite of Merge. It unmerges the cells.

So now that we have covered how to merge cells in Excel, perhaps it is interesting to know that we can also “Unmerge” cells!

How to Unmerge cells in Excel?

Suppose we have the same set of merged cells that we had seen earlier with the “Merge & Center” option.

Now to unmerge these cells, we will need to once again to the Home ribbon and navigate to the “Merge & Center” option and click on the drop-down arrow at the side, to open up the underlying options.

Now, here we shall select the “Unmerge Cells” option to unmerge the selected cells.

Hence the merged cells have been “unmerged” by using this option.

Trick to Quickly Merge Multiple Cells in Excel

Suppose we have to merge multiple cells in Excel. Our objective here is to merge all the cells for a “Year” for all entries for a particular year.

Step 1: We shall proceed with selecting the cells A2 to A4.

Step 2: Next, we shall go to the Home ribbon and click on “Merge & Center”.

As we see in the below screenshot the cells A2, A3 and A4 have been successfully merged.

Step 3: Now we shall simply click on this merged cell and then click on “Format Painter” in the Home Ribbon.

This causes the selected cells to be highlighted as shown below.

Step 4: Next we shall select and drag the cells A5 to A10.

Читать еще:  Как очистить формат в excel

As we can see, the merging has been carried out for the rest of the years by simply clicking on “Format Painter”. Format Painter essentially copies the same format from the source cells(s) and applies it across all the destination cells.

Things to Remember About Merge Cells in Excel

  • Sorting data over merged cells in Excel is not possible.
  • Navigating through merged cells can become difficult and cumbersome.
  • It is advisable to use the merge cells option only for headings.
  • Merge option is limited to merging cells and not the cell values.
  • In case we have to merge the cell values then we will need to use either the CONCATENATE function or the Ampersand (&) Operator.
  • There is a shortcut to merge cells in Excel: Alt + H + M + U.This combination of key presses merges as well as unmerges the cells that are selected in the excel sheet.

Recommended Articles

This has been a guide to Merging Cells in Excel. Here we discuss how to Merge Cells in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

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.

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.

Table of Content

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.

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

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.

“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.

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.

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.

Читать еще:  Net работа с excel

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.

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!

Merge Cells in Excel

Merging cells in excel means combining two or more cells together, excel has provided us with an inbuilt button which is used to merge cells in excel, this button is available in alignment section of the home tab, to merge two or more than two cells first thing to do is the selection of cells which are to be merged and then when we click on this button the cells are merged.

Table of Contents

What is Merge Cells in Excel?

Let’s understand how to merge cells in excel with examples.

In excel, quite often we may want the values of multiple cells to be merged into one or we may need to present the data differently.

There are the couple of ways we can merge the cells in excel. In this article, I will discuss the ways of merging cells in excel, its shortcuts and which one is better and which one to avoid.

How to Merge Cells in Excel?

As I have earlier stated merging means, combining multiple cells into one. One of the most common ways of merging the cells in excel is using the Merge & Centre option in Home Ribbon.

There is an issue with this kind of merging cells. If there is a value in the cells A1 & B1. If I merge using this method, it will only retain the left-hand side value and overwrite the right-hand side value.

Look at the below data first. I have the first name and last name values from A1 to B2. If I want to merge the first name and last name together I cannot use this method because it overtakes the right-hand side value and retains only the left-hand side value.

Step 1: Data Structure

Step 2: Select the two cells you want to merge. In this case, I am selecting A2 & B2 together.

Step 3: Click on the Merge & Centre option in the Home Tab.

Step 4: Once you click on that option it will open up the below dialogue box and click ok.

Now the result will be as per the below image.

Now excel merged these two cells together rather merging the values of these two cells. The problem is we lost our last name due to merging cells using Merge & Centre option.

Top 2 Right Ways to Merge Cells in Excel

Then what is the right way of merging two values of the two cells together? We have a couple of options for this also.

Let me explain each one by one.

#1 – Using Concatenate Formula to Merge Cells in Excel

You know Excel has many functions. CONCATENATE can combine multiple cell values into one. Using this method we can merge first name and last name but in a different cell.

Step 1: Select the cell C2.

Step 2: Apply the CONCATENATE formula as shown in the below image.

The formula is for

  • Part 1: What is the first value you need to merge?
  • Part 2: This is just the space between two values.
  • Part 3: This is the second value I want to merge.

So concatenate two cells values into one.

#2 – Using Ampersand Operator to Merge Cells in Excel

Like how we have combined two values using CONCATENATE similar we can combine using ampersand operator.

Instead Of CONCATENATE, we just need to insert & symbols.

Ссылка на основную публикацию