How to merge cells in Excel. Split cells, or rather, undo the merging of cells into Excel, filling all the resulting cells with data from the merged

Quite often, when working with tables in Microsoft Excel, a situation occurs when you need to combine several cells. The task is not too difficult if these cells do not contain information. But, what if the data has already been entered into them? Are they really going to be destroyed? Let's see how to merge cells, including without data loss, in Microsoft Excel.

Although, we will show the merging of cells using the example of Excel 2010, but this method is suitable for other versions of this application.

In order to combine several cells, of which only one is filled with data, or completely empty, select the required cells with the cursor. Then, in the Excel Home tab, click on the icon on the "Merge and Place in Center" ribbon.

In this case, the cells will be merged, and all data that will fit into the merged cell will be placed in the center.

If you want the data to be placed according to the formatting of the cell, then you need to select the "Merge cells" item from the drop-down list.

In this case, the default recording will start from the right edge of the merged cell.

Also, it is possible to combine several cells line by line. To do this, select the required range, and from the drop-down list, click on the "Combine by Rows" value.

As you can see, after this, the cells were not united into one common cell, but adopted a row-by-row union.

Combining via the context menu

It is possible to merge cells through the context menu. To do this, select the cells to be merged with the cursor, right-click on them, and in the context menu that appears, select the "Format cells" item.

In the cell format window that opens, go to the "Alignment" tab. Check the box "Merge cells". Here you can also set other parameters: direction and orientation of the text, horizontal and vertical alignment, auto-fit, word wrap. When all the settings are done, click on the "OK" button.

As you can see, the cells are merged.

Lossless consolidation

But, what if there is data in several of the cells to be merged, because when merging, all values ​​except the top left will be lost?

There is also a way out in this situation. We will use the CONCATENATE function. First of all, you need to add another cell between the cells that are going to be connected. To do this, right-click on the far right of the merged cells. In the context menu that appears, select the "Insert ..." item.

A window opens in which you need to move the switch to the "Add column" position. We do this and click on the "OK" button.

In the cell formed between the cells that we are going to merge, put the value without quotes "= CONCATENATE (X; Y)", where X and Y are the coordinates of the cells to be joined, after adding a column. For example, to combine cells A2 and C2 in this way, insert the expression "= CONCATENATE (A2; C2)" in cell B2.

As you can see, after that, the symbols in the common cell "stuck together".

But, now instead of one merged cell, we have three: two cells with the original data, and one merged. To make one cell, right-click on the merged cell, and select the "Copy" item in the context menu.

Then, we move to the right cell with the initial data, and by clicking on it, select the "Values" item in the insert parameters.

As you can see, this cell contains the data that was previously in the cell with the formula.

Now, we delete the leftmost column containing the cell with the primary data and the column containing the cell with the concatenation formula.

Thus, we get a new cell containing the data that should have been merged, and all intermediate cells are deleted.

As you can see, if the usual merging of cells in Microsoft Excel is quite simple, then merging cells without loss will have to tinker with. However, this is also a doable task for this program.

Microsoft Excel is used for working with spreadsheets in many offices. The application has a wide range of functionality, but not the most user-friendly interface. It is difficult for new users of the application to understand how to perform even basic actions in it, for example, such as merging cells. Often, when working with tables, users have to face the need to merge two or more cells horizontally or vertically. Let's consider how to do this in Microsoft Excel as conveniently as possible.

Please note: Depending on the version of the programMicrosoftExcel, the instructions below may vary slightly.

How to Concatenate Cells Easily in Microsoft Excel

First, let's look at the basic program tools for merging cells. The program provides a separate tool for converting several cells into one, while it boasts wide functionality.

To simply combine multiple cells into one horizontally or vertically, you need to select the cells, and then click in the top menu in the "Home" section on the item "Merge and center". In this case, all the selected cells will be combined into one; note that the information in it will be saved only from the upper left cell of the selected array. In this case, this information after merging will be placed in the center of the formed line.

To merge cells, but the information from the cells does not move to the center, you need to click on the down arrow of the tool to merge cells, and select the option "Merge cells" from the presented tools. After that, the default record (the top left of the array, or the only one that exists) will be in the merged cell, according to the existing cell formatting.

Another possibility that standard tools provide is to combine an array of selected cells into MicrosoftExcel row by row... To merge the selected cells line by line, you must click on the “Merge by Rows” button in the list of additional features of the cell merge tool on the instrument panel.

How to merge cells in Microsoft Excel via context menu

Also, Word has a separate context menu that contains the options for merging cells. If there is a specific task of combining cells that cannot be solved by the above tools, it is recommended to use this menu.

Before calling the context menu for merging cells, you need to select the data array to be worked on. Next, right-click on any place in the selected array, and select the item "Cell format".

A menu will open in which you need to switch from above to the item "Alignment"... Here, to merge cells, check the box "Merging cells", after which you can make other text formatting settings - set the vertical and horizontal alignment options, enable the word wrap function, and so on. When all the necessary settings are made, click "OK" to perform the specified actions.

How to merge cells in Microsoft Word without data loss

As noted above, when combining several cells that contain data, only information from the upper left cell is saved. Let's see how to merge cells without losing data:

As you can see, there is a cell that contains the combined information. If necessary, you can now merge several cells so that this data is present in them.

Combining two or more cells in Excel will help make your document more attractive and readable. This is a simple formatting and anyone can learn it.

There are two ways to connect cells: gluing cells into one or combining information inscribed in these cells, but this will cause the effect of data loss. Let's try to figure out how to merge cells in Excel.

How to combine two cells

Follow these steps:

• Select the cells, right-click on them;
• Select the item "Format cells ...", a new window will appear;
• Go to the "Alignment" menu, then - "Display";
• Place the checkbox next to the "Merge Cells" line and press "Enter".

Now the two cells will merge into one. However, there is one point here. If you merged cells horizontally, then only the information that was in the left will remain; if combined vertically, then the information that was in the top cell will remain. Everything that was written in the second cell will simply be deleted.

Connecting data from different cells

But in Excel 2010 and other versions of the program, you can combine cells in such a way that everything you write is saved. Let's say you have three columns: the first contains the first name, the second - the surname, and the third - the patronymic. It is required to combine them in one cell (for example, do it by Ivan Petrov Ivanovich). If the full name is not one, but many, then it will take too long to rewrite everything anew. There are two options for merging cells that make it much faster.

Option one:

• Make the fourth column, calling it full name;
• On the next line, enter "= A2 & B2 & C2" (that is, those cells in which there are the first last name, first name and patronymic) and press "Enter";
• The result will be the following - "Ivan Petrov Ivanovich". That is, the full name will be written without spaces;
• If you need to set spaces, insert the “&” “&”, that is, the formula will be “A2 &” “& B2 &” “& C2”. The result will be "Ivan Petrov Ivanovich";
• You can use any character to place the separator, such as a comma. You will get such a formula “A2 &” “& C2 &”, “& B2”, as a result the full name will look like this: “Ivan Ivanovich, Petrov”;
• To use the formula for other cells, copy the first formula, select the lines below it (opposite which are separated by the last name, first name and patronymic) and press "Enter".

Option two:

• The formula “= CONCATENATE (A2; B2; C2)” is applied in the same way;
• If you need to set a space, then "= CONCATENATE (A2," ", B2)";
• If a comma is needed - "= CONCATENATE (A2,", ", B2)".

How to split merged cells

1. Click on the cell with the left mouse button. On the Home tab, in the Align section, the Merge and Center button will light up;
2. To remove a connection, just click this button.

However, when dividing cells, the data in them will not be disconnected. If they were combined, the information will be located in the left cell. In the second cell, the effect of data loss will occur, that is, it will be empty.

How to find merged cells

1. Go to the "Home" tab, to the "Editing" section;
2. Select "Find and select", then "Find";
3. Select "Options", click the line "Format";
4. Open the "Alignment" menu, in the "Display" section, check the box "Merge cells";
5. Click "OK";
6. Then use one of two options:

• To find the next merged cell, click Find Next. The required cell will be highlighted in Excel;
• To find all such cells at once, click Find All. All merged cells appear. You can click on any cell in the list - then it will be highlighted in Excel, and you will see where it is located.

Important findings

1. All data that is in the cells is lost (except for the upper left in the selected cells, in which there is no loss), this applies to any direction of selection;
2. It is quite possible to merge cells back, which will not lead to data loss. The information contained in them will be located in the upper left cell (it will be located in solid text).

Merging cells in Excel 2010 and other versions of the program helps to speed up the work with the document and create a clearer picture. Using this function, you can easily learn how to properly structure tables.

Users who are just starting to work with the Excel spreadsheet editor often wonder how to combine cells in excel.

For this, there are special commands and functions in the program itself, as well as several methods such as add-ins and macros.

All of them are simple enough to learn how to use in just a couple of tricks using the following tips.

Using standard Excel functions, you can concatenate table cells.

At the same time, they are aligned in the center, joined by lines, or simply aligned, leaving the previous formatting.

Advice! It is best to carry out the merging in advance, with still empty cells, since after the procedure the information remains only in the leftmost cell at the top.

Combining via the context menu

The easiest and fastest way to merge cells and write data into one enlarged column and row is to use the context menu.

The procedure is as follows:

1. The range of cells that need to be merged is highlighted;
2. The right mouse button is pressed on the selected part of the table;
3. In the menu that opens, select the item "Format cells";
4. The "Alignment" tab opens;
5. A check mark is put in front of the "Merge cells".

The method is simple, but suitable, of course, only for text data - it is unacceptable and meaningless to combine cells with numbers and formulas.

It should be noted that in this case, only information from its upper left part remains in the combined area, which the program warns about in advance.

You can save the data by copying it to another area in advance, and after combining it, attach it to the remaining text.

Union via toolbar

For programs of the type and older versions, the merge icon is displayed directly on the panel. By clicking it, you can not only quickly connect the areas, but also immediately align them in the middle.

This is done in order to speed up the process of forming text headings in the line, which are often created in this way.

If the execution of the command has resulted in a central position of the data that is not required for the text, it is easy to set it in the desired position (left or right) using the alignment commands.

The Excel 2007/2010/2013 panel also has such a button, on the Home tab (Alignment group).

However, here it is already equipped with a drop-down menu to increase the number of actions performed with its help.

Using the above commands, you can not only merge table elements with center alignment, but also perform two additional options:

• Create a whole group of cells concatenated by rows;
• Merge without centering text.

It is not possible to combine columns even in this version.

Sometimes the area cannot be merged and the buttons and commands remain inactive. This happens if the sheet is protected or only general access is allowed to the book (document).

Before joining areas, you must remove all these conditions, opening the opportunity to format the table.

Function for combining text

In order for the merge to occur without losing important information and the data did not have to be distributed to other cells and returned back after the merge, it is worth using the "Concatenate" function.

This is easy to do:

1. First, a cell is selected near the areas to be merged and formatted as needed (for example, it is made 3x3 or 2x6 in size);
2. It contains a formula of the CONCATENATE type (A1; A2), which indicates the cells (one at a time!), The text of which will be combined).

The result will be a concatenated field of type:

Information in area D1: F8 from cells A1 and C3, connected without loss of text

When working with tables, sometimes you have to change their structure. One of the options for this procedure is string concatenation. In this case, the combined objects turn into one line. In addition, there is the possibility of grouping nearby inline elements. Let's find out in what ways you can carry out these types of merging in Microsoft Excel.

As mentioned above, there are two main types of string concatenation - when several lines are converted into one and when they are grouped. In the first case, if the inline elements were filled with data, then they are all lost, except for those that were located in the topmost element. In the second case, physically the lines remain the same, they are simply combined into groups, the objects in which can be hidden by clicking on the icon in the form of a symbol "minus"... There is another option for connecting without losing data using a formula, which we will discuss separately. Namely, based on the indicated types of transformations, various ways of combining lines are formed. Let's dwell on them in more detail.

Method 1: concatenation through the format window

First of all, let's look at the possibility of combining lines on a sheet through the formatting window. But before proceeding with the direct merging procedure, you need to select the nearby lines that are planned to be merged.

There are also alternatives to navigate to the formatting window. For example, after selecting lines, while in the tab "Home", you can click on the icon "Format" located on the tape in the toolbox "Cells"... From the list of actions that opens, select the item "Cell format ...".

Also, in the same tab "Home" you can click on the oblique arrow located on the ribbon in the lower right corner of the toolbox "Alignment"... Moreover, in this case, the transition will be made directly to the tab "Alignment" formatting windows, that is, the user does not have to make an additional transition between tabs.

You can also go to the formatting window by pressing the hotkey combination Ctrl + 1, after highlighting the required elements. But in this case, the transition will be made to that tab of the window. "Cell format" last visited.

With any option for switching to the formatting window, all further actions to combine the lines must be carried out according to the algorithm that was described above.

Method 2: using tools on the ribbon

You can also concatenate strings using the button on the ribbon.

But not all cases require the text to be centered. What if you want to place it in a standard form?

Method 3: concatenating rows inside a table

But it is far from always necessary to combine the lines to the end of the sheet. More often than not, a join is made within a specific table array. Let's take a look at how to do this.

You can also perform joins within a table using the tools on the ribbon.

Method 4: combine information in strings without losing data

All of the above methods of merging imply that after the completion of the procedure, all data in the merged elements will be destroyed, except for those located in the upper left cell of the area. But sometimes it is required to combine certain values ​​located in different rows of the table without loss. This can be done using the function specially designed for such purposes COUPLING.

Function COUPLING belongs to the category of text operators. Its task is to combine several text lines into one element. The syntax for this function is as follows:

CONCATENATE (text1; text2; ...)

Group arguments "Text" can be either separate text or links to sheet elements in which it is located. It is the last property that we will use to accomplish the task. In total, up to 255 such arguments can be used.

So, we have a table that contains a list of computer equipment with its price. We are faced with the task of combining all the data located in the column "Device", in one line without loss.

There is also an alternative option to carry out the specified procedure for combining data from several lines into one without loss. In this case, you will not even need to use the function, but you can get by with the usual formula.

Method 5: grouping

In addition, you can group strings without losing their structural integrity. Let's see how to do it.

As you can see, the way to combine lines into one depends on what kind of union the user needs, and what he wants to get in the end. You can combine rows to the end of the sheet, within a table, perform the procedure without losing data using a function or formula, and group the rows. In addition, there are separate options for performing these tasks, but their choice is already influenced only by the user's preferences from the point of view of convenience.