Menu
homeAndroid

How to delete merged cells in excel. Merging cells with data. Video on quickly merging cells without losing values

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

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 merged blocks in your document with with the required text, 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, 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".

Excel tables are built on a slightly different principle than in Word. If in the latter we can separate cells, then in Excel a cell is an indivisible unit. The issue of getting two columns under one common header is quite common, so it is important to know how to merge cells in Excel, since in this case it is necessary to merge a table fragment.

Method 1 – Toolbar.

On the panel you will see a merge button.

To use it, highlight the range you want to merge and click it.


As a result, a merger will take place.


If you click on the arrow next to this button, you will see a menu.

The Merge and Center command allows you to center the contents of the new fragment. If you try to merge a range with a value, only the data from the first address being merged will be saved. And you will see a warning.


Consent will result in the loss of remaining data.


The Merge by Rows command will create consolidated cells in each row.



The “Merge Cells” command is similar to the first one, only without centering the text.


The last point answers the question of how to unmerge cells in Excel.

Method 2 - Using the Menu

IN latest versions office classic menu has been replaced by tabs. And familiar windows are called using small icons in the lower right corner of the tab.


Don't forget to first select the cells to be merged and click on this icon. The alignment window will appear.


Check the box next to “Merge Cells”. Here, in the corresponding drop-down fields, you can specify data alignment parameters.

Method 3 – Copy Already Merged Data

If you need to reproduce an existing join, use the clipboard. The hotkey combination for merging cells in Excel in this case will be as follows:

CTRL+C – copy to clipboard.
CTR+V – paste from the clipboard.

Place the pointer on the selected position.


Copy by pressing CRTL+C. A sign that the data will be copied will be “running ants” - the animation of the frame along its borders.


Place the cursor on the desired cell. It is not necessary to select the same number of addresses; Excel will automatically select the required number and execute the command. True, the data will be lost. Click the buttons to paste from the clipboard. A similar union will appear in the selected location.

Method 4 – pattern format

In Excel, as in any program Office package, there is a very convenient “Format by sample” button.
It is located in the "Clipboard" section.


Point to the merged fragment and click this button. Move the pointer to the desired location and click the mouse. The formatting will be fully reproduced.

While the command is waiting, a brush icon will appear near the mouse pointer. As soon as you click the mouse, the icon will disappear and a new merged fragment will appear.

How to merge the contents of a cell in an Excel table while preserving the data

This current problem. In any of previous methods Excel deletes the data. To save them, use the “Concatenate” function.
Click the Fx icon next to the formula bar. This will open the function insertion window.


Enter the name “Connect” in the search bar and click the “Find” button.



Select the found function and click OK. A setup window will appear.


Specify the range with your hands or select it with the mouse.


Please note that the function must be at an address separate from the ones being combined. If you need to put consolidated data into a joined range, do the join in advance and then enter the function there.

There are no hotkeys that allow you to merge cells in Excel without losing data, so you can use other methods.

Another opportunity to combine text from several Excel cells
– use the “&” operator, which combines strings. To start entering a formula, place the pointer at the place where it will be located. new text and click the "=" sign. Then click on the first address, press &, click on the second, and so on.


Combining text from two different cells in one in Excel versions 2003-2010 is carried out similarly.

How to remove merging cells in Excel

You can use the last item from the “Merge Cells” button menu.


Or you can uncheck the box in the formatting window.

How to merge using a macro

One of the most complicated ways– write a program yourself that will automate the process. In the package Microsoft Office added code developer to VBA language. To use it, press ALT+F11. The window for adding program code will open.


Run the Insert – Module command.


A window for entering data will open.


Copy and paste the code.

Sub Merge_Column() Dim i1 As Long Dim i2 As Long Dim f As Long Dim textCol As String Application.DisplayAlerts = False For f = 1 To Selection.Areas.Count For i1 = 1 To Selection.Areas(f).Columns.Count textCol = Selection.Areas(f).Cells(1, i1) For i2 = 2 To Selection.Areas(f).Rows.Count textCol = textCol & Chr(10) & Selection.Areas(k).Cells(i2, i1) Next Selection.Areas(f).Columns(i1).Merge Selection.Areas(f).Cells(1, i1) = intext Next Next Application.DisplayAlerts = True End Sub

Go to Excel by clicking on its icon on the left top corner.


Select the range to be merged and run the macro on the “View” - “Macros” tab.


Select the macro you want and click the Run button.


The table fragments will be merged.

Cell button not active in Excel

This is a rare case, but nevertheless it happens.

Sort merged Excel cells

The difficulty here is that all sorted fragments must have the same size. Therefore, for non-standard joins in a column or row, you need to cancel the join operation and perform it again in the same size as all the others. For example, if you are trying to sort a column where everything is combined by two, and one by three, either combine everything by three, or convert the three-cell cell into two. Otherwise you will get this error


After you have put the associations in order, click the “Sort and Filter” button in the toolbar (on the right) in the “Home” section.

Select the right type sorting.

How to find merged cells in Excel

If Excel sheet large, it can be difficult to detect merged data. In this case, we will use the search. On the Home tab, click the Find and Highlight button and select Find. Or click hot Ctrl keys+ F. Read more


Then click "Options" and "Format".


Check the box next to “Merge Cells” and click “OK”.


Then click “Find all” or “Find next”, and required addresses will be highlighted sequentially. When you click on the first button you will see a list of all combined ranges.


That's all the information on the merged cells. We hope it will help you quickly and easily create workbooks in Excel and work with them without any problems.

Have a great day!

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 cells, click on them right key mice;
  • 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 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.


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 Excel type 2003 and up 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 for quick definition text header in a line, which are 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, we write a formula like CONCATENATE (A1;A2) in it, after which we 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 top row keyboards 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 required 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.

IN office suite MS Excel can combine several horizontally or vertically adjacent fields into one large one. This can be done in several ways.

Using the context menu

Calling right key mice context menu, having previously selected the fields that require merging, you should select Format.

We are interested in the tab Alignment.

In property Display mark with a tick An association and press OK.

Should be considered that only the top left value will be saved in the combined field.

Ribbon button for excel 2007 and higher

You can also use the button on the ribbon to merge.

IN Office versions 2013 it is located on the tab home and it looks like this.

Default button unites and produces leveling text in the center. In the drop-down list, among the proposed options there is also merging by rows, without alignment and cancellation, if you change your mind. With the same button you can divide them back.

Before clicking this button, you should highlight range of vertical or horizontal cells.

In Office 2010, the button has almost the same appearance with a similar drop-down list.

In Office 2003, the merge button similar function located on the toolbar Formatting.

You can glue cells together using copy-paste, that is copy(Ctrl+C) merged, after which insert(Ctrl+V) it to the required location.

Using the “CONNECT” function

Excel has a function Couple. Concatenates multiple lines into one.

In the function itself CONNECT we should indicate the cells or range we need, separated by semicolons:

=CONCATENATE (cell1;cell2;…) from 1 to 255 function arguments.

A similar coupling procedure can be performed as follows:

Writing macros

You can write a simple macro (or use macro recorder) and assign him some convenient for you hotkey combination. Possible options The code for such a macro is presented below.

The second option will ensure the merging of all combined fields while preserving the original data.

Select the required fields and run the macro. Editor VBA opens with a combination Alt+F11.

We contribute program code We execute the macro.

To run the created macro, click Alt+F8. A window will open Macro(Macro). On the list Macro name(Macro name) select the required one and click Execute(Run).

How to merge columns

You can combine values ​​in Excel two columns in one line by line. For example, we want to combine a document and information about it.

This can be done as follows. We highlight first column then on tab home — Editing click on the field Find and highlight, the command of interest is in the list " Select a group of cells...«:

In the window that opens, put a tick - Empty cells, then OK.

In the second column there is empty columns– select them.

After the equal sign we enter formula values ​​of the corresponding field of the desired column:

Then Ctrl+Enter and the values ​​will only be inserted into empty cells in the second column.

How to merge cells using hotkeys

By default this combination does not exist, however, you can do this: select the required cells, merge them using the button on the ribbon.

For any subsequent selection, press F4 (repeat the last command!).

Also in the settings you can assign the required combination for the desired command at your discretion, or write a macro for these purposes (for more advanced users).