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.
Also read: How to Start Writing VBA Macro in Microsoft Excel
Merging Cells
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.
- Unmerge
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.
Merging Rows
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.
Merge Columns
The 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:
=CONCATENATE(A2,A2,A3,A4,A5,A6)
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.
Wrapping Up
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.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe
William Elcock
William has been fiddling with tech for as long as he remembers. This naturally transitioned into helping friends with their tech problems and then into tech blogging.
Comments are closed