• Merging cells in Excel. Merging Cells in Microsoft Excel

    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 the cells, right-click on them;
    • 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 the 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 structure tables correctly.

    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 useful for creating a common 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 retain data from only one cell (top left or top right cell), while 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.

    In this lesson I will tell you how to combine text in cells in Excel using the Concatenate function. Both of these methods, which we will consider, allow you to combine several cells into a single array. The cells can be either text or numeric.

    Concatenate function in Excel

    The concatenate function allows you to combine several text strings, or numeric values, or references to other cells, as well as various combinations of these elements, into a single array. Let's look at it using an example.

    Task: There is a table with columns First Name, Middle Name and Last Name. It is necessary to make a single array of these values. In fact, you need to combine all three cells into one - full name.

    All formulas begin with the sign = . Next, enter the name of the function itself CONNECT. When entering a name Excel functions gives a hint that you can use. A description of its purpose appears next to the name.

    After entering the function name, enter an opening parenthesis (. In the tooltip you can now see a description of the syntax - what needs to be entered for this formula.

    The next step is to click on the cell that will be linked first. In our case, this is a cell in a column with a name.

    After this, according to the syntax, we put a semicolon ; - this character serves as a separator. Repeat the previous step for the columns with middle name and last name. Finally, close the function using a closing parenthesis ) .

    After pressing the key Enter you will get the clutch result.

    This result is not entirely readable because there are no spaces between the values. Now we will add it. To add spaces, we'll change the formula a little. A space is a type of text and in order to enter it into a formula, you must enter quotation marks and insert a space between them. The whole structure will look like this " " . All that's left to do is connect everything with a sign ; .

    You can enter additional characters in exactly the same way. For example, put a comma or a period. The design in this case will be like this "." . All that remains is to extend the function to all rows (I’ll tell you about this at the end of the article), and since this cell contains a formula, not values, you need to copy the values ​​to new column via the Paste Special function.

    Combining text in cells using an ampersand

    Now let's look at another way in which you can combine text values from different cells in one. In this case we will use the special ampersand character & - graphic abbreviation of the Latin union et (and).

    Task: There is a table with employees. Columns First Name, Middle Name and Last Name. Now they are separate. It is necessary to combine all the values ​​in the row into one Full Name cell.

    We will use formulas. We stand on the cell in the column with the full name. Next, enter the beginning of the formula - the sign = . After that, click on the first value in the line that we want to add. In our case, this is the value in the column Name.

    In order to add the value of the patronymic to the value of the name, we will use the sign & (in English layout Shift+7). We put it after the value A2 and now click on the cell in the column Surname.

    Similarly, add the value from the column Surname.

    After clicking on Enter the text data will be concatenated, but there will be no space between the values.

    We will add a space by introducing an additional ampersand and double quotes. In this case, the space construction looks like this: " " - a single space is placed between the quotes. Click on the cell in the Full Name column twice with the left mouse button to correct the formula, and add a space to the formula using the & sign.

    After pressing the key Enter, you will get the desired result.

    Now we simply extend the formula to the remaining cells in the column. To do this, click once on the cell with the formula and drag it, holding down the left mouse button, by the lower right corner down to the last cell.

    If you only need values ​​without formulas, then copy and paste them side by side using Special insert.

    That's all. If you have any questions or know other ways to combine, be sure to write in the comments below.

    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 a single general data header that has different meanings. You can see an example of such information display in the image below. Read on to learn how to merge cells in Excel step by step. 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 that you want to merge and right-click on the selected area. 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:

    1. 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.
    2. 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.
    3. Merge cells - this item works exactly the same as in the first option through the format.
    4. 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 left them in line 4 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.

    When creating tables, situations often arise when the user wonders how to combine cells into one in Excel. This happens at moments when it creates large text tables. For example, when creating a cross-tab, when you need to write some text corresponding to several subheadings.

    Getting Started

    Let's bring concrete example. The problem is that in Excel it is not possible to split base cells, so if you need to make cell A2 look like it's split into 3 parts, you can use merging cells A1-C1. After combining them, you will have a table with a heading in A1-C1 and three cells under it A2-C2. When creating tables, remember that their structure must be thought out in advance, so that later you do not have to redo everything from scratch. Merging cells in Excel is done in several ways.

    Creating a table

    So, let's start directly formatting the cells. Let's look at how to merge cells in Excel. There are three ways.

    1. The fastest and easiest option. On the control panel, above open page with a document, there is a button "Merge and Place in Center". After clicking on it, the cells you have selected will be combined into one, and any data you enter into it will be displayed exactly in the center.
    2. The second and third methods can be combined into one, since in them everything is solved through the same function.

    Formatting Features

    There are several rules by which cells are merged.

    • Cells can be merged row by column or by sector.
    • If they contain data, then the resulting cell contains data exclusively from the upper left “cell”. When formatting multiple cells with Excel information will issue a notification about data destruction.
    • You can merge cells in several places in the table at once without merging all sections. The merged cells will remain in their places.

    What if you often have to work in Excel? How to merge cells without losing data? For such situations, a special add-in module was created - Office menu. It is built into your office program. An additional element appears on the control panel for the user.

    To merge cells in a data range, simply click the “Merge Without Loss” button; as a result, you will get a common cell with data located in the same way as in the original version, except that the original values ​​will be highlighted with special separators.

    If you need to combine everything into one line, click "Combine Lines". All data in the specified range will be split into lines and separated by special characters.

    Similarly, you can split a range into columns by clicking "Merge Columns".

    Reversible process

    Having figured out how to merge cells in Excel, it is worth mentioning that any actions in Microsoft Office Excel can be canceled either by clicking the button in the “Cancel” ribbon (cancels last action), or by performing the same actions that were described in the “Creating a table” paragraph.

    Read more: