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.


Download Practice Workbook

Download this practice workbook below.


3 Easy Ways to Move Merged Cells in Excel

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.

Embedding VBA Macro to move Merged Cells in Excel


1. Using Paste Special Command to Move Merged Cells

Using Paste Special 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.

Using Paste Special Command to move merged cells in Excel

  • 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.

Using Paste Special Command to move merged cells in Excel

  • 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.

Using Paste Special Command to move merged cells in Excel

  • 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.

Read more: How to Shift Rows in Excel (5 Quick Ways)


Similar Readings


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.

Changing Text Alignment to move merged cells in Excel

  • 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.

Changing Text Alignment to move merged cells in Excel

  • 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.

Changing Text Alignment to Move Merged Cells in Excel

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

Changing Text Alignment to Move Merged Cells in Excel

  • 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.

Changing Text Alignment to move merged cells in Excel

  • 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.

Read More: How to Shift Cells Right in Excel (4 Quick Ways)


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.

Steps

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

Embedding VBA Macro to move Merged Cells in Excel

  • Then click Insert > Module.

  • In the module window, enter the following code.
Sub move_merged_cells()
Selection.Cut Range("G5")
End Sub

Note :

1. Range(” G5”) in the code window at line 2 in the Selection.Cut denotes the destination cell where the cell content will move. If you need to paste the cell content to another location then edit this manually according to your need.

  • 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).

Embedding VBA Macro to move Merged Cells in Excel

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

Embedding VBA Macro to move Merged Cells in Excel

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

Embedding VBA Macro to move Merged Cells in Excel

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

Read More: How to Move Down One Cell Using Excel VBA (with 4 Useful Applications)


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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo