Merging cells in Microsoft Excel. Why are menu items and buttons for merging cells inactive?

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 must first copy the text from the cells, for example, to Notepad.
When you merge 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 while holding it, press and release the following keys I, Ш, I one by one, after which 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 recorded in the cells you selected, then after closing the Format Cells window, a warning from Microsoft will appear with the following text: The selected area contains several 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 a 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 in 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 the Enter key on your keyboard and the cell values will immediately be merged in Excel. Don't forget to put an equal sign = before the formula.

Video

This video shows how to combine values from Excel in different cells into one.

There are times when you need to combine several cells, for example, when creating table headers. Microsoft Excel 2013 allows you to do this. In this case, the following condition must be met: only the left cell of the selected range can be filled, and the remaining cells must be empty. To merge several cells into one, select them and then click on the button Merge tabs Home. The selected cells will be merged into one, and the data in the merged cell will be placed with center alignment (Fig. 1.14).

You can use other options for merging cells, as well as cancel merging, by clicking on the arrow on the right side of the button and selecting the appropriate command in the menu that appears. You can cancel a merge in one of two ways.

Click the merged cell and then click the arrow on the right side of the button Merge. In the menu that appears, select Unmerge Cells. Click right click mouse on the merged cell and in the appeared context menu select the item with the left mouse button Cell Format. In the dialog box that appears, go to the tab Alignment and uncheck the box Merging cells(there should be no check mark in the box). Click the button OK.

Aligning cell contents

For any single cell or for a group of selected cells, you can set the content alignment both horizontally and vertically. To set the horizontal alignment you need, use the buttons on the Home tab. There are buttons on the same tab to select vertical alignment.

Often when designing tables in Excel, there is a need to merge cells, for example, in order to make a heading for inserting a long line of text. In this article we will describe two simple ways, how to merge cells in Excel 2003, 2007, 2010, 2013 and 2016. In addition, we will describe some important points that the user may encounter when performing this operation.

How to merge cells in Excel using a button

The easiest way to merge cells in Excel is using the button on the Home tab. Select the cells you want to merge with your mouse and click on the down arrow next to this button. This will open a small menu with several options for merging cells.

Let's look at all the available options in order:

Combine and place in the center– When using this option, the cells will be merged, and their contents will be placed in the center of the new merged cell. This option convenient to use for creating general header for multiple columns.
Merge by row– merges each selected row into a separate cell.
Merge cells– simple merging of all selected cells.
Unmerge Cells– disconnects all selected cells, returning them to their original form.

If you have Word 2003, then the button for merging cells should be located on the toolbar.

How to merge cells using the Format Cells function

You can also merge cells using the Format Cells function. To do this, you need to select with your mouse the area that you want to merge and right-click on it. After this, a context menu will appear on the screen. Here you need to select the “Format Cells” menu item.

As a result, a window called “Format Cells” will appear on the screen. In this window, you need to go to the “Alignment” tab, check the box next to the “Merge Cells” function and click on the “Ok” button.

Merge multiple data cells

When merging cells, there is one thing you need to be aware of. If you merge 2 or more cells that have data in them, the resulting merged cell will only contain data from one cell (top left or right top cell), while the data in the remaining cells will be deleted.

If you try to perform such a merge, you will receive a warning about data loss. And in order to continue merging despite the loss of data, you need to click on the “Ok” button.

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 an example Excel programs 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 “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 we move to right cell with the initial data, and by clicking on it, select the “Values” item in the insertion 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.

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 concatenate a range with a value, only the data from the first concatenated address 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 right place 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.

Merging text from two different cells into 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 VBA code developer. To use it, press ALT+F11. The add window will open program code.

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