How To Merge Cells, Columns & Rows In Excel

When working with data in Excel, it is common to combine cells, columns, and rows to combine numbers or text.

There are various reasons why you might want to combine information in Excel. Sometimes formatting needs to be improved. In other cases, it is necessary to transfer information from several cells to one.

Whatever the reason, you can use the following methods to combine information in Excel in any way you like.

How to merge cells in Excel

One of the most common reasons to combine multiple cells is when you are creating a title row in your Excel spreadsheet.

1. You can create heading text inside any of the cells you want. combine. Format the text however you want, including font size, style, vertical alignment, or height.

2. Then you need to make sure that the text is aligned to the width of the data table. To do this, select all the cells you want to merge and center them.

3. With all the cells you want to merge selected, from the main menu, choose Merge and Center. This will combine all the selected cells into one and center the text or data in that cell.

You can do the same for vertical cells. For example, if you want the month name to be centered vertically on the right side of all rows for that month, you can do so using the Merge Cells function.

To do this:

– /

1. Enter the name of the month in the first cell.

2. Then select all relevant cells. So in this case, January will include the first and second cells.

3. Now that these cells are selected, on the main menu, click the drop-down arrow next to the Merge and Center button. Select Merge Cells from the drop-down menu.

This will combine these cells in Excel into one and place the text at the bottom of the cell by default. The reason you don’t want to use Merge & Center in this case is because it will center the text horizontally in the column.

In this case, you would probably prefer to center the text vertically so that it sits in the center of all the cells to which it is applied. To do this, simply select the cell you just merged and click the vertical center alignment icon on the main menu in the Alignment group of the ribbon.

This will align the text vertically with all the corresponding cells so that everything is perfectly aligned.

How to merge columns in Excel

In Excel, you are not limited to merging individual cells. You can also concatenate entire columns of data.

For example, in this sample spreadsheet, you can combine the salesperson’s first and last name into another column for their full name.

In this spreadsheet, if you select all the data in two columns and try to merge cells using the Merge and Center feature, you will see an error message that you will lose data in all cells except the first cell in the range.

It is generally useless.

There are some quick ways to combine all data from two columns without losing anything.

Concatenate columns using Notepad

One easy way to combine data from two columns into one is to copy all data from two columns into notepad. The find and replace feature in Notepad is an efficient way to quickly format two pieces of data into one.

1. Copy all the cells from the two columns that you want to combine and paste them into Notepad. Go to the bottom of the list and press Tab on your keyboard. Select this tab stop and copy it using Ctrl-C. You will need this later. Then select all the text.

2. Open the Edit menu and choose Replace.

3. In the Find box, press Ctrl-V to insert a tab character. In the Replace with field, press the spacebar. This will replace all tabs in the document with a single space.

4. Now select Replace All and all text in the Notepad document should look fine, with the first and last name separated by a single space.

ol>



5. Select all text in your document and press Ctrl-C to copy. Go back to your sheet and paste using Ctrl-V at the top of the first column you originally copied from.



6. Finally, just rename the first column and remove the second. Now your table has two columns merged into one.





This is not the most sophisticated way to combine columns in Excel, but it works and is simple.

Concatenate columns in Excel using concatenation

If you want to get a little more complex and save a few steps in the process, you can use the CONCATENATE function in Excel. This function works the same as the Concatenate function in Google Sheets

This function combines text from multiple cells into one. You can even use it to combine any number of columns, not just two.

1. To do this, right-click the column to the right of the two you want to combine and choose Insert. A new empty column will be inserted.
2. In the first blank cell at the top of this new blank column, enter = CONCATENATE (C3, ”“, D3). Replace C3 and D3 with cells containing your data. The “” character in the middle adds a space between the two-cell text. By pressing Enter, you will see the data from two cells along with a space between them.



3. Finally, to copy this function down the entire column and merge data from both columns, hold down the Shift key on your keyboard and hover your mouse over the lower right corner of the first cell until the mouse icon changes to two horizontal lines. Then double click with the left mouse button.





This is the auto-fill feature in Excel. It will fill the formula to the end of that column as far as the data will be in the column to the left. Now you can see that the entire column contains data from the first two columns concatenated together.

However, to remove the first two columns, you will need to copy the entire new column and paste it as values ??only.



Once you’ve done that, you can delete the two old columns and your spreadsheet is complete and the two columns are merged into one.



How to merge rows in Excel

There is no quick and easy trick or function to combine rows in Excel. You will need to use the same CONCATENATE function as for concatenating columns.

However, the technique is slightly different. Combining data from different rows into a new row is infrequent, but sometimes it can be necessary.

Concatenating strings using concatenation

For example, in the example table we used, what if you wanted to concatenate all team member names from one month into a new row on another page? To do this, you need to concatenate the names and separate them using a character like a comma.

1. Place the cursor where you want to place multiple lines of data. For example, you can place your cursor on a new sheet. Then enter = CONCATENATE (Sheet1! C3, ”,”, Sheet1! C4). Replace C3 and C4 by selecting the actual cells on the other sheet that you want to merge.
2. When you press Enter, the concatenated data appears on a new line, separated by commas. To combine more rows, simply copy and paste the first cell into the second new row and edit the formula to include the rows from the original sheet that you want to combine.





You can continue this process until you have concatenated all the rows of the original sheet that you want. Remember, you can freeze the top row in Excel if you need to so that you can still see the header while working with the data.

Concatenate rows in Excel using the merge and center function

Of course, Merge & Center works for multiple lines containing the same data. For example, in this table, both records for February are from the “Central” region. Instead of repeating this twice, you can combine these two lines into one.

1. Select both lines you want to concatenate.
2. From the main menu, select Merge & Center on the ribbon.





As you can see, this concatenates two lines containing the same data into one line containing one of those duplicates.

This is a great way to clean up tables and reduce the number of duplicates in your dataset.

These are some of the quickest tips for merging cells, columns and rows in Excel. Do you know others? Share them in the comment section below!

–

Share this: