How to delete merged cells in excel. Merging cells in MS Office Excel. How to merge cells using the Format Cells function
Cells that form a rectangle (with at least one side touching each other) can be combined into one large cell that has a common format. You can merge cells as standard using Excel, and using macros and add-ons. When merging cells with data using standard methods The cell information is replaced with the text of the top left cell of the merged range. The use of macros and add-ons can significantly speed up and simplify this type of operation, as well as save the values of all merged cells.
Merge cells in Excel without losing data
When merging cells (both empty and containing data), it speeds up and simplifies the work using VBA add-ins for Excel, which allow you to merge cells in a specified range by rows, by columns, as well as with a given step and without losing text. Exist ready-made solutions tasks related to merging cells.
Quickly merge cells in Excel with an add-in
An even more flexible version of the add-in allows you to save the text of the cells being merged, separating it with a separator character, that is, it merges cells without losing data. Below is the dialog box for this add-in.
Using the add-on allows you to:
1. With one click, call the macro dialog box directly from the Excel toolbar;
2. merge cells by row;
3. merge cells by columns;
4. when merging cells with text, save the text data of all merged cells;
5. choose at your discretion the delimiter symbol (a symbol that separates the text of one cell from the text of another);
6. when merging empty cells with cells containing formulas, save the formulas;
7. when merging cells containing formulas, save formulas from the left/top cells;
8. selectively merge cells with a given step.
Video on quickly merging cells without losing values
How to merge cells in Excel using standard tools?
Merging cells using the context menu
The easiest way to merge cells is to select the range of cells to be merged, click right click mouse anywhere in the selected area and select "Format Cells..." in the context menu that appears. Next, in the "Format Cells" window, go to the "Alignment" tab, in the "Display" group, check the "Merge Cells" box and click the "OK" button.
Merging cells via the toolbar
In Excel 2003, the Formatting toolbar includes a button that, when clicked, merges selected cells and aligns the contents of the cell to the center, which is often used to create headings.
In Excel 2007/2010, a similar button is located on the "Home" tab, in the "Alignment" button group. If you go to the menu of this button by clicking the small arrow to the right of the button, then in addition to the “Merge and place in the center” option, “Merge by rows”, “Merge cells” and “Unmerge cells” will become available.
Why are menu items and buttons for merging cells inactive?
If you cannot merge cells because the necessary menu items and buttons are inactive, it means that either the sheet is protected, which must be removed before merging, or it is allowed general access to the workbook, which must be undone before merging cells. .
How to merge cells in Excel using hotkeys?
You can speed up merging cells somewhat by copying the merged cell and then pasting it into the desired locations using the hotkey combinations Ctrl+C for copying and Ctrl+V for pasting. In addition, you can write a simple macro ( or record it with a macro recorder) And assign your own hotkey combination to this macro, for example Ctrl+M. An example code for a simple macro is given below.
Sub Obedinenie_Vydelennyh_Yacheek() Selection.MergeCells = True End Sub
In order to transfer this program code to your computer, hover your mouse over the field with the program code, click on one of the two buttons
in the right top corner this field, copy the program code and paste it into a project module on your computer (more information about
In Excel, you can merge cells either by rows or columns. When merging cells in Excel, you need to consider some nuances:
- When merging cells in Excel, the data recorded in the cells will be deleted except for the data recorded in the upper left cell. When merging text cells In Excel, you need to copy the text from the cells in advance, for example, into a notepad.
- When merging multiple cells across rows and columns, you can only merge the same number of cells in a row and column. For example, you cannot merge 2 cells in one column and 1 cell in another column, or 2 cells in one row and 1 cell in another row.
Merging cells via cell format
To merge cells in Excel, you need to hover the cursor over one of the cells to be merged, click left button mouse and without releasing it, select all the cells that need to be merged. Release the left button and press the right mouse button. A context menu will open in which you need to select Format Cells.
Combining rows in Excel
A window called Format Cells will open. Also open this window to merge cells into Excel hot keys can be used. Press on the keyboard Alt key and holding it alternately press and release next keys I, Ш, I, after this a window will open and you can release the Alt key. In this window, you need to go to the Alignment tab and on this tab check the box for merging cells.
Window in which you can combine rows in Excel By clicking on the OK button, the Format Cells window will immediately close and Excel will merge the cells you selected. If some data was written in the cells you selected, then after closing the Format Cells window, a warning from Microsoft will appear with the following text: The highlighted area contains multiple data values. Merging cells will result in the loss of all values except the top left one.
Values are lost when merging cells in Excel 2010 By clicking the OK button in this warning, it will immediately close, and your cells will be merged, but the data from them will be deleted and only the data that was recorded in the selected upper left cell will remain.
You can split a cell in Excel that has been merged You can split merged cells in Excel like this: hover the cursor over this cell and press the right mouse button. A context menu will open in which you select Format Cells. A window will open in which you can split cells in Excel that were previously merged. In this window, you need to go to the Alignment tab and on this tab uncheck the Merge Cells option and click OK. After this, the Format Cells window will close and the cells will be separated.
Merging cells via the toolbar
You can separate and merge cells in Excel using the Excel toolbar. To merge cells in Excel, you need to hover over one of the cells to be merged. Press the left mouse button and without releasing it, select all the cells that need to be combined and only then release the left mouse button.
In Excel, merging cells is done through the toolbar. In the Excel panel, click on Merge and Place in Center. After this, all selected cells will be merged. To split a cell in Excel, you need to hover the cursor over the merged cell and press the left mouse button.
With one click you can split a cell into two or more in Excel. Then click on Merge and place in the center or on the pop-up menu next to this item and select Unmerge Cells. This way you can split cells in Excel.
Merging values from cells
You can merge text in excel cells without losing data, but the data will be placed in new cell. Merging cells in Excel without losing data is done using the formula = cell name & » » & cell name. For example, if you need to combine text into Excel cells B3 and C3 then the formula will look like this =B3 & » » & C3.
Using a formula, you combine data in cells in Excel Merging the contents of cells in Excel will occur in any cell you select, for example A3. You need to place a formula in this cell, and to do this, move the mouse cursor over cell A3 and press the left mouse button. Then insert =B3 & » » & C3 into the formula bar and press on the keyboard Enter key and the cell values in Excel will immediately be merged. Don't forget to put an equal sign = before the formula.
Video
This video shows how to combine values from Excel in different cells in one.
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 if data has already been entered into them? Will they really be destroyed? Let's figure out how to combine cells, including without data loss, in the program Microsoft Excel.
Although, we will show merging cells using the example of Excel 2010, but this method 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 Excel tab“Home”, click on the icon on the ribbon “Merge and place in the center”.
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 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 “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, the cells have merged.
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 you can see, after this, the characters in the common cell are “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 working with tables, sometimes you have to change their structure. One variation of this procedure is to concatenate strings. In this case, the combined objects turn into one line. In addition, it is possible to group nearby inline elements. Let's find out how these types of joins can be carried out in Microsoft Excel.
As mentioned above, there are two main types of string merging - when several lines are converted into one and when they are grouped. In the first case, if 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 in in the same form, they are simply combined into groups, objects in which can be hidden by clicking on the symbol icon "minus". There is another option for connecting without losing data using a formula, which we will discuss separately. It is on the basis of these types of transformations that the various ways combining lines. Let's look at them in more detail.
Method 1: Merge via 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 actual merging procedure, you need to select nearby rows that you plan to merge.
There are also alternative options for navigating to the formatting window. For example, after selecting rows, while in the tab "Home", you can click on the icon "Format" located on the ribbon 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, which is located on the ribbon in the lower right corner of the tool block "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 selecting the necessary elements. But in this case, the transition will be made to that window tab "Cell Format", which was last visited.
With any option of going to the formatting window, everything further actions merging lines must be carried out according to the algorithm described above.
Method 2: Using Tools on the Ribbon
You can also merge rows using the button on the ribbon.
But not in all cases it is required that the text be placed in the center. What to do if you need to place it in a standard form?
Method 3: Joining rows within a table
But it is not always necessary to combine lines to the end of the sheet. Much more often, the join is made within a specific table array. Let's look at how to do this.
You can also join within table boundaries using the tools on the ribbon.
Method 4: Combining information in rows without losing data
All of the merging methods listed above imply that after completion of the procedure, all data in the merging elements will be destroyed, except for those located in the upper left cell of the area. But sometimes you need to combine without loss certain values, located in different rows of the table. This can be done using a function specially designed for such purposes. CONNECT.
Function CONNECT belongs to the category of text operators. Its task is to combine several text lines into one element. The syntax of this function is as follows:
CONCATENATE(text1,text2,…)
Group Arguments "Text" can be either separate text or links to the elements of the sheet in which it is located. It is the last property that we will use to complete the task. A total of up to 255 such arguments can be used.
So, we have a table containing a list 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 are also Alternative option carry out the specified procedure to combine data from several lines into one without loss. In this case, you won’t even need to use a function, but you can get by with a regular formula.
Method 5: Grouping
Additionally, you can group rows without losing their structural integrity. Let's see how to do this.
As you can see, the method of merging strings into one depends on what type of merging the user needs and what he wants to get as a result. 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 also group rows. In addition, there are separate options for performing these tasks, but their choice is influenced only by the user’s preferences in terms of convenience.
The need to merge cells in Excel arises quite often for users. It would seem that there is something wrong here, because there are quite a lot of ways to do this. However, it is important for users, especially those who are accustomed to working with Word, to remember one very important point: When merging cells, only the value in the topmost and leftmost cell remains. As for the remaining data, they will simply be erased.
If at this stage you are upset, I want to make you happy: there is still a way out, and more than one! As a matter of fact, I would like to devote this material to the question of how cells are merged in Excel without data loss.
How to merge cells
However, before I tell you about this, I would also like to mention ways to merge cells when the data has not yet been entered or if its disappearance would not upset you too much. Still, there are many users who want to be enlightened on this issue as well.
How to merge cells without losing data
Here I will probably also highlight two ways to do this:
- The first is to use an add-in, let it be VBA-Excel. After you download this product and install it, an additional item “Merge cells” will appear in the main program ribbon. Accordingly, to do this, you just need to select them, and then specify the desired word separator: period, comma, semicolon or line break.
- The second method is simpler and involves using the built-in Excel . I already told you how to use it once, but I’ll repeat it briefly again: call the “Function Wizard”, then start typing the name in the search. In the “Function Arguments” window for the “Text 1” field, select the first cell among those that you want to merge with the left mouse button. In the “Text 2” field, select the next cell with the cursor and so on until they run out. Please note that you cannot merge the entire range of cells at once; they must be separated by a semicolon. For example, your formula might look like this: =CONCATENATE(A1,B1,C1).
Well, there doesn’t seem to be anything complicated. Now you know how to combine empty cells with each other and how to do the same with cells whose data you absolutely must not lose!
Video to help
