Categories
Excel

How to Swap Columns in Excel to Compare and Analyze Data

When I do Microsoft Excel training, participants often tell me that they want to rearrange the columns in their spreadsheets to make data entry and analysis easier.

Often, the columns you want to work with aren’t adjacent to one another.

I’ll let you in on a little secret… it’s easy to move entire columns making analysis simpler and more convenient!

In this article, we’ll explore how to move columns to simplify to process of comparing and analyzing data.

These techniques will work whether you want to move an entire column or multiple columns… and you’ll pleased to know that you can apply what you learn to data lists and Excel tables!

Are you ready to get your Excel columns set up in a way that works for best for you?

Swap Adjacent Columns with Cut and Insert Cut Cells

We’ll start with a fairly basic method – swapping columns with the shortcut menu in Excel.

Let’s work through an example. Remember – if you make a mistake along the way, you can always click Undo!

  1. Right-click the column header that you want to move and click Cut from the shortcut menu.
  2. Right-click the column header that you want to swap and click Insert Cut Cells.

The example (below) shows my screen after right-clicking the heading for column D.

Swap columns with Cut and Insert Cut Cells

Swap Multiple Columns with Cut and Insert Cut Cells

The process described above will work just as well if you want to swap multiple columns. The steps are basically the same but you start by dragging to select multiple columns.

  1. Drag to select two or more adjacent columns
  2. Right-click anywhere in the selected area and click Cut.
  3. Right-click the column heading where you’d like the data to go and click Insert Cut Cells.

Swap Multiple Columns Using the Mouse (and the Shift Key)

Swapping two columns (or multiple columns) in Excel using your mouse and Shift key is a quick way to reposition a selected column to make data entry and analysis easier.

Here is how the process works.

  1. Drag to select one or more columns in Excel (drag across the column headings).
  2. Position your mouse pointer over the right border of the selected range.
    A four headed arrow appears behind your pointer.
  3. Press and hold your left mouse button then drag the selected range to the desired location while pressing the Shift key.
  4. Position the “I beam” where you’d like your column(s) to appear then release your mouse button.

The screenshot (below) shows the I beam positioned between columns B and C.

Swap columns with mouse and Shift key

Swap Columns Using Keyboard Shortcuts

Many people believe that everything in Excel can be accomplished more quickly using keyboard shortcuts.

Here is how to swap two columns using only your keyboard.

  1. Position your cursor within a column, press Ctrl + Space to select the entire column, then press Ctrl + x to cut.
  2. Select a column that you’d like to swap with the first one then press Ctrl + Space to select the whole column.
  3. Press Ctrl + Shift + = (equals sign) to swap the new column with the original one.

Swapping Columns in an Excel Table

The techniques described in this article work exactly the same whether you’re swapping columns in a regular worksheets or Excel tables… with one exception!

In Excel tables, the Crtl + Space keyboard shortcut will select all of the cells in a particular column… not the entire column. You’re better off clicking the column heading when selecting a column in a table.

Swapping Columns is Easy

I hope you found this article useful. Each of the techniques accomplishes the same thing. You can easily swap one selected column with another.

Whether you right-click, use drag and drop or keyboard shortcuts really comes down to personal preference.

If you only need to swap individuals cells (not an entire column), be sure to check out my article on how to swap two adjacent cells.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.