How to merge cells in excel


Introduction :

Very often while working with Excel we come across situations where we need to enter values in more than one cells at once e.g. a heading that will span over two or more columns of data, displaying a long text without increasing column width, a value that is common to two or more cells etc. For such scenarios Excel has given us a very useful feature of merging cells. Once we know how to merge cells in excel we can use this feature to create cells that are longer than normal cells and span over two or more columns (or if we merge vertically we get cells that span over two or more rows).

Method 1: Merge cells in Excel Using The “Merge And Center” on Home Tab

Step 1 :

Select All the Cells that you want to Merge.

 

How to merge cells in excel

 

Step 2 :

Go To the home tab

 

Step 3 :

Locate and Click the “Merge and Center” option.

 

How to merge cells in excel

 

Pros of Using this Method :

 

  • Very Sweet and simple method.
  • Merges the cells with just a few clicks.

 

Cons of using this method :

 

  • This method works fine with empty cells but the problem arises when we try to merge cells that already contains some data. In this case Excel merges the cells but does not combine the text within the merged cells, it will keep only the Upper left value and will discard the rest of the values.  

 

How to merge cells in excel

 

Note  # : Before discarding the values of the cells – Excel displays a warning to the user that data will be lost if the user will proceed with “Merge and Center”. Once the user clicks “OK” then only Excel Merges the cells by discarding the values.

 

How to merge cells in excel

 

 

 

 

 

  • Any data set that contains cells that were merged using this method loses the ability to get sorted.

 

 

Method 2 : Merge Cells in Excel Using the Center Across Selection

 

Step 1 :

Select the cells to be merged.

 

How to merge cells in excel

 

Step 2 :

Right click on the selection and click on Format Cells from the menu that appears.

 

How to merge cells in excel

 

A dialogue box will appear titled “Format Cells”.  Alternatively you can Press Ctrl + 1 to open the Dialogue box directly.

 

How to merge cells in excel

 

Step 3 :

Go to the “Alignment” tab of the dialogue box.

 

How to merge cells in excel

 

 

Step 4 :

Go to the Horizontal Dropdown and click on the down arrow.

 

How to merge cells in excel

 

Step 5 :

Click on “Center Across Selection” Option.

 

How to merge cells in excel

 

Step 6 :

Click “OK”.

 

How to merge cells in excel

 

Pros of Using this Method :

 

  • This Method overcomes the Sorting problem of Method 1 - Cells Merged in this manner can be sorted without error.
  • Also we can select each cell individually even though they are merged.

 

Cons of using this method :

 

  • This is a great method of merging cells but one restriction still remains. It works well if only the leftmost cell contains data and all the other cells are empty.

 

How to merge cells in excel

 

Method 3 : Combining Cells in Excel :

We learned just now that the methods explained above are good for empty cells but not for merging non empty cells as these methods do not combine the values within the cells. If instead of merging the cells we want to combine the values within the cells then we have a detailed tutorial on how to Combine cells in Excel and also the values of those cells.

 

Conclusion :

In the above tutorial we learned how to merge two or more cells to create a single cell that can contain longer values or can be used to span over two or more columns or rows. We have seen that these methods are good enough for empty cells but lose data if we merge non-empty cells. We have also seen how to combine cells in Excel if we want to combine the values within the cells. This technique can further be used for useful purposes like - combining first and last name in excel and combining line breaks in excel etc.