Swap Two Columns

What it does

You built a table with First Name in column B and Last Name in column A. Now you want them in the right order. Swapping columns in Excel involves cut, paste-with-shift, paste-back, which is three operations and one cell-reference correction away from a disaster. The macro swaps two columns in one click, supporting both adjacent (just select 2 columns) and non-contiguous (Ctrl+click two separate columns).

Real-world example

Picture an FP&A analyst at a regional manufacturing company building this quarter's variance report. Her template has columns in the order: account, current quarter actual, prior quarter actual, variance dollar, variance percent. The CFO walks by, says he prefers seeing prior quarter first so the eye scans naturally from old to new. So now she needs to swap "current quarter actual" (column B) with "prior quarter actual" (column C).

Her current method: insert a blank column to the left of B, copy column C into the blank column, delete column C, the data shifts, now column B has prior quarter and column C has current quarter, but the column headers are mismatched, so she edits the headers. About 90 seconds with three places to misclick.

With this macro she selects columns B and C, runs the tool, and the values swap in place in less than a second. Headers swap along with data because they're part of the column. Total elapsed: 5 seconds.

On a per-swap basis the time saving is small but the bigger win is freedom from cell-reference corruption. Cut and paste of columns can break formulas in other parts of the workbook that referenced the swapped cells. The macro swaps values only, leaving formulas elsewhere untouched.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select exactly 2 columns to swap. Two adjacent columns work; non-contiguous columns also work if you Ctrl+click two single-column ranges.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick SwapColumns, click Run.
3. The macro swaps the values of the two columns. The popup confirms.

FAQ

Does it swap formulas correctly?

Formulas are swapped as text, which means relative references in the formulas now reference different cells than before. This is the same behavior as cut-and-paste. If your columns have formulas with relative references, freeze them first with Convert Formulas to Values, then swap.

What if my two columns have different numbers of rows?

The macro errors with "Both columns must have the same number of rows." Make sure your selection covers the same row range in each column.

Can it swap rows instead of columns?

No, this macro is column-only. To swap rows, you'd want a different macro (which we may add). For one-off row swaps, just cut and paste manually.

What if I select 3 or more columns?

The macro errors with "Select exactly 2 columns to swap." It's a deliberate guard so you don't accidentally rearrange more than you meant to.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the swap in one step. As always, work on a copy of the file if it's the only copy. The macro runs entirely offline. Your data never leaves your machine.