Move or Swap columns in Excel - (Full guide)

Managing data efficiently in Excel often involves reorganizing columns to better structure your worksheets. Whether you need to move a single column, rearrange multiple columns, or swap the positions of two columns, this task is a common yet critical part of data management. While it may seem straightforward, performing these actions efficiently can save significant time and help avoid potential errors.

In this comprehensive guide, we’ll explore various methods to move or swap columns in Excel, ranging from basic techniques like dragging and dropping to advanced solutions using VBA and third-party tools like Kutools for Excel.

Move one or more columns

When working with large datasets in Excel, reorganizing columns is a common task. Whether you need to adjust the order of your data for better readability or rearrange it to meet specific requirements, there are several ways to move columns efficiently. Here, we will cover three methods:

Method 1: By dragging and dropping

This is the easiest and most intuitive way to move columns within an Excel worksheet.

1. Select the Column:
   Click on the column letter at the top of the worksheet to highlight the entire column.
2. Hold the Shift key:
   Move your cursor to the border of the selected column until it turns into a four-sided arrow.
3. Drag to the New Location:
   Press and hold the "Shift" key, then click and drag the column to your desired position.
4. Release the Mouse Button:
   Once you reach the desired location, release the mouse button, and the column will be moved.
   

Method 2: By Kutools for Excel

When your worksheet contains a large number of data columns, the traditional drag-and-drop method can easily lead to column misplacement or operational errors. In such cases, the "Column list" feature of "Kutools for Excel" will become your powerful assistant. It supports precise positioning, helping you effortlessly move columns to the desired location, ensuring the accuracy of data layout and the efficiency of operations, making complex data organization simple and efficient.

After installing Kutools for Excel, please do with the following steps:

1. Click "Kutools" > "Navigation Pane" > "Column list" to open this "Column list" pane;
2. In the pane, choose the column you wish to relocate, then drag and drop it to the desired position.
   

Method 3: By cutting and inserting

If you prefer using keyboard shortcuts or working in a structured manner, the cut-and-paste method is an effective way to relocate columns.

1. Select the Column:
   Click on the column letter to highlight the entire column.
2. Cut the Column:
   Press "Ctrl" + "X" , or right-click and select "Cut".
   
3. Select the Target Column and insert the column:
   Click on the column where you want to insert the cut column. Then, right-click on the target column and select "Insert Cut Cells", or press "Ctrl" + "+" (the + key on the numeric keypad).
   

Swap two columns or ranges

Swapping two columns or ranges in Excel is a common task, but doing it efficiently can save time and prevent errors. This article explores two effective methods: using VBA code and Kutools for Excel.

Method 1: By VBA code

VBA allows users to automate tasks in Excel. Below is a step-by-step guide to swapping two columns using VBA.

1. Open the VBA Editor:
   Press "Alt" + "F11" to launch the VBA Editor.
2. Insert a Module:
   Go to "Insert" > "Module" to create a new module
3. Paste the code into the module:
   Copy and paste the following VBA code into the module:
   

   Sub SwapTwoRange()
   'Updateby Extendoffice
   Dim Rng1 As Range, Rng2 As Range
   Dim arr1 As Variant, arr2 As Variant
   xTitleId = "KutoolsforExcel"
   Set Rng1 = Application.Selection
   Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
   Set Rng2 = Application.InputBox("Range2:", xTitleId, Type:=8)
   Application.ScreenUpdating = False
   arr1 = Rng1.Value
   arr2 = Rng2.Value
   Rng1.Value = arr2
   Rng2.Value = arr1
   Application.ScreenUpdating = True
   End Sub
   

4. Run the Macro:
   Press "F5" to execute the code. Follow the prompts to select the two columns or ranges.
   
   

Result: The columns or ranges will be exchanged instantly.

Method 2: By Kutools for Excel

Tired of the tedious copy-paste routine or the risk of losing critical formatting when reorganizing columns in Excel? "Kutools for Excel" offers a useful feature- "Swap Ranges" function, which allows users to swap two columns or ranges instantly while preserving the original formatting. This ensures that cell colors, fonts, and other styles remain intact, eliminating the need for manual adjustments after swapping.

After installing Kutools for Excel, please do with the following steps:

1. Click "Kutools" > "Range" > "Swap Ranges".
2. In the "Swap Ranges" dialog box, select the columns or ranges you want to swap separately, see screenshot:
   
3. Then, click "OK" button, and the selected columns or ranges will be exchanged immediately without altering formatting.
   

FAQs

1. Can I swap non-adjacent columns in Excel?
   ✅ Yes, you can swap non-adjacent columns in Excel using both VBA and Kutools. In VBA, you can define specific columns regardless of their positions. In Kutools, the Swap Ranges function allows you to select any two ranges, even if they are not adjacent.
2. Will swapping columns affect my formulas in Excel?
   ✅ Swapping columns may impact formulas that reference those columns, as formulas will automatically adjust their references. However, if absolute references ($A$1) are used, they will not change when swapping columns.
3. How do I fix a mistake I made while moving data in Excel?
   ✅ If you made a mistake while moving data, you can use the Undo function (CTRL + Z) to revert the action. Alternatively, you can manually move the data back to its original position or use a backup version of your file if available.
4. How do I swap data organized in rows?
   ✅ The process for swapping rows is similar to swapping columns. In VBA, replace Columns with Rows in the code. In Kutools, use the Swap Ranges feature, selecting two row ranges instead of column ranges.

Conclusion:

Excel provides multiple methods for moving or swapping columns efficiently. For simple moves, dragging and dropping or cutting and pasting are convenient solutions. If you need more control and automation, VBA is a powerful tool for frequent or large-scale operations. On the other hand, Kutools for Excel simplifies the process, offering a user-friendly interface that preserves formatting. Depending on your needs, you can choose the method that best suits your workflow, ensuring both efficiency and accuracy when managing your data. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.