How to Move Merged Cells in Excel (3 Suitable Ways)

In Excel, we need to merge cells for many reasons, starting from better clarity of data may be due to space constraints. And the same time might require moving those merged cells to different locations in Excel. And doing those complicates things as we deal with different cell sizes sometimes creates problems in Excel. In this article, we discuss how we can move merged cells in Excel without any hassle.

How to Move Merged Cells in Excel: 3 Easy Ways

For demonstration purposes, we are going to use the below dataset. In this dataset, cells that are in merge conditions of different sizes are shown. And they move to a new location. How we move the original dataset to a new location is disclosed in this article elaborately.

1. Using Paste Special Command to Move Merged Cells

Using the Paste Special command, one can move merged cells of any size from one location to another location.

Steps

• In the image, the range of cells B5:E9 now moves to the empty range of cell G5:J9.

• For this, select the range of cells B5:E9 and right-click on your mouse. A context menu will open, from the context menu click on Copy.

• After that, select cell G5, and right-click on the mouse again, from the context menu, go to the Paste Special command, then click on the Keep Source Column Widths(W) from the Paste Special command.

• After clicking the Keep Source Column Widths(W), you will notice, that the merged range of cells B5:E9 is now moved to the range of cells G5:J9.

For better clarity, the main dataset is copied to the original place from where a range of cells was moved.

2. Move Merged Cells by Changing Text Alignment

This process unmerges the cells in Excel and moves them to another location in Excel while keeping the impression of being merged.

Steps

• We need to move the range of cells B5:E9 to the empty range of cells G5:J9. But we first need to know why traditional moving cells won’t work.
• To demonstrate this, first select the range of cells B5:E9 and try to move it by dragging the selection border around the range of cells B5:E9.

• Then you will notice that there will be a warning window stating that the movement of merged cells is not possible. Click OK on that window.

• The reason behind this is that to move cells, the source and destination cell size has to be equal. Any merged cell is out of this rule, that’s why you will see the warning message if you want to do something like this.
• To resolve this issue, you need to select the merged cell B5:E5 and right-click on your mouse. A context menu will appear.
• From the context menu, click on the ‘Merge & Center’ icon shown in the image.
• It can also be done by the Merge and Center command in the Alignment group in the Home tab.

• After unmerging the cells, you will notice that the text is now only on the B5 cell, which was previously in cell B5:E5.

• Select the range of cells B5:E5 again and right-click on your mouse.
• From the context menu select Format Cells.

• A new window will open and from that window, go to the Alignment tab.
• Then from the Text alignment group, select Center Across Selection from the Horizontal options.
• Click OK after this.

• Repeat the same process for the rest of the merged cells. Click on OK.
• Then again select the range of cells B5:E9, right-click on your mouse, and click Cut.

• Then select cell G5 and right-click on the mouse.
• From the context menu, click on the Paste icon from the Paste Options.

• After clicking the Paste command, you will notice the merged range of cells B5:E9 now moved to the range of cells G5:J9.

For better clarity, the main dataset is copied to the original place from where a range of cells was moved.

3. Embedding VBA Macro in Excel

Using a simple VBA macro can drastically reduce the time to move merged cells from one location to another location. You can also use the VBA code to merge cells in Excel.

Steps

• First, go to the Developer tab, then click Visual Basic.

• Then click Insert > Module.

• In the module window, enter the following code.

Sub move_merged_cells()
Selection.Cut Range("G5")
End Sub

• Then close the Module window.
• Select the range of cell that needs to be moved. In this case, select the range of cells B5:E9.
• After that, go to View tab > Macros(Double click).

• After clicking View Macros, select the macros that you created just now. The name here is move_merged_cells. Then click Run.

• After clicking Run, you will see that the merge range of cells B5:E9 is now moved to the G5:J9.

For better clarity, the main dataset is copied to the original place from where the range of cells was moved.

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the question “how to move the merged cell in Excell” is answered here in 3 different ways. Started by using the Paste Special tool then continued to format cells to change alignments, and ended up using VBA Macros. Among all of the methods used here, using the paste special is the easier to understand and simple one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enabled workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

<< Go Back to Cells | Merge | Learn Excel