How to Merge Cells, Columns and Rows in Excel

Merge Excel Featured

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.

Merging Cells

You will find the built-in merge options under the Home tab of Microsoft Excel.

Merge Option Excel 1

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.

Merge Excel Selection 1

If you choose “Merge & Center,” the result will be centered in the area you selected.

Merge Excel Center 1

On the other hand, if you choose “Merge Across,” the text in the left cells of the rows that were selected will be kept.

Merge Excel Across 1

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.

Merge Excel Merge Cells 1

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:

Merge Excel Rows 1
=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

Merge Excel 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:

Merge Excel Concatenate No Spaces 1

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)
Merge Excel Concatenate Spaces

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.

William Elcock
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.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox