6 Ways to Switch Columns in Microsoft Excel

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

Select column by character
Select column by character

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

Select whole column with Shift
Select whole column with Shift

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

Column drag handle location
Column drag handle location

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

Dragging a column using drag handle
Dragging a column using drag handle

Click on the border to start dragging.

Switched column by dragging
Switched column by dragging

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

Replacing old data
Replacing old data

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

Column A switched to column B
Column A switched to column B

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.

Switching table columns
Switching table columns

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.

Switched column outside table
Switched column outside table

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 source column
Select source column

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

Drag column here
Drag column here

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.

Switched column position with Shift
Switched column position with Shift

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 source column range
Select source column range

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

Switch column in table with Shift
Switch column in table with Shift

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.

Switched column outside table with Shift
Switched column outside table with Shift

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

Cut source column
Cut source column

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

Pasting source column
Pasting source column

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

Insert cut cells
Insert cut cells

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

Switched column and shifted all to the left
Switched column and shifted all to the left

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.

Right click and choose insert cut cells
Right-click and choose insert cut cells

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

Switched column position to the left of destination
Switched column position to the left of destination

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.

Create helper row
Create helper row

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.

Sort dialog
Sort dialog

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

Sort Options dialog
Sort Options dialog

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

Configuring custom sort
Configuring custom sort

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.

Switched positions of columns
Switched positions of columns

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

Delete redundant column header
Delete redundant column header

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:

VBA script to switch columns
VBA script to switch columns
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:

Sample dataset for VBA
Sample dataset for VBA

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.

First input box for source column
First input box for source column

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

Second prompt for destination
Second prompt for destination

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

Third input box to choose destination
Third input box to choose destination

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.

Excel reorganized columns
Excel reorganized columns

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.

Office Scripts Code Editor
Office Scripts Code Editor

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.

Delete old code
Delete old code

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.

Office Scripts prompt
Office Scripts prompt

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 Run on the input box
Click Run on the input box

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

Switched column A to D using Office Scripts
Switched column A to D using Office Scripts

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃