Merge rows, columns and cells in Excel. Merge words from different cells into Excel text

Instructions

To unite cells in Excel without losses data contained in them, use the ampersand operator - the symbol &, denoting the conjunction “and” in the language. Place the cursor in the cell in which the data will be combined, put an equal sign and an opening parenthesis in the formula bar.

Select the first cell using the left mouse button and enclose a space in quotation marks between the ampersands - &" "&, select the next cell and enter &" "& again. Continue until you have marked everything this way. cells that need to be combined. To finish entering the formula, enter a closing parenthesis and press Enter. The formula will look something like this: =(A1&" "&B1&" "&C1).

If you need to separate the combined data with punctuation, place it after the first ampersand and quotation mark, and be sure to add a space after entering the punctuation mark. Example formula for combined data using punctuation: =(A1&"; "&B1&"; "&C1).

You can also use the “Connect” function. To do this, place the cursor in a cell and click the fx icon in the formula bar. A new Function Wizard dialog box will open. Select the CONCATENATE function from the list or search for it using the search field. In the “Function Arguments” window, place the cursor in the “Text1” field and select the first cell of the range to be merged with the left mouse button. Move the cursor to the "Text2" field and select the next cell in the document. Click on the OK button.

When using this method, do not select the entire range of cells to be merged at once; this will lead to data being lost. The formula should not look like =CONCATENATE(A1:B1). Separate cell addresses with ";" - semicolon, then all values ​​will be saved. Example formula: =CONCATENATE(A1; B1; C1).

Sources:

• How to merge cells in Excel 2007/2010/2013/2016?

An association cells– one of the functions used when creating a table. It means creating one cell from two or more selected cells. This function is used in both Word and Excel.

Instructions

You created in a Microsoft Word document, and while entering data you needed to combine two or more into one. When creating a table, the “Design” and “Layout” tabs appear in the top menu. Hold down the left mouse button and select the desired cells. Go to the “Layout” tab, then to the third group from the left “Merge”.

If you need to cancel the merge cells, click on the “Undo input” button in the upper left corner, or the keyboard shortcut Ctrl + Z.

In a Microsoft Excel worksheet, a union cells happens differently. If there is data in multiple cells being merged, only the data in the top left or top right (depending on the current viewing direction) will be retained, and the remaining data will be deleted.

Select the cells you want to merge. Right-click on the selected sector. Select Format cells" Go to the Alignment tab. In the “Display” field, check the “Merge” box. cells».

In this case, Excel will issue a warning: “The selected area contains several data values. An association cells will cause all values ​​except the top left to be lost."

You can edit the join. On the Alignment tab, click the arrow next to the "a" button. In addition to merging, cells here are offered to “Merge and place in the center”, “Merge by rows”. Bottom button “Cancel merge” cells" will cancel the merge, but will not return the data that was in the individual cells.

To cancel the merge and return data, click the “Undo Entry” button, or the keyboard shortcut Ctrl + Z.

Sooner or later, a personal computer user has to deal with software such as Excel. These are designed spreadsheets that allow you to create various databases.

Instructions

To learn how to work with this program, you first need to decide on the tasks that should be performed for you. As a rule, such software has flexible built-in functions that allow you to format and classify all information in different areas. There are many different courses on the Internet that allow you to see in real time how tables are built, information is integrated, data is sorted, and much more.

As a rule, the first teaching begins with familiarization. You must clearly know where each column is located. At this point in time, there are several versions of this software, namely: Excel 2003, 2007 and 2010. Probably, many are accustomed to version 2003. Despite the fact that now updates are being created only for 2007 and 2010, Excel 2003 software does not stop popular.

Open the program on your computer. Go to the “Help” tab and read those items that interest you. Essentially, the software is similar in functionality to Word. There are the same formatting commands, the menu items are largely similar, and the interface is also clear. To add a new table, click "File" - "New". Next, enter the name of the document in which you will work.

Next, enter the information into the cells of the table. You can stretch each block, both in width and in length. To add more cells, right-click on the table and select “Add Cells”. In the "Service" tab you can find additional operations that this software provides. Start by simply copying information into a table, and then try to display it in the table in different ways, sort it, try to present it in a more convenient form.

Video on the topic

The Microsoft Office Excel spreadsheet editor is designed to work with relatively small data sets that do not necessarily contain only numerical values. Words, phrases, and even text fragments can be placed in spreadsheet cells. To process data of this kind, as for numeric cells, formulas are also used. In particular, the table editor has functions for combining (summing) several text cells.

You will need

• Microsoft Office Excel spreadsheet editor.

Instructions

Launch Excel and load the desired table into it. Select the cell in which you want to place the combined text.

Go to the “Insert” tab in the program menu and expand the drop-down list labeled “Text” - it is placed in the “Function Library” command group. The list contains more than dozens of operations for working with texts ed variables, sorted alphabetically - scroll to rows starting with the letter "C" and select the "CONCATENATE" function. As a result, the Function Creation Wizard dialog box will launch, in which you will need to fill out several form fields.

Specify the address of the cell containing the text that should begin the row in the table cell being created. You must either enter it manually in the “Text1” field, or click the desired table cell with the mouse cursor. Then repeat the same action for the “Text2” field - here you need to place the address of the cell with the text that should be added to the previous one. When you proceed to fill out the second field, Excel will add a third one just in case - you can use it if you need to construct a value from three or more original ones cells. A new line will be added continuously as the previous ones are filled.

Please note that the function will concatenate texts ed values ​​in their original form, without adding anything as a separator. If you need to insert a space, a comma or any word between them, then use a line in the form of the formula creation wizard to do this - enter the necessary characters into it, placing them in quotation marks. For example, if in the “Text1” field you enter a link to the first cell, in the “Text2” field you enter “, ”, and in “Text3” a link to the second cell, then a space will be placed between the inscriptions.

Click OK and a value will appear in the cell containing the combined text of the values ​​you specified. cells.

The Function Design Wizard can be called without going to the “Formulas” tab. To do this, just click the insert formula icon located above the table to the left of the formula line. Then select the “Text” line in the “Category” drop-down list and look for “ ” in the “Select a function” field. By clicking OK you will open a window with the form described above.

Video on the topic

Cell protection in the Microsoft Office Excel spreadsheet editor is not used to maintain secrecy - data file protection is intended for these purposes. Locking access to cells is needed, rather, to prevent accidental changes to formulas or data in spreadsheets. The procedure for enabling protection is simple, but requires an understanding of the operating principle.

You will need

• Spreadsheet editor Microsoft Office Excel 2007 or 2010.

Instructions

The protection is organized in such a way that it is installed on the document sheet as a whole and applies to all its contents. Therefore, before you begin setting up access blocking, you should set exceptions to the general ban. Of course, this should only be done if you need to leave free access to some table cells. Start the procedure by selecting the entire sheet - press the key combination Ctrl + A.

Right-click the selection and select “Format Cells” from the pop-up context menu. A window will appear on the screen in which you need the “Protection” tab - check the “Protected cell” checkbox located on it. If you want blocked ones not only to be unable to be edited, but also to be unable to view the formulas they contain, check the “Hide formulas” box. Click OK and the sheet is ready to be protected.

If you do not need to leave any elements freely accessible, skip this step, otherwise select those cells, which must be excluded from the list of protected ones. Call the properties window again through the context menu and do the opposite operation - uncheck the checkbox next to “Protected cell”. Click OK.

In the MS Excel office package, you can combine several fields adjacent horizontally or vertically 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 An association and press OK.

Should be considered 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 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 code options for such a macro are 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 Macro program code and execute.

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, then 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:

Then 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 does not 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).

Working in the Excel spreadsheet editor is a necessary skill for every modern office worker. Microsoft Excel has very wide capabilities, and, having the proper set of knowledge about working in this program, you can more effectively solve certain problems. In this article, we will look at how to merge cells, rows or columns in Excel. Unfortunately, this is a problem for most users. But don't be alarmed. This article will answer all your questions. Let's get started. Go!

Users can give the table the desired look at any time

In general, data grouping is a rather difficult topic if you do not fully understand it. Merging table blocks is done using the button<-a->, which is located in the “Alignment” block in the tool ribbon. Please note that when merging, only the left and first values ​​will remain from the data, the rest will be deleted. Also, if some rows are combined, it will not be possible to filter by them, and the autofilter will not work.

Now select the areas that you want to combine into one, you can do this by holding down the Ctrl key. Next, click on the button<-a->in the toolbar. By clicking on the arrow in this button, you will open a list of merger options:

• Place in the center;
• By lines;
• Cells;
• Cancel.

After you select the desired option, the areas will be combined according to the specified parameters.

You can do without the main toolbar. After selecting the desired area, right-click on it and select “Format Cells”. Next, go to the “Alignment” tab and check the “Merge Cells” checkbox.

Unfortunately, you cannot merge rows and columns without losing data using standard Excel. Therefore, there are settings for this table editor that allow you to expand the functionality of the program. After downloading and installing the add-in, you will have a wide range of options for combining rows and columns.

There is a more advanced way to group multiple table values ​​into one area. You can use formulas for this. If you have blocks with text or numbers, and you want to group it all, then write the following in the formula field without quotes: “=CONCATENATE (row and column number of each cell separated by a semicolon).” For example: “=CONCATENATE (B2;C2;D2;E2).” In this case, the values ​​will be grouped in a row. If between them you want

To combine two or more cells into one, use the colspan and rowspan attributes of the tag . The colspan attribute sets the number of cells to be merged horizontally. The rowspan attribute works similarly, with the only difference being that it spans cells vertically. Before adding attributes, check the number of cells in each row to ensure there are no errors. So, replaces three cells, so the next line should have three tags or design like ...... . If the number of cells in each row does not match, empty phantom cells will appear. Example 12.3 shows, although valid, but incorrect code, in which a similar error appears.

Example 12.3. Incorrect cell merging

The result of this example is shown in Fig. 12.5.

Rice. 12.5. Appearance of an additional cell in the table

The first line of the example specifies three cells, two of them are combined using the colspan attribute, but the second line adds only two cells. Because of this, an additional cell appears that is displayed in the browser. It is clearly visible in Fig. 12.5.

The correct use of the colspan and rowspan attributes is demonstrated in Example 12.4.

Example 12.4. Merging cells vertically and horizontally

The result of this example is shown in Fig. 12.6.

Rice. 12.6. Table with merged cells

This table has eight columns and three rows. Some of the cells with the inscriptions “Internet Explorer”, “Opera” and “Firefox” are combined in some places with two and in others with three cells. The cell labeled "Browser" has a vertical merge applied.

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