Merge rows, columns and cells in Excel. Combining Rows in Microsoft Excel

Program Microsoft Excel used for working with spreadsheets in many offices. The application has wide functionality, but is far from the most friendly interface. New users of the app find it difficult to understand how to perform even basic actions in the app, such as merging cells. Often, when working with tables, users have to deal with the need to merge two or more cells horizontally or vertically. Let's look at how to do this in Microsoft Excel as conveniently as possible.

Please note: Depending on the program versionMicrosoftExcel, the instructions below may vary slightly.

How to Easily Merge Cells in Microsoft Excel

First of all, let's look at the basic tools of the program designed for merging cells. The program provides a separate tool for converting several cells into one, while it boasts wide functionality.

To simply merge multiple cells into one horizontally or vertically, you need to select the cells, then click in top menu in the “Home” section to the “Merge and place in center” item. IN in this case all selected cells will be merged into one; note that information in it will be saved only from the top left cell of the selected array. At the same time this information after merging, it will be placed in the center of the resulting line.

To merge cells, but the information from the cells does not move to the center, you need to click on the down arrow of the cell merging tool, and select the “Merge cells” option from the presented tools. After this, the default entry (the top left one from the array, or the only one that is available) will be located in the merged cell, according to the existing cell formatting.

Another opportunity that standard tools provide is to combine an array of selected cells into MicrosoftExcel by row. To merge selected cells row by row, you need to click on the “Merge by rows” button in the list additional features cell merging tool on the dashboard.

How to merge cells in Microsoft Excel using the context menu

Word also has a separate context menu that contains options for merging cells. If there is a specific task of merging cells that cannot be solved by the above tools, it is recommended to use this menu.

Before calling the context menu to merge cells, you need to select the data array on which you will work. Next, right-click on any place in the selected array and select "Cell Format".

A menu will open in which you need to switch to the item at the top "Alignment". Here, to merge cells, check the option "Merging Cells", after which you can make other text formatting settings - set the vertical and horizontal alignment options, enable the word wrap function, and so on. When all the necessary settings have been made, click “OK” to complete the specified actions.

How to merge cells in Microsoft Word without losing data

As noted above, when merging multiple cells containing data, only the information from the top left cell is retained. Let's look at how to merge cells without losing data:

As you can see, there remains a cell that contains the combined information. If necessary, you can now merge several cells so that this data is present in them.

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 mouse context menu, having previously selected the fields that require merging, select Format.

We are interested in the tab Alignment.

In property Display mark with a tick Association and press OK.

Should be taken into account 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, a merge button with a similar function is 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, after which OK.

There are empty columns in the second column - select them.

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

After which 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 doesn't 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).

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. Right-click 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.