Tuesday, October 12, 2010

How do you flip or reverse a column of data in Excel?

How do you flip or reverse a column of data in an Excel spreadsheet? Well, if you simply want to reverse a list that's already in alpha or numeric order that is easily accomplished using the built in "Sort: feature (you can also easily transpose columns into rows and vice versa).

However, often you will find yourself with a column of data that isn't sorted (and may even be in different formats like a mix of numbers and text). How do you simply flip it so the bottommost items are on top and the top is now the bottom of the column of data?

There are a number of ways to do this, including writing a macro, but the easiest way I can think of is to simply create what I like to call a helper column. First, select the column you want to flip, right click it, and choose Insert to add your new helper column. Add a list of numbers starting with 1 and running down to the number of items in your list. Now select both of those numbers and then double-click the lower right corner of the cell that contains the 2 to quickly fill down the length of your target column.

Select your helper column and any other columns you want sorted along with it. Go to the Data tab and click the Sort button and use this to Sort by your helper column using A to Z or largest to smallest. That's it. Once completed, you can delete your helper column. Simple, eh?

Another more advanced method is to use a macro. Try this:

Sub FlipColumns()

Dim vTop As Variant

Dim vEnd As Variant

Dim iStart As Integer

Dim iEnd As Integer

Application.ScreenUpdating = False

iStart = 1

iEnd = Selection.Columns.Count

Do While iStart < iEnd

vTop = Selection.Columns(iStart)

vEnd = Selection.Columns(iEnd)

Selection.Columns(iEnd) = vTop

Selection.Columns(iStart) = vEnd

iStart = iStart + 1

iEnd = iEnd - 1

Loop

Application.ScreenUpdating = True

End Sub