Three ways to combine columns in Excel

A user working in a popular spreadsheet editor periodically faces the problem of combining columns in Excel. For some, this is the simplest task that can be dealt with in one click, while others simply don’t know how to do it. In any case, this article will be useful to everyone, since it presents three ways to combine two columns into one in Excel.

Combination Methods

All methods of how to combine columns in Excel can be conditionally divided into two categories, which differ in execution principle. Some involve the use of formatting tools, while others use the program features.

If we talk about the simplicity of the task, then the undisputed leader is the first category. But, unfortunately, not always, using the formatting settings, you can achieve the desired result. That is why it is recommended to read the article to the end in order to determine for itself the most suitable method for completing the task. Well, now let's move directly to how to combine data in columns in Excel.

Method 1: through the context menu

Most often, when working in any program, the user is used to performing any actions using the context menu. So, to combine data in columns in Excel, you can use the options from it. Let's take a closer look:

1. Using the left mouse button, select the row of cells in the row whose columns you want to combine.
2. Right click on the selection.
3. In the context menu that appears, select the option "Format cells".
4. A window of the same name will appear. In it, go to the “Alignment” tab.
5. Pay attention to the “Display” parameter group. In it, you need to check the box next to the "merge cells" line.
6. Click OK to apply all selected options.

The window will close and you will notice that the cells selected before this merge into one. But we are faced with the task of combining columns, not individual cells. Therefore, we proceed directly to the second stage of this method:

1. Click on the “Home” tab.
2. Select the cell that we merged.
3. On the toolbar, click the "Format by sample" button. It has a paint brush icon and is located in the clipboard tool group.
4. Select the rest of the columns you want to merge.
5. After that, click on the “Format by sample” button again.

As soon as you do this, all selected columns will become a single whole. This was the first way how you can combine columns in Excel, however it is not without flaws. The fact is that the data that is in the combined cells is partially affected. To be more precise, only those that were in the first column will remain. If this does not suit you, then the last method will tell you how to avoid this.

Method 2: using the tool on the panel

The second method, how to combine columns in Excel, is an order of magnitude faster, since the steps need to be done much less:

1. Select the columns you want to combine. Here it is worth clarifying that it is necessary to select the columns directly, not the cells. To do this, position the cursor on the letter of the column, hold down the left mouse button and drag it horizontally to the next letter.
2. Click on the “Home” tab.
3. In the “Alignment” tool group, click on the drop-down list next to the “Merge and center” button.
4. From the menu that appears, select “Merge by Rows”.

Immediately after this, the columns will be merged and the rows will remain untouched. As you can see, actions are performed, as already mentioned, an order of magnitude faster, but the disadvantage of partial data loss still remains. If this is unacceptable to you, then go on to the third method, which eliminates this.

Method 3: using a special function

We will now explain how to combine columns in Excel without losing data. And this is done using the "Link" function:

1. Select any empty cell on the sheet in the program.
2. Click on the “Insert Function” button. It is located to the left of the line of formulas.
3. The “Function Wizard” window will appear. In it you need to select "Link" from the list. After that click “OK”.
4. Now you need to enter the function arguments. In front of you you see three fields: “Text1”, “Text2” and “Text3” and so on.
5. In the Text1 field, enter the name of the first cell.
6. In the second field, enter the name of the second cell next to it.
7. If you wish, you can continue entering cells if you want to combine more than two.
8. Click OK.

In the cell where the function was inserted, the text of the two previously indicated appeared. Most likely, the result will not satisfy you, since the cells are combined, but in a completely different place and only one row from the column is involved. All this can be fixed:

1. Highlight the merged data.
2. Place the cursor in the lower right corner of the cell.
3. Hold LMB and pull down.
4. All other lines are also combined.
5. Highlight the results.
6. Copy it.
7. Select the part of the table that you want to replace.
8. Paste the received data.

This method is quite time-consuming, but it makes it possible to combine columns without losing data.