If you want to learn how to switch columns in Excel to reorganize dataset columns, read this Excel tutorial now.

Microsoft Excel offers various ways to structure your data for analytics and visualization purposes. One such way is switching the position of data columns from one end to another, in between columns, or to a new worksheet.

However, all the methods to swap columns depend on mouse-based movements, copying a column from one place to another, advanced sorting, and programmatic methods like Excel VBA and Office Scripts. Let’s get started!

Switch Columns via Dragging and Dropping

This is the easiest method to switch the column position. The method behaves differently if you perform it on any dataset or a table.

In Regular Datasets

Navigate to your worksheet on which you want to switch column positions. Select the whole column by either clicking on the column header character.

Alternatively, select the first cell, press Shift, and select the last cell to highlight the whole column.

Hover the cursor over the column border to activate the drag-and-drop function.

Click on the border to start dragging.

Now, drag it to the destination and let go of the mouse click.

If you drop the source column on the destination containing another dataset, Excel shall ask you to replace the old data with new column data.

In an Excel Table

Select the table column you wish to switch within the table or outside the table.

Now drag it to the column where you want to move it. You shall see a guiding line on the right-side border of the destination column.

Let go of the click to switch column positions.

The source column shall occupy the position of the destination column. The existing data of the destination column shall move to the left.

If you place the column outside the table borders, Excel shall cut the column data and paste it into the new position.

The moved column will no longer be in Excel table format. It’ll be converted to a regular cell range with cell formatting matching the source table.

📒 Read More: 13 Ways to Insert a Column in Microsoft Excel

Switch Columns Using the Shift Key

This method involves selecting the column you want to switch in Excel by holding down the Shift key while clicking on the cell range. Once selected, you can drag and drop the columns to rearrange them in the desired order.

In Regular Datasets

Select the column data range, press the Shift key, and keep it pressed until you’ve switched the column.

Move the cursor to the right-side border of the destination column. This shall show a guiding line.

Release the source column to switch the position.

Excel shall push the existing data of the dataset to the left and place the source column into the destination range.

In an Excel Table

Select a column on a table and press the Shift key.

While holding the Shift, hover the cursor over the border of the column to find the drag handle.

Use the drag handle to drag the column on any of the existing columns of the Excel table.

Release the click to switch the column position.

Excel shall ask you to rewrite the data of the existing column with the new data.

If you attempt to move the column outside the table, you shall see the column movement guiding line. You shall convert the table column into a regular data column by moving it outside the table.

Excel also resizes the column by deleting the blank column you left by switching the source column to a new position.

📒 Read More: 8 Ways to Apply a Formula to an Entire Column

Switch Columns Using Cut and Paste

Select the source column and press Ctrl + X on the keyboard.

Go to the destination column and press Ctrl + V to switch the column position.

Alternatively, right-click on the destination column and choose the Insert Cut Cells option from the context menu.

Excel shall move the source column, paste it on the destination column, and push all the columns of the dataset to the left edge. This is done to delete the blank column left by moving the source column.

Now, if the destination column contains data, copying the column shall automatically replace the old dataset.

To keep the existing data in the destination column, right-click and choose Insert Cut Cells.

Excel shall switch the source column to the left side of the destination column.

📒 Read More: Row vs Column in Microsoft Excel: Key Differences

Switch Columns Using Horizontal Sorting

By default, you can sort in Excel from top to bottom. However, you can also change the order of sorting from left to right. In this method, I’ll show you how to use this custom sort feature to switch columns.

Go to the source dataset. Select the column headers of the dataset and insert a row above the column headers.

Now, enter values, like 1, 2, 3, 4, etc., to number the column headers.

Select the input dataset and press Alt + H + S + U to bring up the Custom Sort dialog.

Click on the Options button to get the Sort Options dialog. There, choose the Sort left to right option. Press OK to apply.

On the Sort dialog, click the Sort by drop-down and choose Row 1. The rest of the drop-down fields like Sort On and Order shall be configured automatically by Excel depending on the data of Row 1.

Click OK to apply the sorting function.

Excel shall automatically reorganize the dataset by switching the positions of the columns.

You can now delete Row 1 to get rid of the redundant column headers.

Switch Columns Using Excel VBA

If you wish to switch the column positions automatically, you use a VBA script to create a VBA macro. Then, you can run the macro whenever you need to reorganize columns in a dataset.

For example, the following VBA script allows you to visually instruct Excel to switch columns effortlessly:

Sub MoveColumn()
    Dim SourceRange As Range
    Dim DestinationRange As Range
    Dim ExistingDataRange As Range
    
    ' Prompt user to select the source range
    On Error Resume Next
    Set SourceRange = Application.InputBox("Select the source range to move", Type:=8)
    On Error GoTo 0
    
    ' Exit if user cancels the selection
    If SourceRange Is Nothing Then
        MsgBox "Operation cancelled.", vbExclamation
        Exit Sub
    End If
    
    ' Prompt user to select the destination range
    On Error Resume Next
    Set DestinationRange = Application.InputBox("Select the destination range", Type:=8)
    On Error GoTo 0
    
    ' Exit if user cancels the selection
    If DestinationRange Is Nothing Then
        MsgBox "Operation cancelled.", vbExclamation
        Exit Sub
    End If
    
    ' Check if destination range contains data
    If WorksheetFunction.CountA(DestinationRange) > 0 Then
        ' Prompt user to select the range to move existing data
        On Error Resume Next
        Set ExistingDataRange = Application.InputBox("Select the range where existing data will be moved", Type:=8)
        On Error GoTo 0
        
        ' Exit if user cancels the selection
        If ExistingDataRange Is Nothing Then
            MsgBox "Operation cancelled.", vbExclamation
            Exit Sub
        End If
        
        ' Move existing data to the selected range
        ExistingDataRange.Resize(DestinationRange.Rows.Count, DestinationRange.Columns.Count).Value = DestinationRange.Value
    End If
    
    ' Move the source range to the destination range
    SourceRange.Cut DestinationRange
    
    MsgBox "Column moved successfully.", vbInformation
End Sub

You don’t need to do any manual adjustments in the above script. The VBA code is highly flexible to adjust to your own dataset. All you need to do is supply the inputs according to the instructions shown in the input boxes.

To learn the steps to create a VBA macro using the above script, check out this Excel tutorial now:

📒 Read More: How To Use The VBA Code You Find Online

Did you create the macro yet? Perfect! Let’s find below how to use this macro:

Suppose, you wish to switch the position of the column A in the above dataset to column C. Since the destination cell range contains another dataset, you also want to move it to another blank column.

To achieve this, press Alt + F8 to bring up the Macro dialog. Choose the MoveColumn macro and hit Run.

In the first input box, select the cell range of the column you’re switching to a different position.

Now, choose the destination for the selected column in the second prompt.

If Excel finds any data in the destination column, you’ll see a third prompt. Here, choose the cell range where you want to move the second column.

Now, Excel shall reorganize the columns as you’ve instructed.

⚠️ Warning: Before running any VBA or Office Script, copy your workbook as a backup. You won’t be able to use Excel undo feature on the worksheet.

Switch Columns Using Office Scripts

Office Scripts lets you automate this task on both Excel for the web and Excel for the Microsoft 365 desktop app. If needed, you can also share the script with a collaborator so they can also automate the process.

Go to your worksheet where you need to run this Office Script and click on the Automate tab.

Inside Scripting Tools, find the New Script command button and click on it.

You’ll see the Code Editor panel on the right side border.

Sometimes, it might show an old script. Click anywhere on the code and press Ctrl + A to select the whole script.

Press Delete to remove this redundant code.

Now, paste the following code inside the Code Editor and hit the Save script button.

function main(workbook: ExcelScript.Workbook, selectedColumn: number, newColumn: number) {
    let selectedSheet = workbook.getActiveWorksheet();
    let range = selectedSheet.getUsedRange();
    let values = range.getValues();
    let columnValues = values.map(row => row[selectedColumn]);
    let newValues = values.map(row => {
        row.splice(newColumn, 0, row.splice(selectedColumn, 1)[0]);
        return row;
    });
    range.setValues(newValues);
}
    

When saving, you might want to assign it a unique name so you can find the script in the future.

Click the Run button to execute the script.

Office Scripts shall show an input box. In the Selected Column field, enter the column number you wish to move. Column number starts from 0 for A, 1 for B, 2 for C, etc.

In the New Column field, enter the destination of the column. For example, if you wish to move column A to column D, press 3 in the New Column field.

Click the Run button on the prompt window to execute the Office Scripts code.

Excel shall move the selected column and replace the rest of the columns to the left to accommodate the new column.

📝 Note: Office Scripts functionality is only available to Microsoft 365 Business Standard or better subscriptions. Also, the Microsoft 365 Domain administrator must enable the feature for your account, especially if you’re a part of an organization.

Conclusions

Now you know how to switch column positions in Excel using drag and drop, cut-copy-and-paste, and the right-click methods.

Additionally, you’ve also learned the programmatic methods to switch columns using custom sort, Excel VBA, and Office Scripts.

If the article helped or you’ve got a method to share about switching columns, comment below.