EXCEL TOOLS

How to Switch Columns in Excel

Published April 18, 2022
Share:

Want to keep track of your data and compute averages automatically? Microsoft Excel is the most widely used spreadsheet application in the world, with millions of users. Excel and other spreadsheet programmers are excellent for data manipulation, analysis, and visualization because they allow you to sort, filter, format, and chart your data all in one place.  Consider gathering contact information for a field trip.

A table is created by a collection of columns and rows in an Excel spreadsheet. Columns are normally allocated alphabetical letters, while rows are usually assigned numerals. A cell is the intersection of a column and a row. A cell's address is determined by the letter that represents the column and the number that represents the row.

Have you ever wondered how to move columns in Excel tables?

This tutorial covers how to switch or shift multiple columns. Changing adjacent columns is something most people do quite often. This tutorial will show you how to:

  1. Switch Two Columns with the Shift Key method
  2. Swap the Place of an Entire Column with the Cut and Paste Method
  3. Swap Multiple Columns in Excel with One Move
  4. Switch Two Excel columns with Keyboard Shortcuts

Switch Two Columns with the Shift Key method

When you switch Excel columns with drag and drop, it will only highlight the cells instead of moving them. Use the shift key method if you want to move a selected column; there are a few steps to doing this:

  1. Open the Excel application.
  2. Right-click on the header of the column you want to move. It will select the entire column.
  3. Move the cursor to the right side of the column. The cursor will change to a four-directional arrow icon.
  4. Left-click on the side of the column and press the shift key.
  5. Simply drag the column holding the shift key. You will see a line "|" showing where your next column will be inserted.
  6. Release the left mouse button and shift the key.
  7. The first column will replace the second column, moving the column to the side.
  8. Then, select the second column and use the same method to move it to the position of the first column.
Microsoft Excel - First Positions

Figure 1 - Microsoft Excel - First Positions

Microsoft Excel - Second Positions

Figure 2 - Microsoft Excel - Second Positions

Microsoft Excel - Final Position

Figure 3 - Microsoft Excel - Final Position

Note: changing the positions without holding the shift key will overlap the data of the second column.

Swap the Place of an Entire Column with the Cut and Paste Method

If the drag and drop method does not work for you, you can also use the cut and paste method. There are the following steps:

  1. Open the Microsoft Excel Application.
  2. Right-click on the header of the column you wish to move. It will highlight the entire column.
  3. After highlighting, right-click on the column header and choose the "cut" option. You can also press Ctrl + X to cut the column.
  4. Click on the column header that you want to swap with the other column.
  5. When highlighted, right-click on the column and click 'insert cut cells' from the menu.
  6. This will insert the column within the place of the initial one.
  7. Use the same method to move the second column to the other column's place.
Microsoft Excel - Cut option

Figure 4 - Microsoft Excel - Cut option

Microsoft Excel - Insert cut cells

Figure 5 - Microsoft Excel - Insert cut cells

Microsoft Excel - Last Position

Figure 6 - Microsoft Excel - Last Position

Note: You will not be allowed to insert a new column to your chosen area, following a few conditional rules, which are whilst copying/pasting entire columns.

Swap Multiple Columns in Excel with One Move

To swap columns in Excel with one move, there are some simple steps:

  1. Select the first row; then right-click the row and select the insert option.
  2. Use the first row to make a new order of columns.
  3. Then, add values in new rows according to the pattern you want your columns to appear in.

Figure 7

  1. Next, select all the data by left-clicking and dragging it to the last cells of the data.
  2. Click on the Data tab on the toolbar.
  3. There, click on Sort in the Sort and Filter group.
Data tab - Select sort

Figure 8 - Data tab - Select sort

  1. The Sort dialog box will appear.

Click on Options.

Sort Dialog Box - Options

Figure 9 - Sort Dialog Box - Options

Select the Sort left to right option and Click Ok.

Sort Options - Sort left to right

Figure 10 - Sort Options - Sort left to right

Then, in the Sort by option, select row 1 and click OK.

Sort Dialog Box - Sort by

Figure 11 - Sort Dialog Box - Sort by

Delete the newly inserted row.

Result:

Result

Figure 12 - Result

Switch Two Excel Columns with Keyboard Shortcuts

With keyboard shortcut keys, it is easy to switch two columns. Follow these steps to change selected columns:

  1. Select any cell in the columns in Excel.
  2. Hold Ctrl and press the space key to select the whole column.
  3. Next, again hold Ctrl and press the 'X' key to cut it.
  4. Select the column that you want to switch with the first one.
  5. Again, hold Ctrl and press the space key to highlight the column.
  6. Hold the Ctrl button and press the (+) plus key to insert the first one into the new location.
  7. Go to the second column and hold Ctrl and press space to select the entire column.
  8. Press Ctrl + 'X' to cut the columns.
  9. Select the place of the first one and press Ctrl + (+) plus.
  10. This will switch the location of both columns.
Microsoft Excel - First Position

Figure 13 - Microsoft Excel - First Position

Microsoft Excel - Second Position

Figure 14 - Microsoft Excel - Second Position

Microsoft Excel - Final Position

Figure 15 - Microsoft Excel - Final Position

The IronXL C# Library

For opening, reading, editing, switching columns, and saving Excel files in .NET, IronXL provides a versatile and powerful framework. It is compatible with all .NET project types, including Windows apps, ASP.NET MVC, and .NET Core Applications.

For .NET developers, IronXL provides a simple API for reading and writing Excel documents.

To access Excel manipulation scripts, IronXL does not require the installation of Microsoft Office Excel on your server, nor the use of Excel Interop. Working with Excel files in .NET becomes incredibly rapid and straightforward due to this.

Using IronXL, developers can perform all Excel related calculations without any complication, simply by writing a few lines of code, including tasks such as adding two cells, total column option, adding an entire column in an Excel table, adding an entire row in an Excel table, all the cells sum function/sum option and sum function of multiple columns and multiple rows, and many other helpful features.

Below are some code examples of the C# code in action.

using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet ["A1"].Formula = "Sum(B8:C12)";
worksheet ["B8"].Formula = "=C9/C11";
worksheet ["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet ["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet ["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();
using IronXL;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Set Formulas
worksheet ["A1"].Formula = "Sum(B8:C12)";
worksheet ["B8"].Formula = "=C9/C11";
worksheet ["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();

// Get Formulas
// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet ["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet ["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();
Imports IronXL

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private worksheet As WorkSheet = workbook.DefaultWorkSheet

' Set Formulas
Private worksheet ("A1").Formula = "Sum(B8:C12)"
Private worksheet ("B8").Formula = "=C9/C11"
Private worksheet ("G30").Formula = "Max(C3:C7)"

' Force recalculate all formula values in all sheets.  
workbook.EvaluateAll()

' Get Formulas
' Get the formula's calculated value.  e.g. "52"
Dim formulaValue As String = worksheet ("G30").Value

'Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = worksheet ("G30").Formula

'Save your changes with updated formulas and calculated values.
workbook.Save()
VB   C#

Developers have to be careful when they modify and edit Excel files in C# because it can be easy for one misstep to change the whole document. Being able to rely on efficient and straightforward lines of code helps reduce the risk of error and makes it easier for us to edit or delete Excel files programmatically. Today, we'll walk through the steps necessary to edit Excel files in C# quickly and accurately, using functions that have already been well-tested. For more information, please visit the following link.

< PREVIOUS
How to Read Excel File in Java (Tutorial)
NEXT >
How to Add Up a Column in Excel

Ready to get started? Version: 2024.10 just released

Free NuGet Download Total downloads: 1,049,987 View Licenses >