When working with data in Excel, it is common to join cells, columns, and rows together to combine numbers or text.
There are several reasons why you may need to merge information in Excel. Sometimes it’s to improve the formatting. Other times it is to bring information from multiple cells to a single cell.
Whatever the reason, you can use the following methods to combine information in Excel any way you want.
Merge cells in Excel
One of the most common reasons for merging multiple cells is when you create a title row in your Excel spreadsheet.
- You can create the title text in any of the cells you want to merge. Format the text however you like, including font size, style, and vertical alignment or height.
- Next, you need to make sure that the text is perfectly aligned across the width of your data table. To do this, select all the cells you want to merge and center.
- With all the cells you want to merge selected, click the House select menu Merge and Center† Combines all selected cells into a single cell and centers the text or data in that cell.
You can also do the same vertically for cells. For example, if you want to have the month name centered vertically on the right side of all rows for that month, you can do it with Merge Cells.
To do this:
- Type the name of the month in the first cell.
- Then select all relevant cells. So in this case, January would contain the first and second cells.
- Now, with those cells selected, in the House menu select the drop-down arrow next to Merge and Center† Select Merge cells from the drop-down menu.
This will merge those cells into one in Excel 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 probably prefer to center the text vertically so that it is in the center of all cells it applies to. To do this, just select the newly merged cell and select the vertical alignment icon from the main menu in the Alignment ribbon group.
This aligns the text vertically with all relevant cells so that everything lines up perfectly.
Merge Columns in Excel
In Excel, you are not limited to merging individual cells. You can also merge entire columns of data.
For example, in this sample spreadsheet, you want to combine the sales associate’s first name and last name in another column for their full name.
In this spreadsheet, if you select all the data in the two columns and try to merge the cells with Merge & Center, you will see an error message that you will lose the data in all cells except the first cell in the range.
This is not helpful at all.
There are some quick ways that you can combine all the data from two columns without losing anything.
Merge Columns with Notepad
An easy way to combine data from the two columns into one is to copy all the data from the two columns to Notepad. Notepad’s find and replace feature is an effective way to quickly format the two data into one.
- Copy all the cells from the two columns you want to merge and paste them into Notepad. Scroll to the bottom of the list and press tab on your keyboard. Highlight that tab space and copy it with Ctrl-C. You will need this later. Then highlight all text.
- Select the edit menu and select To replace†
- In the Find what field, press Ctrl-V to paste the Tab character. In the Is replaced by field, press the spacebar. Replaces all tabs in the document with one space.
- Select now replace everythingand all text in the Notepad document should look normal, with the first name and last name separated by just a single space.
- Highlight all text in the document and press Ctrl-C to copy. Go back to your sheet and paste with Ctrl-V at the top of the first column you originally copied from.
- Finally, just rename the first column and delete the second. Now your spreadsheet has merged the two columns into one.
This isn’t the most advanced way to merge columns in Excel, but it works and it’s easy.
Merge Columns in Excel with Concatenate
If you want to get a little more advanced and save a few steps in the process, you can use the CONCATENATE function in Excel. This feature works just like the Merge feature in Google Sheets.
This function combines text from multiple cells into one. You could even use it to merge as many columns as you want, not just two.
- To do this, right click on the column to the right of the two you want to merge and select Insert† Inserts a new, empty column.
- In the first empty cell at the top, type this new empty column = CONCATENATE(C3,” “,D3)† Replace C3 and D3 with the cells that contain your data. The ” ” in the middle adds a space between the text from the two cells. When you press enter, you will see the data of the two cells put together with a space between them.
- Finally, to copy this function through the entire column and merge data from both columns, hold Shift 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 the mouse.
This is an autofill function in Excel. It fills the formula at the bottom of that column as far as there is data in the column to the left. Now you can see that in the whole column, data from the first two columns has been merged.
However, if you want to delete the first two columns, you have to copy and paste the whole new column again as values only.
Once you’ve done this, you can delete the two old columns and your spreadsheet is now done, with the two columns merged into one.
How to merge rows in Excel
There is no quick and easy trick or function to merge rows in Excel. You must use the same CONCATENATE function as you do for column concatenation.
However, the technique is a bit different. It’s not very common for people to combine data from different rows into a new row, but the need can occasionally arise.
Merge rows with merge
For example, in the sample spreadsheet we used, what if you wanted to combine all team member names from a single month into a new row on a different page? To do this, you need to concatenate and separate the names with a character such as a comma.
- Place the cursor where you want to place the data from multiple rows. For example, you can place the cursor in a new sheet. Then type = CONCATENATE(Sheet1!C3,”,”,Sheet1!C4)† Replace C3 and C4 by selecting the actual cells in the other sheet that you want to merge.
- When you press Enter, you will see the concatenated data in the new row, separated by commas. To merge more rows, copy and paste the first cell into the second new row and edit the formula to include the rows of the original sheet you want to merge.
You can continue this process until you have merged all the rows you want from the original sheet. Remember that you can freeze the top row in Excel if you want so that you can still see the header as you work on the data.
Merge rows in Excel using Merge and Center
Of course, Merge and Center works for multiple rows containing the same data. For example, in this spreadsheet, both February items come from the “Central” region. Instead of repeating this twice, you can merge these two rows into one.
- Highlight both rows you want to merge.
- From the main menu, select Merge & Center on the Ribbon.
As you can see, this combines the two rows of the same data into a single row containing one of those duplicates.
This is a smart way to clean up your spreadsheets and reduce duplicates in the dataset.
These are some of the quickest tips to merge cells, columns, and rows in Excel. Do you know others? Share them in the comments below!