Unmerge cells in excel. Everything about merging cells in Excel

You can merge cells in Excel in a variety of ways. This function comes in handy when you want to summarize data for several cells in one, or create a header for a table.

Using the context menu

Select the blocks that need to be combined and right-click on them. From the context menu, select Format Cells.

The following dialog box will appear, in which go to the Alignment tab. Check the box "Merging Cells". Here you can set the alignment of the text in the block, or select its orientation. Click OK.

If text was entered into the merged blocks, the program will display the following message: all values ​​will be deleted except the upper left one.

After the merger, the word “let’s unite” remains, instead of “let’s merge these cells”.

Use the button on the ribbon

Select the required blocks, go to the “Home” tab and click on the button "Merge and Place in Center".

The familiar message will appear, all values ​​will be deleted except the top left one - it will be placed in the center.

If you click on the arrow next to the button, an additional menu will appear. Here you can select one of the proposed actions, including canceling the merger.

Copying previously merged

If you already have combined blocks with the required text in your document, then select them, copy them using the combination “Ctrl+C”, and paste them into the desired area of ​​the document – ​​“Ctrl+V”.

Even if you select only one cell to paste the copied area, it will still be pasted, in my case, three blocks per row and two per column. In this case, the data that was written there will be deleted.

Using the CONCATENATE function

The fourth way is to combine cells in Excel, saving the data, using the “CONCATENATE” function. We will combine A1 - B1 and A2 - B2.

Let's add an additional column between them. Select B1, on the “Home” tab, click on the arrow next to the “Insert” button and select from the list "Insert columns into sheet".

Next, select B1, a new column has been inserted into it, and write the following formula to concatenate A1 - C1: =CONCATENATE(A1;" ";C1). In the quotation marks, enter the separator in the middle: “;” , “:” , “,”, I have a space there.

In the same way we combine A2 - C2. You can simply stretch the formula across the column by pulling the lower right corner of B1.

In order to leave only merged cells in the table, select them and press “Ctrl+C”. Right-click on them and select from the menu "Insert Special"– “Values”.

Thus, we copied only the values ​​of the selected cells; they are now not related to neighboring cells by the formula.

Let's delete column A and C. Select A1:A2, on the “Home” tab, click on the arrow next to the “Delete” button and select from the list "Remove columns from sheet". We also delete data in C1:C2.

As a result, we received merged cells without data loss.

Using Macros

The fifth method is to combine blocks in Excel without losing values, using a macro. You can read how to insert a macro into Excel by following the link.

Launch the VBA editor using the key combination “Alt+F11” and create a new module.

Now I paste the following code into the area for entering VBA code. The macro will be called "MergeCell". Save the created macro. If you have Excel 2007 or higher, when saving the document in the “File type” field, select "Excel workbook with macro support".

Close the VBA editor with the combination “Alt+Q”, after which the Excel document will open.

Now you need to run the created macro. Select the cells in the document that need to be merged. Go to the Developer tab and click on the “Macros” button. In the next window, select the macro with the desired name from the list - “MergeCell”, and click “Run”.

The selected blocks are merged, and the data is preserved. To ensure that text appears normally in a cell, go to the “Home” tab and click on the button "Wrap text".

When designing tables to display information more clearly, it becomes necessary to combine several cells into one. This is often used, for example, when specifying one common data header that has different meanings. You can see an example of such information display in the image below. Read on to learn how to merge cells in Excel step by step. You can merge not only horizontally, it is possible to merge vertically, as well as groups of horizontal and vertical cells.

An example of combining a digit with one header “Digit” and different data

How to merge cells in excel

There are two ways to merge cells. The first is using the context menu through the format. Select all the cells that you want to merge and right-click on the selected area. In the drop-down context menu, select “Format Cells...”.

In the format window, go to the “Alignment” tab and in the display block, check the “merge cells” box.

Check the “merge cells” option in the “Alignment” tab

If there is any data in the cells, Excel issues a warning every time that all data except the top left one is lost. Therefore, be careful when merging and do not lose important data. If you still need to merge cells with data, click “OK” to agree.

In my case, out of 10 numbers in the cells in the merge area, only the number “1” from the top left cell remained.

By default, Excel aligns data to the right after merging. If you need to quickly combine and at the same time immediately align the data to the center, you can use the second method.

Just like in the previous method, select the cells that need to be merged. At the top of the program, in the “HOME” tab, find a block called alignment. This block has a drop-down list that allows you to merge cells. There are three types for union:

1. Merge and Center - Clicking this option will result in exactly the same merge as in the previous example, but Excel will format the resulting data to be centered.
2. Merge by rows - if an area of ​​cells with several rows is selected, the program will merge row by row and, if there is data, will leave only those on the left.
3. Merge cells - this item works exactly the same as in the first option through the format.
4. Cancel merging - you need to select a cell that was previously merged and click on the menu item - the program will restore the cell structure as before merging. Naturally, it will not restore data before merging.

After trying any of the methods, you will know how to merge cells in Excel.

The second way to quickly merge cells

The structure of an Excel document is strictly defined and in order to avoid problems and errors in the future with calculations and formulas, each data block (cell) in the program must have a unique “address”. An address is an alphanumeric designation for the intersection of a column and a row. Accordingly, only one row cell should correspond to one column. Accordingly, it will not be possible to divide a previously created cell into two. To have a cell divided into two, you need to think about the structure of the table in advance. In the column where separation is needed, you need to plan two columns and merge all cells with data where there is no separation. In the table it looks like this.

It is not possible to split a cell into two in Excel. You can only plan the table structure when creating it.

In line 4 I have a cell divided into two. To do this, I planned in advance two columns “B” and “C” for the “Rank” column. Then, in the lines where I don’t need division, I merged the cells line by line, and in line 4 I left them without merging. As a result, the table contains a "Section" column with a cell split into two in the 4th row. With this method of creating a division, each cell has its own unique “Address” and can be accessed in formulas and when addressing.

Now you know how to split a cell in Excel into two and you can plan the table structure in advance so that you don’t break the already created table later.

Almost all users who start working with the Excel office program ask the question: “How to merge cells in Excel?” To use this function, the editor has several special commands that will help you do what you have planned in a matter of seconds. Using them is quite simple, you just need to remember a couple of simple tricks, which we will discuss below.

To merge cells in the editor, you can use its standard capabilities. In addition, they will automatically move to the center and independently join rows and other tables. In order for the table to look correct, you should combine everything in advance, when the cells are still empty, since after the procedure is completed, some of the entered text may go out of frame.

Let's look at several ways to combine cells. So, the first method works using the context menu. This method is considered the easiest and fastest than the others.

Instructions:
- First, you should select the desired range of cells that you are going to merge;
- On the selected area, right-click;
- In the list that appears, select the line labeled “Cell Format”;
- Afterwards the “Alignment” tab will appear on the screen;
- Put a checkmark next to the “Merging Cells” item.

Performing the above steps is as easy as shelling pears, but you can only use it for text information, and for numbers and various formulas everything will be a pointless exercise. Do not forget that only the data from the upper left part remains in the selected place, which the program makes known before starting the process.

You can come to the aid of data if you copy it in advance to another part of the editor and only then paste it into the table.

The second method uses the toolbar.
In programs like Excel 2003 and earlier versions, the merge icon is located right on the panel, by clicking on which you can quickly connect the desired areas and at the same time align them to the center. All this was done to quickly determine the title of the text in the line, which is formed in exactly this way.

If these actions moved all the information to the center of the editor, which was not necessary for the text, then you can easily return everything to its original position using the commands on the right and left.

Excel 2007,2010,2013 have the same button, which is located in the "Home" line. Unlike older versions, this function is also equipped with a drop-down menu, which increases the number of actions it performs.

Using the above commands, you can not only merge cells with center alignment, but also take advantage of equally important additional functions, such as: creating a whole group of cells combined by rows and merging without centering data.

In some cases, it is impossible to merge, and the buttons on the panel are inactive. This problem may occur due to installed security or the document may not be shared. Before carrying out the merging procedure, you should first get rid of all these conditions and open the possibility of formatting the table.

Third way.
To avoid losing necessary information during the merge procedure, you should use the "Concatenate" function. These actions do not require premature distribution of data to other cells and returning them back after merging.

Instructions:
- First, you should select a cell located close to the areas to be merged and format it correctly. For example, we use the size 2x6, 3x3;
- Next, in it we write a formula like CONCATENATE (A1;A2), after which we also indicate the cells with text that will be combined.

As a result, we will get the merged area.

Fourth way.
This method uses the "&" symbol, which is by far the easiest way to merge cells without losing any information. For example, the "+" symbol is used to summarize information in cells, and "&" is used to glue it together. This sign is located on the top row of the keyboard with the English layout, on the key with the number "7".

Fifth and final method.
The last method uses macros and add-ons. The process of merging cells in a table using the same method can be speeded up by using the keyboard shortcut CTRL+C and CTRL+V, which means copy and paste formatting. The same actions can be performed by selecting the macro writing path. To do this, you need to select the desired formula or use a macro recorder.

If you do not have the skills to create algorithms, then you should use those already built into Excel, thanks to which you can combine columns, rows and cells with a specified step. Some of them have the ability to connect text data with formulas without subsequent loss of information.

Excel is a very powerful tool for working with tables. That is why it is so in demand among people of various professions. However, not everyone knows how to merge cells in Excel. It's actually not difficult. There are a large number of ways. Let's look at them in more detail.

In order to connect several cells using the simplest method, you need to do the following.

1. Place any values ​​in several cells.

1. Now select this entire line.

1. Then click on the “Merge and Center” icon.

1. After that you will see the following.

You are warned that all information except that in the first cell will be lost.

1. You must agree to this condition. To do this, click on the “OK” button. The result of the actions described above will be a long line in which only the number 1 remains, since it was in the first selected fragment. In addition, the content is centered.

It is worth noting that all other information will be destroyed. Therefore, usually only empty cells are merged.

Extended capabilities

In order to demonstrate what can be done using the toolbar, our table will need to be significantly enlarged.

If you click not on the button, but on the “triangle” next to it, you can see additional menu items.

Select all the elements and click on the corresponding menu item.

You will be warned again about lost values. We agree with this.

As a result, you will get the following result.

Merge by row

Select the entire table and click on the second item.

After this you will see a standard warning. But note that the number of popups will correspond to the number of rows in your table.

As a result of this, you will end up with many rows consisting of several merged cells.

In our case, the duplicate data is due to the fact that all the rows were the same.

Merge cells

To demonstrate this feature, you need to take the following steps.

1. Select a few elements rather than the entire table.
2. Click on the triangle icon to display an additional menu.
3. Select the appropriate item.

1. We agree that as a result of the operation, only the contents of the upper left cell from the entire selected range will be saved.

1. As a result, we will get a large rectangle with just one number.

The steps described above can be repeated using the context menu.

1. Select several cells with text or any other content.
2. Right-click on active elements.
3. Then select “Format Cells”.

1. As a result, the following window will appear.

1. We are interested in two sections here:
   • alignment (here you can specify any suitable option);

• display (here be sure to check the box next to “Merge cells”).

1. Finally, click on the “OK” button.

1. We agree with the warning.

Thanks to this, we get the following result.

Using this mechanism, you can combine any number of elements both vertically and horizontally. The main thing is to select the desired range before calling the context menu.

Duplicating fragments

If you already have rows that consist of several cells, you can always easily copy them. In this case, all formatting will be completely the same. To do this, we will do a few simple steps.

1. Select the existing merged fragment.

1. Press the hot keys Ctrl + C (a dotted frame should appear).

1. Click on some other element.

1. Press the hot keys Ctrl + V.
2. As a result, we obtain an exact copy of the original string.

Thanks to this method, you can copy any number of “modified” cells.

Format by paragraph

You can copy the design in another way. This is made even easier.

1. Select the line whose appearance needs to be duplicated.
2. Click on the brush icon. Immediately after this, the cursor should change its appearance.

1. After that, click on the element that you want to transform.

1. As a result, all cells that are to the right of the selected one will be merged. The number of combined cells coincides with the original source.

Merge without data loss

In the methods described above, information was deleted in all other elements of the table except the first. In order to create a string from several fragments and at the same time preserve the original data, you can use the built-in functions of Microsoft Excel.

1. Move the cursor to the desired location. Click on the "fx" button.

1. Immediately after this you will see a window for inserting a function.

1. You need to change the category to “Text”.

1. Next, find the “Connect” function in the list. Select it and click on the “OK” button.

1. Immediately after this, a new window will appear in which you need to specify the function arguments. Links to elements can be entered manually.

1. But the easiest way is to just click on them one by one. First, click on the “Text 1” field.

1. Then to the first element of the desired line (the data will be inserted automatically).

1. Then go to the “Text2” field.

1. This time click on the next cell.

When finished, simply click on the "OK" button. As a result of this, you will get a string containing the values ​​of all the above fragments.

How to undo a merge

If you've concatenated more than you need, or the wrong line at all, it's very easy to put it all back together:

• To do this, just press the hot keys Ctrl + Z;
• or click on the undo icon of the last action on the quick access panel.

But there are other cases when it is necessary to split a string that was merged a long time ago. Or you have a large number of them and they were created at different stages of working with text in the table. In this case, undoing the last action will not help.

For such cases, there are two other methods. Let's look at them in more detail.

Toolbar

Return everything as it was possible in the same place where the unification takes place.

1. Select the desired fragment of the table. On the Home tab, click on the triangle icon. Select the last item.

1. Thanks to this, everything will return to its original state.

Context menu

You can also do the above using the context menu.

1. Also, first select the long line.
2. We do a right mouse click.
3. Select the item “Format Cells”.

1. In the window that appears, uncheck the box and click on the “OK” button.

1. Thanks to this, everything returns to how it was originally.

What to do if the button is not active

In some cases, it is not possible to edit tables. This is due to the protection of the sheet. It is very easy to remove it.

Go to the “Review” tab and click on the “Unprotect Sheet” button.

Conclusion

The methods described in this article for combining rows and columns in Excel are suitable for all modern versions of the application (starting from 2007). If something doesn't work out for you, you may not have read the instructions carefully. The reason may be that you are highlighting the wrong elements. Or don’t highlight them at all.

Video instruction

For those who still have any questions, a video is attached below in which you can hear additional comments.