How to merge cells in Excel without losing data. Merging Cells in Microsoft Excel

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

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

How to merge two cells

Follow these steps:

• Select the cells, right-click on them;
• Select “Format Cells...”, a new window will appear;
• Go to the “Alignment” menu, then “Display”;
• Check the box next to the line “Merging Cells” and press “Enter”.

Now the two cells will merge into one. True, there is one point here. If you merged cells horizontally, then only the information that was in the left will remain; if they were 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.

Joining data from different cells

But in Excel 2010 and other versions of the program, you can combine cells in such a way that everything written is saved. Let's say you have three columns: the first contains the first name, the second contains the last name, and the third contains the patronymic. You need to combine them in one cell (for example, make Ivan Petrov Ivanovich). If there is not one name, but many, then rewriting everything again will take too long. There are two options for merging cells that make this much faster.

Option one:

• Make a fourth column, calling it your full name;
• On the next line, enter “=A2&B2&C2” (that is, those cells containing the first last name, first name and patronymic) and press “Enter”;
• You get the following: “IvanPetrovIvanovich”. That is, the full name will be written without spaces;
• If you need to set spaces, insert the “&” “&” sign, that is, the formula will be “A2&” “&B2&” “&C2”. The result will be “Ivan Petrov Ivanovich”;
• To add a separator, you can use any character, such as a comma. The result will be the following 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 the separated last name, first name and middle name) and press “Enter”.

Option two:

• The formula “=CONCATENATE(A2;B2;C2)” is applied similarly;
• If you need to set a space, then “=CONCATENATE(A2,” “,B2)”;
• If you need a comma, use “=CONCATENATE(A2,” “,B2).”

How to split merged cells

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

However, when dividing cells, the data in them will not be separated. If they were combined, the information will be located in the left cell. The second cell will experience data loss, 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 “Format” line;
4. Open the “Alignment” menu, in the “Display” section, check the “Merge cells” line;
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 will 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 Takeaways

1. All data that is in the cells is lost (with the exception of the top left in the selected cells, in which there is no loss), this applies to any direction of selection;
2. The merging of cells can be reversed, which will not lead to data loss. The information they contained will be located in the upper left cell (it will be in solid text).

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

Working in the editor Excel tables is a necessary skill for every modern office worker. Microsoft Excel has very broad capabilities, and, having the proper set of knowledge about working in this program, you can more effectively solve certain problems. In this article, we will look at how to merge cells, rows or columns in Excel. Unfortunately, this is a problem for most users. But don't be alarmed. This article will answer all your questions. Let's get started. Go!

Users can give the table the desired look at any time

In general, data grouping is a rather difficult topic if you do not fully understand it. Merging table blocks is done using the button<-a->, which is located in the “Alignment” block in the tool ribbon. Please note that when merging, only the left and first values ​​will remain from the data, the rest will be deleted. Also, if some rows are combined, it will not be possible to filter by them, and the autofilter will not work.

Now select the areas that you want to combine into one, you can do this by holding down Ctrl key. Next, click on the button<-a->in the toolbar. By clicking on the arrow in this button, you will open a list of merger options:

• Place in the center;
• By lines;
• Cells;
• Cancel.

After you select the right option, the areas will be merged according to the specified parameters.

You can do without the main toolbar. After selecting the desired area, click on it right click mouse and select Format Cells. Next, go to the “Alignment” tab and check the “Merge Cells” checkbox.

Unfortunately, you cannot merge rows and columns without losing data using standard Excel. Therefore, there are settings for this table editor that allow you to expand functionality programs. After downloading and installing the add-in, you will have a wide range of options for combining rows and columns.

There is a more advanced way to group multiple table values ​​into one area. You can use formulas for this. If you have blocks with text or numbers, and you want to group it all, then write the following in the formula field without quotes: “=CONCATENATE (row and column number of each cell separated by a semicolon).” For example: “=CONCATENATE (B2;C2;D2;E2).” In this case, the values ​​will be grouped in a row. If between them you want

Quite often when working with tables in Microsoft program In Excel, a situation arises when you need to merge several cells. The task is not too difficult if these cells do not contain information. But what to do if data has already been entered into them? Will they really be destroyed? Let's figure out how to merge cells, including without losing data, in Microsoft Excel.

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

In order to combine several cells, only one of which is filled with data, or completely empty, select the required cells with the cursor. Then, in the Excel “Home” tab, click on the “Merge and Place in Center” icon on the 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 cell formatting, then you need to select “Merge Cells” from the drop-down list.

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

It is also possible to combine several cells row by row. To do this, select the desired range, and from the drop-down list click on the “Merge by Rows” value.

As you can see, after this the cells were merged not into one common cell, but were merged row by row.

Merging via context menu

It is possible to merge cells via the context menu. To do this, select the cells that need to be merged with the cursor, right-click on them, and select “Format Cells” in the context menu that appears.

In the cell format window that opens, go to the “Alignment” tab. Check the “Merge cells” checkbox. You can also set other parameters here: text direction and orientation, horizontal and vertical alignment, auto-width, word wrap. When all settings are completed, click on the “OK” button.

As we can see, a merger of cells has occurred.

Lossless merging

But what should you do if several of the cells being merged contain data, because when merging, all values ​​except the top left one will be lost?

There is a way out in this situation. We will use the CONCATENATE function. First of all, we need to add another cell between the cells that we are going to connect. To do this, right-click on the rightmost cell to be merged. In the context menu that appears, select “Insert...”.

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 being connected, after adding a column. For example, to combine cells A2 and C2 in this way, insert the expression “=CONCATENATE(A2;C2)” into cell B2.

As we can see, after this, the symbols in 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 “Copy” from the context menu.

Then, move to the right cell with the initial data, and by clicking on it, select “Values” in the insert options.

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

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

This way we end up with a new cell containing the data that should have been merged, and all the intervening cells have been deleted.

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

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 a single general data header that has different meanings. You can see an example of such information display in the image below. How to merge cells into excel step by step read on. 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 one is using context menu via 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.

As well as in previous method Select the cells you want to merge. 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

Structure Excel document is strictly defined and in order to avoid problems and errors in the future with calculations and formulas, in the program each data block (cell) 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 starting to work with office Excel program, are wondering: "How to merge cells in Excel?". To use this function, the editor has several special teams, which will help you accomplish your plans 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.

You can use it to merge cells in the editor standard features. 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 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 later earlier versions, the merging icon is located right on the panel, by clicking on which you can quickly connect required areas and at the same time align it in 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 without any problems you can return everything to initial 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 use no less important additional functions, how to: create a whole group of cells united by rows and merge without centering the data.

In some cases, it is impossible to merge, and the buttons on the panel are inactive. This problem may arise due to established protection or the document is missing general access. 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 loss 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 definitely the most in a simple way merging cells without losing any information. For example, the "+" symbol is used to summarize information in cells, and "&" is used to glue it together. This symbol is located on the top row of the keyboard with 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 choosing 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 specified step. Some of them have the ability to connect text data with formulas without subsequent loss of information.