Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)

The common problem that arises when moving rows and columns in Excel is that it replaces the existing data in the location. This article addresses this issue and provides 3 of the simplest methods for transferring rows and columns in MS Excel without affecting the data in the destination.

3 Ways to Move Rows or Columns MS Excel

1. Using the “SHIFT” key

This is the fastest method. Follow these steps to apply this solution:

  1. Select the rows or columns you want to move.Selecting the data
  2. Hover your mouse cursor to the edge of your selection and wait for it to change into a 4 directional cross.Moving the selection to the desired location
  3. Press and hold the SHIFT key.
  4. Now, left-click on it with your mouse and drag your selection to the desired location while holding the SHIFT key.Dropping the selection to the desired location
  5. A green line should appear to assist you to drop it at the desired location. Follow the green line and drop it at the destination.Rows and columns moved

It will move the data to the desired location and also shift the existing data accordingly.

Read More: How to Switch Rows and Columns in Excel (5 Methods)

2. Using the “Insert” option

This method is comparatively slower but easier. Follow these steps to apply this solution:

  1. Select the rows or columns you want to move.Selecting the data
  2. Cut your selection by pressing Ctrl+X or right-click on it and select the Cut option from there.Selecting the Cut Option
  3. Now, select the desired location you want to move the data to.
  4. Right-click on it and select the Insert Cut Cells option from there.Selecting the Insert Cut Cells option

It will move the data to the desired location and also shift the existing data accordingly.

Read More: How to Add Rows and Columns in Excel (3 Easy Methods)


Similar Readings


3. Using the “Sorting” Option

This method is suitable for scenarios where multiple rows and columns need to be rearranged. Follow these steps to apply this solution:

For Rearranging Rows:

  1. Select the 1st column.
  2. Insert an extra column by right-clicking and selecting the Insert option from there.Inserting a new column
  3. Use the new column to specify the new order of the rows with appropriate numbers.Specifying the new row order in the new column
  4. Select the data.Selecting the data
  5. Now, go to Data and select the Sort option from there.Selecting the Sort option
  6. A new window named Sort should appear. Select the Options button from there.Selecting the Options button
  7. Another window named Sort Options should appear. Select Sort top to bottom and click OK.Selecting the Sort Top To Bottom option
  8. In the Sort by field, select the new column that you created from the drop-down menu and click OK.Selecting the new column
  9. Now, delete the 1st column that you created.Deleting the new column

It will rearrange the rows by the order you entered.

For Rearranging Columns:

    1. Select the 1st row.
    2. Insert an extra row by right-clicking and selecting the Insert option from there.Inserting a new row
    3. Use the new row to specify the new order of the rows with appropriate numbers.Specifying the new column order in the new row
    4. Select the data.Selecting the data
    5. Now, go to Data and select the Sort option from there.Selecting the Sort option
    6. A new window named Sort should appear. Select the Options button from there.Selecting the Options button
    7. Another window named Sort Options should appear. Select Sort left to right and click OK.Selecting the Sort Left To Right option
    8. In the Sort by field, select the new row that you created from the drop-down menu and click OK.Selecting the new column
    9. Now, delete the 1st row that you created.Deleting the new row

It will rearrange the columns by the order you entered.

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)

Conclusion

Data is the most valuable resource. There are numerous cases of losing data while rearranging it in MS Excel. In this article, I have attempted to answer those concerns by presenting three comprehensive and secure ways for moving rows and columns in Excel. I hope you were able to solve your problem. Please leave a comment if you have any suggestions or questions. Thank you.


Related Articles

Chinmoy Mondol

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

2 Comments
  1. First method does not work. Shift button doesn’t change anything. It still wants to replace data. I still cannot re-order my columns or rows without extra steps of creating new blank row and deleting old one.
    The other method of first cutting the data, then right-click to “Insert cut cells” works. I still feel it’s more clunky than the google docs way of just dragging it, but at least there’s a way to do it at all.

    • Dear Jacob Smith
      Thank you for your comment.
      The first method works properly when you press the SHIFT key after selecting rows/columns.
      Let me explain this elaborately.
      Here, in the following dataset, we want to move rows 7 and 8.

      To do so, first, we will select cells B7:D8.
      After that, we will hover our mouse cursor to the edge of the selection and wait for it to change into a 4 directional cross.

      At this point, press the SHIFT key.
      Along with that, left-click on it with your mouse and drag your selection to the desired location.
      A green line should appear to assist you to drop it at the desired location.
      Here, we drag the selected rows to Row 3, and therefore, you can see a Green Line at Row 3.
      In addition, you can see B4:D5 on the left side of the Green line which indicates the final location of the selected rows.
      After that, we will release the mouse and SHIFT key.

      As a result, you can see the movement of rows to a new location.

      I hope that you can now use Method-1.
      If you have any problems, you can always let us know in the comment section.

      Regard
      Afia Aziz Kona

Leave a reply

ExcelDemy
Logo