Excel how to merge cells with data. The result will be a combined field of type

When designing tables to display information more clearly, it becomes necessary to combine several cells into one. This is often used for example when specifying one general header data that has different meanings. You can see an example of such information display in the image below. How to merge cells into excel step by step read on. You can merge not only horizontally, it is possible to merge vertically, as well as groups of horizontal and vertical cells.

An example of combining a digit with one header “Digit” and different data

How to merge cells in excel

There are two ways to merge cells. The first is using the context menu through the format. Select all the cells you want to merge and click on the selected area right click mice. In the drop down context menu select “Format Cells...”.

In the format window, go to the “Alignment” tab and in the display block, check the “merge cells” box.

Check the “merge cells” option in the “Alignment” tab

If there is any data in the cells, Excel issues a warning every time that all data except the top left one is lost. Therefore, be careful when merging and do not lose important data. If you still need to merge cells with data, click “OK” to agree.

In my case, out of 10 numbers in the cells in the merge area, only the number “1” from the top left cell remained.

By default, Excel aligns data to the right after merging. If you need to quickly combine and at the same time immediately align the data to the center, you can use the second method.

Just like in the previous method, select the cells that need to be merged. At the top of the program, in the “HOME” tab, find a block called alignment. This block has a drop-down list that allows you to merge cells. There are three types for union:

Merge and Center - Clicking this option will result in exactly the same merge as in the previous example, but Excel will format the resulting data to be centered.
Merge by rows - if an area of cells with several rows is selected, the program will merge row by row and, if there is data, will leave only those on the left.
Merge cells - this item works exactly the same as in the first option through the format.
Cancel merging - you need to select a cell that was previously merged and click on the menu item - the program will restore the cell structure as before merging. Naturally, it will not restore data before merging.

After trying any of the methods, you will know how to merge cells in Excel.

The second way to quickly merge cells

Structure Excel document is strictly defined and in order to avoid problems and errors in the future with calculations and formulas, in the program each data block (cell) must have a unique “address”. An address is an alphanumeric designation for the intersection of a column and a row. Accordingly, only one row cell should correspond to one column. Accordingly, it will not be possible to divide a previously created cell into two. To have a cell divided into two, you need to think about the structure of the table in advance. In the column where separation is needed, you need to plan two columns and merge all cells with data where there is no separation. In the table it looks like this.

It is not possible to split a cell into two in Excel. You can only plan the table structure when creating it.

In line 4 I have a cell divided into two. To do this, I planned in advance two columns “B” and “C” for the “Rank” column. Then, in the lines where I don’t need division, I merged the cells line by line, and in line 4 I left them without merging. As a result, the table contains a "Section" column with a cell split into two in the 4th row. With this method of creating a division, each cell has its own unique “Address” and can be accessed in formulas and when addressing.

Now you know how to split a cell in Excel into two and you can plan the table structure in advance so that you don’t break the already created table later.

Excel is used mainly for carrying out complex calculations and compiling tables with the results, in which the data will change automatically when the primary data changes. To make it clear what is presented in the final table, it is necessary to properly design the table header. Quite often, some data can be combined into one group, and in a table for several columns or rows one title is indicated in the header. In Excel, the sheet is divided into cells, and in order to formalize our idea of grouping data, we need to combine several cells by placing common text in them. Later in the article we will discuss the question of how to combine cells in Excel in various ways.

The menu also changes from version to version of the program, but there is always one universal method, which allows you to combine cells in Excel, and works in all versions of Excel. First, we'll look at how to merge cells in Excel 2010 using the menu. And so, select the necessary cells, go to the “Home” tab and find in the “Alignment” sector the “Merge and place in the center” item. In our case, the cells are filled with text and when we click “Merge and place in the center”, we receive a warning that the data of all cells will be lost if we continue to merge cells in Excel in this way, except for the top left cell, the data from which will be placed in the center. If you click the arrow to the right of “Merge and Center,” a list of other options for merging cells in Excel will appear that may be more suitable for you.

Now let's look at how to combine cells in Excel in a way that works in all versions of the program. To do this, we also select the necessary cells and right-click in the area of the selected cells, and then select the “Format Cells...” item in the menu that appears. The “Format Cells” window appears, in which we go to the “Alignment” tab and check the box next to “merge cells”. As a result, we get the same data loss warning and cell merging without placing the information in the center.

To merge cells in Excel without losing data, you need to use various tricks. For example, on many forums you can find user-written macros that are called by keyboard shortcuts. The procedure is the same, select the cells and apply the appropriate macro. Other methods involve manually merging text in an adjacent cell first simple copying or by drawing up an appropriate formula.

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 in what ways these types of associations can be carried out in Microsoft program 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 data loss using a formula, which we will discuss separately. It is on the basis of these types of transformations that 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 certain values located in different rows of the table without loss. 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

In addition, 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, this material I would like to devote myself to the question of how to merge cells in Excel without losing data.

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 does 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, it will appear in the main program feed additional item"Merge cells." 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

Quite often when working with tables in a program Microsoft Excel There is a situation when you need to combine 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 the Excel “Home” tab, 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, 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 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, 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.