How to merge cells in excel without losing data. Practical Tips - How to Merge Cells in Excel

You can merge cells in Excel different 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 click on them right click mice. From 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

We highlight 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, it will appear extra menu. 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 between them extra column. 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, inserted into it new column, and write the following formula to link 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 « Special insert» – “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".

I think the methods discussed are enough to combine data or text into Excel cells. At the same time, you can make sure that the data in the merged cells is not deleted.

Rate this article:

Almost all users starting to work with office program Excel, people 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 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 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.

Sometimes Excel surprises with its requirements to do something through uh... macros. Recently, the task arose to split several merged cells one at a time so that the entire range could be sorted normally. I don’t think anyone needs to explain how easy it is to remove the merging of cells by splitting them one by one (I mean format-alignment-uncheck the “merge cells” box). But, unfortunately, Excel has a very interesting property: when unmerging cells, it does not ask how exactly we would like to return them to their normal, separated state, but stupidly writes in the upper left cell the value that was in the merged cell, and leaves all other cells blank . Well, naturally, there is no need to talk about any normal sorting by previously merged cells. And it would be good if there were only 200-300 of them. What if there are sixty thousand of them, divided into groups of 10 cells? Manually copy six thousand values ​​into the remaining empty cells?

A typical picture - we want to sort the list by last name, so that Kuznetsov still comes after Ivanov. The example is, of course, very simplified.

Excel won't let us sort the table just like that - the cells are merged. But even if we cancel the merger, then each surname will be written only in the top cell, and the rest will have to be filled in. It will turn out like this:

But we need each cell to have a corresponding value, otherwise when sorting it will turn out to be hell!

Sorry, do this standard means Excel, as for example, we or - impossible. Macros must be used. But, thank God, there are good people in the world who have already done this before us. Moreover, they wrote not just one macro, but several. At the same time, macros allow you not only to separate cells while preserving the information contained in them, but also much more:

• ungroup cells in selected range with padding

I checked everything, they work great. The simplest code, as shown excellent results, I want to post it here too. Just in case it suddenly disappears original page, the link to which I provided above.

So, let's create a macro with the code:

ABAP

Sub UnMerge_And_Fill_By_Value() " ungroup all cells in Selection and cells of each former group fill with values ​​from their first cells Dim Address As String Dim Cell As Range If TypeName(Selection)<>"Range" Then Exit Sub End If If Selection.Cells.Count = 1 Then Exit Sub End If Application.ScreenUpdating = False For Each Cell In Intersect(Selection, ActiveSheet.UsedRange).Cells If Cell.MergeCells Then Address = Cell.MergeArea .Address Cell.UnMerge Range(Address).Value = Cell.Value End If Next End Sub

SubUnMerge_And_Fill_By_Value() "ungroup all cells in Selection, cells of each former group fill the values ​​of their first cells DimAddressAs String DimCellAs Range If TypeName(Selection)< > "Range" Then Exit Sub EndIf If Selection. Cells. Count = 1 Then Exit Sub EndIf Application. ScreenUpdating=False For EachCellIn Intersect(Selection, ActiveSheet. UsedRange) . Cells If Cell. MergeCellsThen Address=Cell. MergeArea. Address Cell. UnMerge Range (Address) . Value = Cell. Value EndIf Next

As a result of executing the macro, all selected merged cells are split one at a time and filled with exactly the same value that was in the merged cell. Sort, I don't want to.

Just remember that after executing a macro, this operation cannot be canceled. Therefore, it is better to work with a copy of the table.

Well, if, after the cells have been re-sorted, you need to merge the cells again with the same values(let’s forget for a moment that merged cells are evil, because if visual clarity is required, then we cannot do without them), then we will again have to use a macro. There are a lot of links on the Internet - but only one worked for me.

Just in case, I’ll give you the code:

ABAP

Sub MergeCls() Dim ri As Integer, r2 As Integer, Col As Integer r1 = ActiveCell.Row r2 = ActiveCell.Row Col = ActiveCell.Column Do If Cells(r1, Col)<>Cells(r2 + 1, Col) Then If r1<>r2 Then Range(Cells(r1 + 1, Col), Cells(r2, Col)).ClearContents With Range(Cells(r1, Col), Cells(r2, Col)) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With End If r1 = r2 + 1 End If r2 = r2 + 1 Loop Until Cells(r2, Col) = "" End Sub

SubMergeCls() DimriAs Integer, r2As Integer, ColAs Integer r1= ActiveCell. Row r2= ActiveCell. Row Col= ActiveCell. Column If Cells (r1, Col) < ; > Cells (r2+ 1 , Col) Then If r1< > r2Then Range (Cells (r1+ 1, Col) , Cells (r2, Col) ) . ClearContents With Range (Cells (r1, Col) , Cells (r2, Col) )

Many users who are starting to get acquainted with the spreadsheet editor are interested in how to combine cells in Excel. There is for this certain commands, functions in the utility itself and specialized add-ons and macros. All methods are simple, below you can find detailed instructions.

This is one of the most accessible and quick options, allowing you to merge cells in Excel. The algorithm will be like this:

• select the desired range;
• on the marked table part, right-click with the mouse;
• in the menu that appears, select the “Format” sub-item;
• the “Alignment” item will open;
• Check the box next to merging cells in the table.

The method is the simplest, but is suitable exclusively for texts - there is no point in arranging digital elements in Excel, there is no point in doing so. It must be said that in this case, the combined area contains information from its upper left side, which Excel immediately notifies about. To save the information, you must first copy them to a separate area, and only then add them to the remaining text content.

It is better to do the merger in advance, while everything is still empty, because after the procedure the information will remain only in the left window at the top.

How to merge cells in Excel using the toolbar

In older releases of the program, the merge label is located immediately on the panel. If you click on it, you can quickly connect areas, as well as center alignment. This helps speed up the process of forming text headings in a line, which are often created in this very way.

If, after executing the command, everything you need is located in the center, and you don’t need this, you can change the position yourself using special commands.

The Excel 2007/2010/2013 panel also has such a button, in the “Home” (“Alignment”) section. But here it already has a drop-down menu to increase the number of steps performed through it.

The commands will help not only to merge cells in Excel with alignment in the middle, but also to make two additional options actions:

• creating a group united by lines;
• alignment without adjusting the center position.

It’s just that you won’t be able to arrange the columns in this release. In some cases, it is also impossible to connect the area, and commands and buttons remain inoperative. This option is possible if the sheet is protected or only general access is allowed. Before merging cells in XL, you should remove these conditions to open access to table formatting.

Special function

If you do not want to lose something important, so that you do not need to place text data on additional places, return back after the procedure, you should use the “Connect” function. The instructions will be simple:

• select a cell next to the areas to be combined and format it (size 4x4 or 5x3);
• you write a special formula in it - CONCATENATE (A1; A2), where the cells are marked (one at a time!), from which the text will be combined.

As a result, you get the field the right size.

We use the & symbol

How to merge cells in Excel without losing information? Just use special character. Everyone knows that “+” is used for operations with sums in table rows, and “&” is used for concatenating contents. You will find the icon on the number “7” on the top of the keyboard in the English layout.

Important Tips:

• If additional text needs to be added to the data, it is highlighted in quotation marks (A3&“new text”&B5);
• If you use the initial letter extraction function, you can get the last name and initials during the concatenation process.

Macros and add-ons

You can combine cells in Excel using the same method (for example, making 2x2 areas) in a fast way, using the combinations Ctrl + C and Ctrl + V, that is, copy and paste the formatting.

Similar actions are performed by writing a macro - a formula or using a macro recorder. If you can't create a programmatic algorithm, you should use well-designed Excel add-ins. With their help, you can connect columns, rows and even set a step. There are also those that can combine text data with formulas, leaving the data untouched.

How to merge cells in Excel? There are several ways to do this. You just need to carefully read the instructions and choose the right option for yourself.

class="eliadunit">

If necessary, Excel program It is possible to combine data from several cells into one. You can merge both data of the same type (number + number) and data of different types (number + word). These operations are performed using the SPECIFY formula (or in the English version CONCATENATE). An analogue of this formula is the use of a sign called an ampersand (&). Learn to use the clutch Excel is better using real examples.

The first example: using combining words into one cell in Excel (for example, when writing ads in Yandex Direct). Task: add the words “Call me!” to the ad text. and “Call now!” Column B contains the text of the advertisement, column C contains the word “Call”, and the next column: “!” or now!":

For the purpose of large-scale unification, we insert formulas in column A (the ampersand sign is inserted when English layout Shift+7):

=CONCATENATE(B1;" ";C1;D1)

=B2&" "&C2&D2

=CONCATENATE(B3;" ";C3;D3)

=B4&" "&C4&D4

class="eliadunit">

The quotation marks contain a space character. You can insert words or even sentences in this way. As a result, we get the following picture in Excel:

Typically, such a combination is used for text and, accordingly, the CONCATENATE formula applies to text. But it can also be used with numbers. Here's an example:

It is worth paying attention to the fact that you can work with the results obtained as with numbers. Sum, multiply, divide, etc. But you can’t use it in a numerical formula. For example, if you enter the formula =SUM (A2:A5) in the sum cells, the calculation will be 0.

Next, let's look at an example when you need to combine text and a cell with a number to which some display format is applied (date, finance, etc.). If you use only the ampersand or the CONCATENATE formula, the required value will not be displayed correctly (column A):

To display correctly (column B), you need to use the TEXT formula. Its syntax is as follows: TEXT( cell number or value ;« numeric value format »). The formats can be found in the Excel menu in the “Home” tab, “Number” section.

Having the above knowledge, you can skillfully Microsoft Excel merge values ​​into one cell.