Depending how you want to format a body of text in Microsoft Excel, you will sometimes have to merge cells. Whatever your reason is, there are a few different techniques you can use. These can help you to achieve the merging of cells, rows, and columns. Some methods will get rid of some of the data in cells, so you have to decide how you want the final result to look beforehand.
You will find the built-in merge options under the Home tab of Microsoft Excel.
The merge options available are:
- Merge & Center: This option merges cells into one and centers the text. However, only the text from the leftmost cell is kept.
- Merge Across: This option merges cells across from each other into one. All of the rows in a selection chosen to be merged are separated. However, only the text in the leftmost cell of each row is kept.
- Merge Cells: This merges all of the cells in a selection into one. The text is not centered, and only the text from the leftmost cell is kept.
Consider a case where you made a selection that made up six rows and two columns.
If you choose “Merge & Center,” the result will be centered in the area you selected.
On the other hand, if you choose “Merge Across,” the text in the left cells of the rows that were selected will be kept.
With the “Merge Cells” option, the text in the leftmost cell of the selection is kept and the cells are all fused. The text is not centered in this case.
If you want to merge rows, then the
CONCATENATE function is your best bet. If you wanted to merge a series of rows together into one row where the text is separated by commas, you can use the following formula in a blank cell:
=CONCATENATE(A2,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6...)
The extra commas and quotation marks are included so that there is a space after the comma that comes after each word in the list.
CONCATENATE function is your friend again when it comes to merging columns. In order to merge a group of columns together, you have to pick out the relevant cells in the columns you want to fuse and include them in your
CONCATENATE formula. For example, if you had column H and column I with three items in each column, the following formula could be used to merge items together:
=CONCATENATE(H1,", ",H2,", ",H3,", ",I1,", ",I2,", ",I3)
An Aside About the Concatenate Function
If you want to have your merged content without spaces, then use the concatenate function like this:
On the other hand, if you want to have your merged content with spaces, you can use the concatenate function like this:
=CONCATENATE(A2," ",A2," ",A3," ",A4," ",A5," ",A6)
This applies to the merging of both rows and columns.
Depending on what you’re doing, you’ll need to format your data differently. This makes merging cells, rows, and columns an invaluable tool. We have covered different techniques here. Feel free to get creative about how you use them. We’ve covered the basics here, but you can do different things like changing up the spacing of your merged content to your preferences. We can also show you how to make your Excel workbook read-only.
Our latest tutorials delivered straight to your inbox